Filter dashboard to only contain cases with activities

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.

Hey Joos,

This sounds like a good case for using PU_ functions. Just do a FILTER PU_COUNT(“CASE_TABLE”, “ACTIVITY_TABLE”.“ACTIVITIES”) > 0, this should do the job. Every subsequent table is somehow connected to the Cases Table anyways and thus will be filtered accordingly to only Cases with existing activities

Best Regards,

Benedict

1 Like

Right, thanks, so ‘simple’ :slight_smile: PU functions to the rescue!

I also realized that you probably are able to use the whole PQL library in load scripts and filters, it’s just that you’re not returning values (like in charts and tables), but are filtering and hence need to build conditions (true/false).