A common need in Apricot Results is to identify the most recent response to a Tier 2 question for each Participant/Tier 1 Record. There are a variety of methods to construct variables that will determine this. In many cases, this can be obtained with a single formula.
This formula will be set up like this: Last([Field Name] in ([Tier 1 Record ID];[Date Field];[Tier 2 Record ID])) in ([Tier 1 Record ID])
You will want to replace [Field Name] with whichever field you'd like the most recent response to.
[Tier 1 Record ID] will be replaced with the Record ID object for whichever Tier 1 form you are working with.
[Date Field] will be how you define what is most recent. For example, if you replace [Date Field] with [Modification Date], it will show the answer for whichever record was modified last. This also works with custom fields such as "Date of Contact" or "Enrollment Date." If you Replace [Date Field] with the object that corresponds with your "Date of Contact" field, it will show the answer from the record with the most recent Date of Contact.
[Tier 2 Record ID] will need to be replaced with the Record ID for the Tier 2 form that you are working with.
EXAMPLE:
In this example, we will look for the most recent Service Provided. Service Provided comes from the Services Proved Tier 2 form, which is under the Participant Profile Tier 1 form.
We would use the following formula:
=Last([Service Provided_1864] In ([Participant Profile_16].[Record Id]; [Date of Service_1315]; [Services Provided_69].[Record Id])) In ([Participant Profile_16].[Record Id])
I named the variable Last Service Provided. In the top table below, notice how no matter what shows in the Services Provided or Date of Service columns, the Last Service Provided remains the same. This is because no matter what, this service is the most recent service Provided.
The table under that shows the most recent services provided without the extra columns. Note how the Last Service Provided column is no longer duplicated.
Note that Service B is showing for Gwendolyn Armstrong because Service B has the most recent date (6/7/18).
