Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
New Member
Posts: 46
Registered: ‎03-12-2010

Web Filter Error - Table not found

Hi Folks,

 

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

Verb: GET
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.

 

Thanks. 

 

Copper Elite Contributor
Posts: 40
Registered: ‎03-31-2010

Re: Web Filter Error - Table not found

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.

 

Regards,

 

James.

James Sutton
New Member
Posts: 46
Registered: ‎03-12-2010

Re: Web Filter Error - Table not found

Hi James,

 

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   ")

Nickel Contributor
Posts: 59
Registered: ‎07-30-2010

Re: Web Filter Error - Table not found

Hello kj27,

 

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?

Richard Anderson
CRM Consultant
K3 CRM
Nickel Super Contributor
Posts: 172
Registered: ‎04-13-2009

Re: Web Filter Error - Table not found

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?

 

Thanks!!

Becki Blackburn
BITtelligent
Highlighted
Nickel Super Contributor
Posts: 172
Registered: ‎04-13-2009

Re: Web Filter Error - Table not found

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.

Becki Blackburn
BITtelligent