Query help?

  • 1
  • Question
  • Updated 3 years ago
  • (Edited)
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"
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb

Posted 3 years ago

  • 1
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
Any pointers or ideas on this before we break for Christmas?
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
Is purpose a reference field or just a picklist?
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
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.
(Edited)
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
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?
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
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. :(

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.