I have a custom object called EP with a Master-Detail relationship to Account. An EP record has a status picklist field with the options of Applied, Under Review, Accepted and Left. Accepted records have a Start date and Left records have an end date. There can be multiple EP records on an account and the Account record shows the latest Start and End dates.
I want to display the status of the latest EP application on the Account record. I have tried this with RUS fields on Account to identify how many records there are at each status, and then used a formula field to display this, but I can't work out how to take into account the date of the latest record.
This is my formula on the Summary of EP Status field:
IF ( (AND(EP_start_date_later_than_end_date__c ="Yes", (EP_Status_Accepted_RUS__c > 0))), "Current", IF(EP_Status_Left_RUS__c > 0, "Left", IF(EP_Status_Rejected_RUS__c > 0, "Rejected", IF(ISBLANK(Latest_EP_End_date__c),"NA",""))))
Is this the right way forward or should I consider another approach? Is this something that would better be handled by Flow? I was wondering whether to add in a 'Latest Application date' but can't think how to identify the status of the record it belongs to.
Thanks.
A flow that is triggered on EP edits would work well. Each time an EP is created, edited, loop through them to find the latest related to that account and update the account with the status from that EP. The only caveat would be that the flow would not update the account if you delete the latest EP record.
