Hi,
Apologies in advance for this being rather long but I have tried a number of things and none of them worked for me – so I’m either missing something or trying to do something that can’t be done.
My question is do dynamically added where tabs using the API in the Application Initalised event work with SQL Views – as I can’t get it to work.
Here is what I have done so far.
I have a Custom Database view:
Select custno, Name, Address, count() as totalclaims from Claims
Group by custno
Where status = “FR” order by name
The requirement is for a Dashboard List page that shows total claims for all customers, customers who have reached each of the two claims thresholds, warning and blocked,.
I wanted to use the Additional Where tabs feature to create 3 tabs: All Claims where totalclaims > 0, Warning Exceeded where totalclaims > warning threshold. Blocked where totalclaims > Blocked Threshold. So that users did not need to do their own searches or need to know the settings of the Thresholds.
The actual Threshold values can be altered each year, so are held as parameters in another table and loaded in session variables After Application initialized, so shouldn’t be hardcoded in the Query.
Whilst its not possible to use a calculated field in the Additional Where Tab, it is possible to use it in a where clause via the Before SQL query event of the List page, which could access the threshold settings from the session variables. However that only automated one search. And didn’t provide the neat simplicity of the Where Tabs.
I tried using Additional WHERE tabs API to dynamically add Where Tabs for Warning and Blocked using the calculated field totalclaims and the threshold settings from the session variables. The WhereTabs were successfully added but of course they gave an SQL error at runtime because of the calculated field.
So then I tried an SQL view:
Select from (Select custno, Name, Address, count() as totalclaims from Claims
Group by custno
Where status = “FR” order by name) as ClaimsView
There is no facility to define Additional Where Tabs for an SQL View, but its possible to use the Additional WHERE tabs API. I used the API to dynamically add Where Tabs for Warning and Blocked using the calculated field total_claims and the threshold settings from the session variables. The WhereTabs were successfully added – however they appeared to list the same records as the All Claims tab, without filtering by the tota_lclaims field.
Should this work or am I barking up the wrong tree? Thanks