The Package-free, Code-free, Declarative Lookup Rollup
Is that title a tongue twister? This scenario is all to common: for one business reason or another, #AwesomeAdmins need some way to rollup up a count or summary from a “child” object in a lookup relationship. Let’s take a very simple scenario to walk though - how many contacts does each account have? While a report can easily pull this information, automation cannot be run off of a report.
Before diving into how to create a declarative rollup without code or installed packages, I want to note a couple good options out there.
You can write a trigger, but perhaps you, like me, do not currently code…moving on.
You can install the Declarative Lookup Rollup Summaries Tool from the AppExchange. This is a great tool that dynamically writes triggers for you based on declarative input. The options may be a bit daunting at first, but there are a number of experts on the Trailblazer Community happy to help if you get stuck.
Another option on the AppExchange with a freemium model is the Rollup Helper.
The Declarative Option - Process Builder & Flow
The concept is fairly straightforward. We will create a process builder off of the contact object that loops through contacts related to the account, sums up the total, and stores the value on the account. The challenge I have faced with this solution for years is how to run the process builder on deletion of a contact. Natively, the only options are create and edit. What about delete?
At a high level, here is the solution I built: replace the native delete button with a custom quick action that marks a check box. When that checkbox is marked, delete the record in the flow then run loop to count the child records.
4 hours of experimenting later, yes, this does in fact work in my Trailhead Playground for adding new records and deleting using my custom button!
The Underlying Customization
Create a new field on the Contact object:
Choose the field type: Checkbox
Field Label: Delete Contact
Default Value: Unchecked
Field Name: Delete_Contact (default)
Visibility: Make sure this field is visible for all profiles that will delete contacts
Page layouts: Uncheck all - this field does not need to be on any page layout
Create Quick Action:
Action Type: Update a Record
Standard Label Type: None
Label: Delete Contact
Name: Delete_Contact (default)
Success Message: Contact Deleted
Remove all fields from action layout.
Replace standard delete button with new quick action button on the page layout(s).
Create a new custom number field on the Accounts object and add it to the page layout(s). This is where the flow will store the value after counting the number of contacts.
The Process Builder and Flow
Now that all the the custom fields and the custom button are in place, we can create the process and flow.
Since the process will reference the flow, let’s create the flow first, so it is available to reference.
Create a new variables for recordId and AccountID which will be passed through from the Process Builder.
Use Fast Lookup to find the original contact record and a decision based on the checkbox field to determine if the contact should be deleted.
Delete contact record when “Delete Contact” is set to true.
Use a Fast Lookup to find all records where the contact is related to the same account as the original contact.
Create a loop to go through the contacts and a new loop variable.
Create a variable for counting the number of contacts
Loop through the contacts, adding one to the counter each iteration.
Follow a similar process to above, find the account with the account ID, assign the counter number to the Number of Contacts field, and update the account.
The flow will look like this:
The Process Builder
The process is simple with only one formula and one action. It uses the following formula to determine if the record is new or is being deleted and passes two variables into the flow. The purpose of the formula does not actually add any functionality, but rather prevents the flow from running unnecessarily.
OR( ISNEW(), [Contact].Delete_Contact__c = TRUE )
The last step to implementing a declarative rollup this way would be to mass update existing records. The easiest way to do this would be to remove the criteria from the process builder, then mass update all records, or at least one child record from each record relationship.
This method could also be used for summing amounts, finding a max or min, or calculating an average. The main component to update for different scenarios would be the math of the “counter” variable in the flow. Also, the formula in the flow might need to be updated to trigger on relevant field updates.
Anyone with delete access outside of the custom button will be able to bypass the flow counter. This especially includes data loader and related lists buttons.
As an admin, if you need to mass delete using the data loader, you could either delete by marking the checkbox as true, or you could recalculate the values by triggering the flow for all records like the first time setting it up.