Last updated on January 9, 2023
DESCRIPTION
You have a status picklist field on the Account object with two values Active and Inactive. And you want to update the account status to “Inactive” automatically if an account does not have any closed won opportunity for 180 days or no opportunity created in 180 days.
Solution
Step 1
Create a rollup summary field on the account object, that will show the most recent closed date of the opportunity where stage = “Closed Won” using the MAX option on the roll-up summary field.
Rollup Summary field: Most Recent Closed Date
Step 2
Create a formula field on the account object that returns a number (0 decimal) which is the number of days. The formula checks if the account has at least one closed won opportunity, then returns the number of days since the opportunity closed (using the rollup summary field created in step 1). If no closed won opportunity exists, then use the account created date and return the number of days since the account was created.
Formula field: Closed Won Opp Since
IF( ISBLANK(Most_Recent_Closed_Date__c),
TODAY() - DATEVALUE(CreatedDate),
TODAY() – Most_Recent_Closed_Date__c
)Step 3
Create a scheduled flow that should be scheduled to run daily. And put the criteria that if Closed_Won_Opp_Since__c > 180 and the status does not equal “Inactive”, then update the account status to Inactive. You can set the start date of the flow to any preferred date time.
Now this scheduled flow will run daily and check, if any active opportunity has no closed won opportunity created for 180 days, then update the account status to Inactive.
Hope you liked the solution!