I am trying to figure out how to use addTableFilterParam to filter on a related table.
My application is based around 'cases' and has a main record (case_header) that has many related tables to hold additional information (e.g. case_people, case_items, case_invoices etc) which are all linked to the case_header table via a case_header_id foreign key.
I want to allow people to log in to the system but see only those cases they involved with (a record exists for them in the case_people table linked to the case_header). All my forms are based on the case_header table and have tabs showing information from the related tables so I am trying to find an easy way to filter the case_header table so that only the correct cases are shown when the user logs in. I have looked at this post (https://www.servoy.com/forum/viewtopic.php?f=3&t=5864) which talks about using a nested in query but this doesn't work for my scenario. What I really want is something like:
- Code: Select all
databaseManager.addRelatedTableFilterParam(serverName, tableName, relationName, dataProvider, operator, value, filterName);
The effect of this would be to adjust the generated SQL to either add an INNER JOIN on the columns specified in the relation plus the additional specified comparison or add an EXISTS element to the WHERE clause that does the same thing.
Obviously this doesn't exist (anyone think it worthy of a feature request?) but can anyone point me in the direction of how I can achieve this without redesigning the record logic on all my forms?
Thanks
Steve