There are a lot of posts floating around about the lack of support for formula fields in Roll-up Summary fields but not the other way around. I had a unique Use Case that I spent the better half of a day troubleshooting so I thought I’d share my experience. Hopefully, someone can benefit from my efforts.
Need to monitor elapsed time between Account creation and first Closed Deal for reporting and KPI Measures. Create a field on Account that stores the elapsed time in days between Account creation date and first Opportunity Close Date. For maximum flexibility and customization, achieve the solution without custom code.
Let’s take a look at the solution.
That’s it! Looks simple enough right…. except it does not fire. Why not?
We have Test Account A with CreatedDate = 2/1/2018 with 3 Opportunities
So our Account field values are:
Now we update Opportunity A’s Close Date to 2/5/2018, making it the oldest Close Date so our field on Account should be updated as follows
But what we see is this
The roll-up summary field is updated but Lead Time in Days is not. Read on to understand why.
The process runs and checks the value of the roll-up summary field (3/1/2018) against the newly updated Close Date (2/5/2018) but the process exits without performing any updates. Order of execution…
The order of execution states that Roll-up Summary fields are updated on the Parent (and Grandparent if applicable) AFTER triggers, workflows, and processes. So the above process fails to execute even when the Close Date we are setting our Opportunity to is changing to be the oldest one on the Account because the Roll-up Summary field has not been updated yet.
So how can we achieve this without code?
Great idea in theory however it does not work as it only fires when a record is created, not updated.
Update the existing process to only fire when the Opportunity Close Date is changed. Create an action that sets a new checkbox field on Account to true. Then create a new Process that fires on Account when the checkbox is true. In the new process, set the Lead Time in Days to the difference between the Roll-up Summary field and CreatedDate.
Create a new process that fires on Account when ‘Check for New Closed Opps’ is true. Add an action to set the Lead Time in Days field based on Account roll-up summary field and CreatedDate. Now it will be accurate as our roll-up summary value was updated after the first process.
For your reference, here is an article to the order of execution rules.
The second solution, while a little more involved, works!! And does not require any code! Happy Process Building! Remember, if you need help implementing Roll-up’s in Salesforce, we are here to help.
Sign up for weekly OpFocus blog updates!