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.
Best Answer
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.