Situation: We have a data model with an activity table, cases table, and some other tables.
Problem: We would like to only consider those records in the cases table for which an activity exists.
Details: This works if both tables are used in the same component, because in the backend the tables are automatically joined from activity to case (left join). However, when for instance counting the number of cases (
count_table(cases_table)) all cases are considered. We therefore would like to set a load filter or pinned filter that filters out those cases for which no activities are recorded. Any ideas on how to do this???
Ideally we would also like to automatically filter our other tables to only keep records linked to an activity.
We are coming from a ‘single table’ setup where all tables where joined into one large table. This problem did not exist then, but performance (especially when reloading) was sub-optimal. The new setup performs better, but apparently has its limits.
PS: I feel the PQL features in the load script are limited, and I’m wondering why, as it limits the filter capabilities, as required in this case.