Once in a while, I see questions on the Answers community where the requirement is to update a parent record based on all related child records. The complexity arises from the fact that we need to loop through all child records, check for a certain condition and then accordingly update the related parent record. Such looping across multiple records in Salesforce is only possible through a declarative tool like Flows or a coded solution like Apex. Since we do have the option of using a declarative tool here, we are simply going to ignore Apex (no offense to the developers).
Requirement: The system administrator needs to create an automated process which keeps the Account Status updated depending upon the related Opportunities’ stage. If an Account has none of the related Opportunities marked as ‘Closed Won’, the Account Status should be ‘Prospect‘. But if the Account has even a single Opportunity with the Stage of ‘Closed Won’, the Account Status should be set to ‘Customer‘.
Design Approach (questions to ask yourself before you begin the implementation):
- What are we really trying to do here?
The sales reps wish that the account status be determined automatically depending upon the related Opportunities’ stage. The design here would be to fire our automation whenever an Opportunity is created or modified. Our primary goal is to detect a stage change on the Opportunity and then loop through all Opportunities related to the Account to accordingly update the Account status which is a custom field. For example: We have an account with 10 related Open Opportunities. If a sales rep edits these Opportunities and at the end, we have 7 Open, 2 Closed Won and 1 Closed Lost Opportunity, the Account Status should be set to ‘Customer’. If another sales rep comes in and changes the 2 Closed Won Opportunities to be Open or Closed Lost, our automation should update the Account Status to be ‘Prospect’ (since we aren’t left with any Closed Won Opportunities). - What should be the trigger for the automation?
The creation of new Opportunity or a change in an existing Opportunity’s stage should trigger the automation. This would be our Process/evaluation criteria. - Which automation tool/(s) should we use to implement this requirement?
As mentioned before, since we need to loop through multiple records, the Process and Flow Designer combo (Auto Launched Flow) is our only declarative option here and the created Flow will be launched via the Process Builder.
Based on what we have gathered so far, this is what the complete automation flow looks like:
Having a good sense of the implementation design, let’s get down to business.
CREATING THE FLOW:
We have five key variables in this Flow:
- vAccountId for receiving the Account Id from the Process (Text type)
- vCtr as a counter variable (Number type)
- vRelatedOpps for storing all the Opportunities related to the vAccountId (sObject Collection type)
- vEachOpp as an sObject loop variable (sObject type)
- ctrIncrement as a formula value (Number type)
- Find all the Opportunities related to the Account (vAccountId ) and store them in a collection variable (vRelatedOpps)
Fast Lookup - Loop through the collected Opportunities and for each Opportunity in the collection, check if the Opportunity is Closed Won or not. If it is Closed Won, increment the counter by 1, otherwise move to the next Opportunity in the collection. Do this until we have looped through all of the Opportunities in our collection.
Loop through the Opportunity collection 
Check if Opportunity is Closed Won 
Increment Counter 
Connected Elements View - Check the final count of Closed Won Opportunities. If there is even a single Opportunity set to Closed Won which means that the counter value >=1 so update the Account Status to be Customer. Otherwise, update the Account Status to be Prospect.
Do we have >=1 Closed Won Opportunities? 
Yes -> It means our Account should be set to a Customer. 
No -> It means our Account should be set to a Prospect. 
Connected Elements View
CREATING OUR PROCESS THAT LAUNCHES THE FLOW:
See how simple it was? With the help of Process Builder and Flow, we were able to build something that would have otherwise required Apex code. Such kind of automation is a boon for the sales reps and data analytics since it eliminates the effort of manually setting the Account status whenever a related Opportunity’s stage is modified or a new Opportunity is created. If you were able to implement a similar requirement using another approach, please feel free to share it in the comments below.
Happy Flow’ing!
Related/Similar Answer Community questions:
Funky solution. However, this won’t really work if an opportunity is deleted. Deletions do not fire Process Builder, hence the associated account may end up with the wrong status in certain circumstances.
Hi Mayank, Thanks for this. This is a very useful solution. I tried to apply it to cases object to approve a parent case once all the child cases are approved. Instead of the decision for opt closed-won I check for child cases pending approval and increment the counter every time there is a pending approval child found. At the end if the counter is=0 I update the parent to approved. But when I run my test nothing happens even though all the child cases are approved. Is there anything I am missing. Thanks in advance.
Mayank, This looks really helpful, and I am wondering how I could use this to update an Account with a related Contact “Last Activity” date. Use case: tracking active accounts vs non active by Last Activity Date. If an account has 5 contacts, and one of them has an activity logged, I want the date the activity was logged to appear on the Account. If a different contact is updated, their activity date should appear on the account and update the field accordingly.