We have a data structure with case, activity, customer and contact tables.
A contact is linked to a customer (N:1),
A customer is linked to an activity (1:N),
An activity is linked to a case (N:1).
A case can be executed for multiple customers, hence this relationship structure of customers linked to activities.
The challenge/question is to count the number of contacts for a customer of a specific case.
Bonus challenge: only select those contacts that fall within the case runtime (which are attributes in the case table).
The problem we’re facing is the famous ‘no common parent between tables could be found’ error, and we believe the even more famous PULL UP functions could help us, combined with a DOMAIN_TABLE possibly. However, the relationship is such that we cannot just access a parent or grandparent table, but need to go to the parent (case) of a sibling table (activity) of our child table (contact), through customer which is our direct parent.
In our example Customer is the parent table of both contact and activity, and we would like to count contacts for each case.
In SQL this would be a join between the four tables, grouped by case identifier, counting the number of contacts that fall within a date range.