Query help?

Interaction is a child of Patient Case.
Interaction has the fields “Purpose” and "Date."

How do I write a query that will return all the Interactions of a particular Purpose which are the first chronologically of that Purpose for their particular Patient Case, and are in a particular date range?

Consider the data below:

Id = 1 ; Case = A ; Purpose = a ; Date = Jan 1
Id = 2 ; Case = A ; Purpose = b ; Date = Feb 1
Id = 3 ; Case = A ; Purpose = a ; Date = Feb 10
Id = 4 ; Case = B ; Purpose = a ; Date = Feb 15
Id = 5 ; Case = C ; Purpose = a ; Date = Feb 15
Id = 6 ; Case = C ; Purpose = a ; Date = Mar 1

Supposing I want all the records with purpose a, where the first occurrence of purpose a in the Case is in Feb?

How do I write a query that will just return Ids 4 and 5, but not 3?

EDIT: And by “query” I mean “model conditions”

Any pointers or ideas on this before we break for Christmas?

Is purpose a reference field or just a picklist?

Actually, nevermind, that won’t help. I think you’ll have to write an apex trigger or workflow (probably a trigger) that would populate a checkbox called “IsFirstInteractionForThisPurposeForThisCase”. Once your trigger is keeping this checkbox in sync, your query is pretty simple.

Yuk. Well, I guess even skuid has limitations.

I have yet to successfully write an apex trigger… I’ll try a workflow first and see if I can get anywhere.

Unless you have the time to outline the apex for me?

It’s more of a SOQL limitation than a Skuid limitation, but since Skuid relies on SOQL, then I guess that makes it a Skuid limitation. :frowning:

I don’t think the apex stuff is in the scope of this community. But, before you dive into Apex, (which I know can do this kind of thing), you might want to try the process builder.