Here’s my solution to yesterday’s challenge.
The goal is to automate everything and not make any assumptions. We also need to take into consideration exam creations, updates, and deletions.
To calculate
- Contact.[First Exam Date] and Contact.[First Exam Result]
- Contact.[Last Exam Date] and Contact.[Last Exam Result]
I first created two lookup fields on Contact
- Contact.[First Exam]
- Contact.[Last Exam]
Then I installed the Declarative Lookup Roll-Up Summaries (DLRS) package.
Side note: DLRS is one of my favorite packages and I install it in nearly all client orgs. It’s recently become so popular that Salesforce.org hosts the installation package. I also lovingly call it “Dolores” 🙂
DLRS is significantly more powerful than native roll-ups because it can be used with lookups and it can include operations such as First, Last, Min, Max, Sum, Concatenate, and Concatenate Distinct. It also auto deploys apex in order to automatically manage creations, updates, and deletions.
Two DLRS records can populate the lookups Contact.[First Exam] and Contact.[Last Exam]. The records run in user mode and in real-time (as opposed to a nightly batch).
Then I was able to create formula fields
- Contact.[First Exam Date] = Contact.First Exam.[Exam Date]
- Contact.[First Exam Result] = Contact.First Exam.[Exam Result]
- Contact.[Last Exam Date] = Contact.Last Exam.[Exam Date]
- Contact.[Last Exam Result] = Contact.Last Exam.[Exam Result]
Finally, Exam.[Most Recent Exam] is just checkbox formula field with value
- IF(Exam.[Id] = Exam.Contact.[Last Exam], True, False)
The takeaway
Sometimes the best solution involves using non-native Salesforce functionality. I could have used trigger flows or apex, but they would become challenging to maintain. Why complicate your life when a Salesforce-approved package does the job?
Thanks to everyone that wrote in their answers. It was interesting to see your various approaches!