Salesforce Automation – Running Total of Sub-Records
Hi there, my name is Cam and I’m a senior Salesforce Consultant with Marketing Logic. I have a deep passion for automation, and I believe that if you can automate it, you should. A well built Salesforce automation provides an enhanced user experience that can be the difference between someone adopting a solution and overlooking it.
In this blog, I’ll be posting interesting automation use cases, with instructions on how to re-create them.
This week I’ll be looking at how to establish a running total of sub-records. At first, this might seem like a solved problem, and in fact, there are 2 functions as default in Salesforce automation that you can use to count totals: roll-up summaries and auto-numbers.
Before we continue let’s look at the problem we’re trying to solve.
The customer has asked for us to create a field on an event. It needs to keep a running total of how many events have occurred on a specific account. We’ll use meetings as an example of this.
So for every meeting that occurs, we want the field on that meeting record to display which number in the sequence it was, and we don’t want that number to change so we can report on it down the line.
- Meeting 1 – Displays 1
- Then Meeting 2 – Displays 2
- Finally, Meeting 3 – Displays 3
As I previously mentioned there are two field types that could potentially solve this problem, but as we look closer we realise that they don’t quite meet the use case.
Roll-Up Summary (Count):
This only works on the master in a master-detail relationship, and as it happens events can’t be rolled up.
This would assign each new event a unique number, formatted how we choose. At first glance, this may seem tempting, but unfortunately, it will give a count of all the records, not the subset associated with our account, so that’s out.
What we have is a perfect use-case for a simple piece of flow automation, so let’s look at how this can be solved:
Firstly, we’ll need to create a new field on our event object. The event object itself doesn’t support custom fields, as it is technically an activity, so that’s where we’ll create our field. Depending on your setup you’ll need to set the character limits, and field-level security to match your org.
Now that we have our destination field, we need to create a flow that will update this field.
Go to Setup > Process Automation > Flows and click on “New Flow”
Assuming you’re using the latest release (Summer ’20) of Salesforce you will want to choose “Record-Triggered Flow”
The reason for this is that we want the flow to trigger when a record is created, and we’re going to use the following settings:
We only want to run this flow when the record is created, otherwise every time the meeting is edited our numbers will change and the data will be incorrect.
Next, we need to get the event records. We want to retrieve all the records associated to our account. We only want events that are using the “Pitch / Demo – Booked” type. You can set whatever parameters you want here to specify which records should be included in your count.
You want to match the “WhatId” with the account ID associated to the record that initiated the flow. This means that only records associated with that account are included and not all records.
We want to store all the records as we’ll need to loop through them to perform our count.
Now we need to create a loop that is going to iterate over our collection and perform the count, so add in a “Loop” element and link it to your “Get” element. From Summer ’20 you no longer need to create your own loop variables, Salesforce does this for you.
Your flow should now look something like this:
Now we need to perform the actual count. In flow you need to create an assignment element to do the count before you can update the record, so add an assignment element underneath where your loop is.
You’ll need to create a resource to add up the numbers:
Then configure your assignment to add 1 for every time the loop iterates:
So now, every time our loop hits the assignment element it will add 1 to our count variable.
Your flow should now look something like this:
Remember to link your assignment back to the loop, otherwise, it will only run once, it is a loop after all!
Finally, we need to update the record that initiated the flow in the first place. We need to configure our update to put the value of our “count” variable into the field that we created.
**As a side note, that loop variable will include the record you just created, as this flow is triggered after the record is saved, so there is no need to do any additional calculations to add 1 onto our variable.
You can see that we’ve used the record flow variable to match the ID of the record we want to update, then placed the value of “count” into the field we created at the start.
Now link this to your “loop” element. Our final flow should look like something like this:
**I am a big proponent of tidiness within flows in Salesforce automation. At first glance, you should be able to gain a reasonable understanding of what a flow does. Take time to think about the layout, especially if other people could potentially be updating/troubleshooting this.
Now, we can finally activate it. You should, as best practice and where possible, debug a flow to ensure it is working as designed. Feel free to do that first before you deploy to confirm.
This can be adapted and applied to any object, with any lookup on that object as the parent.
So that’s it for this week’s post on Salesforce automation. I hope you found it useful and gained a better understanding of how loops and assignments work. This is a fundamental and very powerful function of the flow builder. Make it your bread and butter as you continue to expand your knowledge.
I’ll be back next week with another Salesforce automation post, so check back regularly.