Apparently models are limited to two active ‘result of a subquery’ conditions.
I thought I’d get around this by creating a couple aggregate models, and then using ‘field from another model’ conditions to point to the agg models. But it doesn’t seem to work.
Let me explain the full situation:
I have the objects Patient_Case__c, Interaction__c, and Signature__c. Interaction__c is a child of Patient_Case__c, and Signature__c has a lookup to Patient_Case__c.
I want a table which shows the rows from Patient_Case__c where:
- At least one of the Interactions is a complete appointment. i.e. Id IN (SELECT Patient_Case_c FROM Interactions__c WHERE Interaction_Category__c = Appointment AND Status__c = Complete).
- None of the Interactions is a Scheduled appointment. i.e. Id NOT IN (SELECT Patient_Case_c FROM Interactions__c WHERE Interaction_Category__c = Appointment AND Status__c = Scheduled).
- And there is no medical chart signature. i.e. Id NOT IN (SELECT Patient_Case__c FROM Signature__c WHERE Signature_Type__c = MD Medical Chart AND Signature__c != null)
But that’s three subquery conditions.
I tried to create aggregate models on the Interaction__c object with a grouping on Patient_Case__c to get a list of the unique Patient Case Ids that had the conditions for 1 and 2. But it doesn’t seem that I can use a ‘field from another model condition’ to use those lists.
What’s the best way to structure this model?