You go through life, and it sometimes feels like drinking from a firehose. This is also through for systems. The current work can overwhelm the available resources. Salesforce Scheduled (a.k.a. Schedule-Triggered) flows are an excellent way to defer some of the work and give your system resources a breather. I discussed this solution this past weekend in Minneapolis at Midwest Dreamin’ but needed more time to go into a step-by-step tutorial. Let me describe the use case and the solution in detail here.
You need to track key performance indicators for your operation daily and build a history. Out-of-the-box reporting functionality and report snapshots are not powerful enough for you. In addition, you want to perform sophisticated mathematical calculations and expand your solution in the future to include details from related object records as well. You want to leverage something other than rollups as the value of a rollup field changes dynamically. In conclusion, you decided to track these KPIs for cases daily:
- Count of Created: Created in the previous 24 hours.
- Count of Modified: Modified in the previous 24 hours.
- Count of Open: Count of open cases at midnight.
- Count of Closed on Date: Closed in the previous 24 hours
- Max Wait Open in Hrs: Max Wait on the oldest open case at midnight.
- Average Wait Open in Hrs: Average Wait of all open cases at midnight.
The solution has two essential components:
1) A custom object record that tracks the results
2) A schedule-triggered flow that creates a single record every night
Let me show you:
1) See the screenshot below to review all the Daily Summary Record custom object fields.
You will create this custom object to house the resulting values.
2) Flow solution: The flow solution is a very efficient one. Many don’t know that selecting an object is optional in schedule-triggered flow. For this use case, you will use a flow without an object that gets all the case records in the org. If you don’t have more than 50K cases, you won’t have a problem with governor limits. If you know you will get more than 50K cases, we will have to split this flow into several flows and/or perform multiple targeted gets. You will leverage collection filters and need only one single get.
See the screenshot below to review all the elements in this flow (click to see a larger image).
You will create the elements as follows:
- Start element: Scheduled daily at midnight: No object and no criteria.
- Get the previous daily summary record: This is only necessary to get the created date to filter cases processed since the last run.
- Get all cases in the Org by ascending Created Date order.
- Collection filter 1: Filter for created cases since the last run.
- Collection filter 2: Filter for modified cases since the last run.
- Collection filter 3: Filter for closed cases since the last run.
- Assignment 1: Assign the counts of the collections to variables.
- Filter Open Cases
- Loop Open Cases
- Assignment 2: Add 1 to the counter, calculate case wait, and add to the total wait variable
- If this is the first iteration, capture the current wait as the max wait. This works because our get is sorted (see above).
- Create one single Daily Summary Record.
Now you can accumulate this information in your Org as long as you want for reporting and audit purposes. This is a very elan record that won’t consume significant storage.