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.

  • 1
    The question would be, what kind of frequency do you want to update your accounts with open opportunities on? A trigger can only fire based on DML and would normally only update the Account it fired on while a batch class could be scheduled to run on any frequency you want it to. What have you tried and where are you stuck with your requirements? – crmprogdev Mar 23 '16 at 12:38
  • @SergeyUtko I have created a custom field on opportunities that can get updated with 0, 1, 2 or 3 according to the opportunity stage. But I'm stuck as to how I can count if all stages are 0, 1, 2 or 3 from account. – dipoisola Mar 23 '16 at 12:42
  • @crmprogdev I have created a custom field on opportunities that can get updated with 0, 1, 2 or 3 according to the opportunity stage. But I'm stuck as to how I can count if all stages are 0, 1, 2 or 3 from account. Is there a better way? – dipoisola Mar 23 '16 at 12:43
up vote 2 down vote accepted

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.

  • Don't have enough rep to add comments to someone else's answer... but looking at your comment on Maneesh's answer I would recommend a couple of things. First off, it appears you will only need to adjust the parent account when the status of an opp changes, so check that first. If the opp stagename is changed, add the account ID to a set for processing later in the trigger. Then do a query later on to get all of the relevant opps you need to check. Loop through every account that is affected, add them to a list with the appropriate values, and update. – Jake Hebert Mar 23 '16 at 13:56

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

enter image description here enter image description here

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

enter image description here enter image description here

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

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.

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?

  • That's all Ihave done. Obviously it is wrong. trigger t1 on Opportunity (after insert, after update) { for(Opportunity op : [SELECT StageName, Service_End_Date__c, AccountID From Opportunity Where Id IN: trigger.new]) { if(op.StageName == "Closed Won") { } } } Can you please give an idea how your for loop could have been and how you'd use the Map. @ManeeshGupta – dipoisola Mar 23 '16 at 13:28

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.