Condition on basic model to determine if Id of row is in grouping of aggregate model?

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:

1. 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).
2. 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).
3. 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?

Thanks!