Apex Code Development You need to sign in to do that
Don't have an account?
Pull first and last record from a related list
Hi - hope somebody can give an idea how to get this done.
We have a custom object "Program Enrollment" and a related list or object called " Outcomes". We need to create reports where we can pull the first and last outcome entries, but we don’t think creating an extra field (check box or drop down) in the outcomes object to flag the first and last outcome is the best approach. And correct me if I am wrong.
Is there a way to pull the first and last record of a related table somehow? And being able to display those 2 values in the "program Enrollment" parent object in fields? So they can then be pulled out in reports.
We have a custom object "Program Enrollment" and a related list or object called " Outcomes". We need to create reports where we can pull the first and last outcome entries, but we don’t think creating an extra field (check box or drop down) in the outcomes object to flag the first and last outcome is the best approach. And correct me if I am wrong.
Is there a way to pull the first and last record of a related table somehow? And being able to display those 2 values in the "program Enrollment" parent object in fields? So they can then be pulled out in reports.
How are you sorting the related list? Depending on how you sort it will define the "first and last" entries?
Let's say you are sorting it based on the create date. On the Program Enrollment object you'd create two Roll-Up Summary fields; one will use the Select Roll-Up Type of "MAX" (we'll call that Max Outcome Create Date) and the other will use the Select Roll-Up Type of "MIN" (we'll call that Min Outcome Create Date).
Now on each Program Enrollment record you'll know the create date of the first and last Outcome records related to it (the Roll-Up Summary fields provide calcuations for related objects).
Now on the Outcomes object create two forumlua fields; let's call them First Created and Last Created. The formula fo rthe Firsted Created field should be if the create date of the Outcome record equals the Min Outcome Create Date of the related Program Enrollment record then populate a value of your choosing. Use the same logic for the Max Outcome Create Date to identify the Last Created record.
You could just use one formula field and have it return different values for the first and last.
In your report you'll filter it to match the values of the formula field(s) you created.
One caveat: if you have two Outcome records related to the same Program Enrollment record that created at the exact same time and the are either the first or last entries you're report will show more than two records.
Let me know if I can clarify anything for you.
If you found my response valuable please "Like" it.
Thanks,
Chris
Renier