05-05-2011 10:40 AM
In Web 7.5.3, I'm creating a distinct filter on the Account entity on a property located in an extension entity of Account. I can select this property and everything builds correctly. Once I re-deploy, the filter shows up on the Account main view, but no distinct values for the field are displayed (i.e. the filter doesn't work). In the log, I see this entry:
Exception caught during the processing of a message
Uri: http://localhost:3333/SlxClient/slxdata.ashx/slx/crm/-/groups?family=Account&name=UK Clients and Prospects&distinctDataPath=C_ACCOUNT_UK:BI_TIER&distinct=C_ACCOUNT_UK.BI_TIER&responsetype=json&time=1304615625250
Original Message: Cannot find table 0.
This extension table (C_ACCOUNT_UK) clearly exists so I'm confused as to why the error is occuring. I have been able to successfully create filters on fields located on 1:M child properties from Account. No such parent/child relationship exists for this table...but it is already an extension table so I'd think this should work.
05-05-2011 09:54 PM
I know this may be a silly question but does your group contain any fields from the Account table?
I was just thinking that Table 0 may not refer to the C_ACCOUNT_UK table?
I think you might get better mileage from using the SLXProfiler to see just what SQL statement the Handler is issueing against the database. Usually that makes it obvious what the issue is.
05-10-2011 06:06 AM
The query for the group is pretty simple - pulling from 4 tables (including Account):
SELECT A1.ACCOUNTID, A1.ACCOUNT, A2.PARENT_ACCOUNT A2_PARENT_ACCOUNT, A3.CITY A3_CITY, A3.POSTALCODE A3_POSTALCODE, A2.SALES_LOB A2_SALES_LOB, A2.MARKET_SECTOR A2_MARKET_SECTOR, A4.ACCOUNT_TYPE_UK A4_ACCOUNT_TYPE_UK, A2.HEAD_OFFICE A2_HEAD_OFFICE, A2.BI_TIER A2_BI_TIER, A1.ACCOUNT_UC FROM ACCOUNT A1 INNER JOIN ADDRESS A3 ON (A1.ADDRESSID=A3.ADDRESSID) LEFT OUTER JOIN C_ACCOUNT_LOB_EXT A4 ON (A1.ACCOUNTID=A4.ACCOUNTID) LEFT OUTER JOIN C_ACCOUNT_UK A2 ON (A1.ACCOUNTID=A2.ACCOUNTID) WHERE (A4.ACCOUNT_TYPE_UK='Prospect') ORDER BY A1.ACCOUNT_UC ASC
This is a bit confusing because the filter is going off of BI_FILTER...which is displaying correctly. I was able to run the Profiler and it doesn't seem like the problem is that it can't find the C_ACCOUNT_UK table:
Failed to parse SQL. (SQL: "SELECT COUNT(*) TOTAL, VALUE, NAME FROM ACCOUNT A1 INNER JOIN ADDRESS A3 ON (A1.ADDRESSID=A3.ADDRESSID) LEFT OUTER JOIN C_ACCOUNT_LOB_EXT A4 ON (A1.ACCOUNTID=A4.ACCOUNTID) LEFT OUTER JOIN C_ACCOUNT_UK A2 ON (A1.ACCOUNTID=A2.ACCOUNTID) WHERE (UPPER(A4.ACCOUNT_TYPE_UK)=':VALUEPARAM0') GROUP BY , ORDER BY ")
05-27-2011 08:02 AM
We have seen a very similar issue with some of our clients when 7.5.3 came out, the issue seems to be with an underscore in a distinct filter when based off a relationship. HF2 resolves this issue, have you applied that yet?
06-13-2011 01:45 PM
I have this exact same issue. Only I have HF1-4 all applied. So if HF2 fixed it, it is now broken again. We have a customer who just upgraded over the weekend from LAN to Web, and I can probably skirt my way around things saying it's a defect, but I'd love it if I could fix it before they complain.
From what I can see - the DataPath needs to say Account.exttensiontable:fieldname instead of just extensiontable:fieldname - but if you try to hard-type that in, it doesn't stick.
Any ideas or fixes?
06-13-2011 01:52 PM
You know what - I bet it has to do with the fact that the actual table name has an underscore in it. All of mine do too.
Pet peaves - apostrophes, ampersands, and underscores.