Get that StageName of the last Opportunity with an specific product at the Account Level

Question

I want to display in a field at the Account level the name of the Stage of the last created Opportunity that has an specific product.

e.g. An Account has 3 Opportunities. And I'm looking for the Stage of the Opportunity that has "Pencils" as Products and was recently created.

• Opportunity 1 is at Stage 4 , has pencils and was created Today. (I want to get the Stage4 name for this one, as this was the latest created. and has pencils)
• Opportunity 2 is at Stage 2 , has pencils and was created Yesterday.
• Opportunity 3 is at Stage 1, does not have pencils and was created Today.

I already have setup the logic for pulling the info from the Product to a field on the Opportunity, let's call it "Has Pencils".

This has to be done as a field and not just as a view, given that this field will sync with other systems outside of SF.

Thanks for your help!

Answer 1

One low-code approach is using a combination of Roll-Up Summary and Process Builder.

You need a custom field on Account to hold the Opportunity Stage, a roll-up summary on Account to hold the latest creation date of its opportunities and a process builder on Opportunity to automatically update the stage at the Account.

1. Create a roll-up summary field on Account, eg. Latest Opportunity Created Date. Use MAX(CreatedDate) for the aggregation function and filter criteria Has Pencils = true.

2. Create a custom field on Account to hold the latest-opportunity-created's stage, eg Latest Opportunity Stage.

3. Create a process builder on Opportunity (on update event). The decision node formula should be [Opportunity].Account.Latest_Opportunity_Created_Date__c = [Opportunity].CreatedDate AND Has Pencils = true. And for the immediate action, use Update Record on the Account with only when the Account's Latest Opportunity Stage does not equals to Opportunity's Stage (to avoid unnecessary DML update).