How to update the status of an Account based on opportunities

Question

I want to be able to update the status of an account based on condition of all opportunities in an account.

According to the following, I want to be able to set an account status field as Active if Opportunity stage is closed won, and the service end date or revised service end date is less than today etc.

1) Active - Opportunity stage is closed won, and the service end date or revised service end date is less than today 2) Open - No opportunity stage closed won or lost, have 1+ opportunities that are in the A-E status 3) Inactive - All opportunity stage are closed won, and service end date(s) are greater than today 4) Lost - All opportunity stages are closed lost.

Answer 1

Since you are trying to update a parent record based on the entire group of related child records it seems to me you will need to use a query inside of an apex class or trigger.

The way I would likely go about solving this would be to put a trigger on the opportunity object. Any time an opportunity is inserted or updated with a different status you will need to add the parent account ID to a set. After all your trigger logic fires, you will need to query all opps related to any parent account in your set and loop through all of the opp statuses like Maneesh said.

There may be some way to muscle in a solution using workflows or rollups, but anytime you are trying to do logic with a group of child records I have found that code and a query is required.

Answer 2

We have something similar setup in our environment. I would advise that you triple check your logic that it is mutually exclusive and exhaustively complete - i.e. solve for every scenario. You would eventually end up with boolean logic that can be implemented. Make sure you consider multiple opportunities!

We used roll-up summaries fields to count the number of opportunities that fell into a specific categories. These roll-up summary fields will update real-time when you change your child opportunity stages.

Roll-up Summary Fields

We then used workflow rules with the logic defined in the beginning. This logic ended up being AND(Total_CLosed_Won_Opportunities__c > 0, Total_Pending_Opportunities__c <= 0, Total_Inactive_Opportunities <=0, Total_New_Opportunities <= 0) then set the Account Status to 'Client'. The scenario you are asking for would have your own logic and requirements but the same goal.

Workflow Rules

This solution would not require any code and would be very light weight. I would suggest this over anything else.

Answer 3

I would think this is best done with a query, particularly since Opportunity is a child of Account. Were it me, and I wanted this updated for all accounts regularly, I'd do it asynchronously using a scheduled batch or queueable class. I'd query first on open Opportunities sort the returned results by the value of your custom field (0 - 3), then by accountId and OpportunityID and Count() making it easy to sort and update your accounts.

You'd want to put all of your Active Opportunity Account Id's into a set and test to see if any of your Accounts in the set have Ids in a Set matching your Open condition. If they do, you'll want to remove them from Active set before update. You may want to run a 2nd query for the last two conditions instead of doing it all as a single query, but I'll leave that up to you.

You could use the same kind of approach in a trigger, be it a roll-up or an after update trigger. I just don't think you need the information in real time, but that's business decision for you to make.

Answer 4

I think you can use a Map here and an After Update/Insert trigger. After an Opportunity record is inserted or updated, you use the AccountID on that opportunity record and find all the other Opporunites for that Account.

Then using simple If-Else logic, you should be able to find out the right status for the Account.

Does that help?