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.

BUSINESS USE CASE

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.

SETUP

Create Account Custom Field

• Name: Oldest Opportunity Close Date
• Type: Roll-up Summary
• Field to aggregate: Opportunity Close Date
• Summary Type: MIN

Create Account Custom Field

• Name: Lead Time in Days
• Type: Date

Create Process Definition

• Name: Set Account Lead Time in Days
• The Process Starts When: A record changes
• Add Object: Opportunity (Select when a record is created or edited)
• Add Criteria (Select Conditions are met):
  • IF Opportunity Close Date Is Changed
  • AND
  • IF Opportunity Accounts Oldest Closed Date EQUALS Opportunity Closed Date
• Add Immediate Action
  • Select Opportunity -> Account ID for the record
  • Select No criteria – just update the records!
  • Set field Lead Time in Days to be the difference between the Opportunity Close Date and the Account Created Date.
    • Type: Formula
    • Formula: [Opportunity].CloseDate – [Opportunity].Account.CreatedDate

That’s it! Looks simple enough right…. except it does not fire. Why not?

The Problem

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 Explanation

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?

The Solution

Solution #1:  Use a Time-based Process.

Great idea in theory however it does not work as it only fires when a record is created, not updated.

Solution #2: Update the Process and Create a New One

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 Account Custom Field

• Name: Check for New Closed Opps
• Type: Checkbox
• Default: Unchecked

Update Existing Process

• Change the entry criteria to only be if the Close Date on the Opportunity has been updated.
• Remove the previous action
• Create a new immediate action that sets  Account’s ‘Check for New Closed Opps’ field to true.

Create New Process Definition

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.

• Name: Update Oldest Opp Closed Date
• The Process Starts When: A record changes
• Add Object: Account (Select when a record is created or edited)
• Add Criteria (Select Conditions are met):
  • IF Check for New Closed Opps is true
• Add Immediate Action
  • Select Action Type: Update Records
  • Record Type: Select the Account record that started your process
  • Select No criteria – just update the records!
  • Set field Lead Time in Days to be the difference between the Oldest Opportunity Close Date and the Account Created Date.
    • Type: Formula
    • Formula: [Account].Oldest_Opportunity_Close_Date__c – Account.CreatedDate
  • Set Check for New Closed Opps field to false

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.