Work With Aggregate Functions in Salesforce Flow

Last Updated on September 15, 2022 by Rakesh Gupta

Big Idea or Enduring Question:

• How to work with the aggregate functions in Salesforce Flow?

Objectives:

After reading this blog, you’ll be able to:

• Find out the NUMBER (Count) of rows for query criteria.
• Find out the MAXIMUM value of a field for query criteria.
• Find out the MINIMUM value of a field for query criteria.
• Find out the AVERAGE value of a numeric field for query criteria.
• Find out the SUM of a numeric field for query criteria.
• And much more

What are Aggregate Functions? 

A SOQL aggregate function calculates the values of multiple rows grouped as input on specific criteria to form a single value. Various Aggregate Functions are: 

1. COUNT – Returns the number of rows matching the query criteria.
2. MAX – Returns the maximum value of a field.
3. MIN – Returns the minimum value of a field.
4. AVG – Returns the average value of a numeric field.
5. SUM – Returns the total sum of a numeric field.

As of the Winter’23 release, Salesforce Flow does not support aggregate functions like SOQL. At the same time, there are lots of situations where you just want to calculate the count of rows in the Record Collection Variable or find out the average amount of opportunities for an account – such scenarios can be fulfilled without using aggregate functions. 

Business Use case I (MAX Function)

Jason Herr is a Solution Architect at Gurukul on Cloud (GoC). He wants to understand how to find the opportunity with the maximum amount for a given account. 

Automation Champion Approach (I-do):

We will use a Get Records element to solve the above business requirement. It doesn’t matter what type of flow you are working on, but the logic is the same. 

Step 1: Define Flow Properties   

1. Click Setup.
2. In the Quick Find box, type Flows.
3. Select Flows, then click on the New Flow.
4. Select the Record-Triggered Flow option, click on Create and configure the flow as follows:
   1. Object: Account
   2. Trigger the Flow When: A record is created or updated
   3. Set Entry Criteria
      1. Condition Requirements: None
   4. Optimize the Flow For Fast Field Updates
5. Click Done.

Step 2: Adding a Get Record Element to Find the Maximum Opportunity Amount

The next step is to use the Get Records element to find the maximum opportunity amount on a given account. 

1. On Flow Designer, click on the +icon and select the Get Records element.
2. Enter a name in the Label field; the API Name will auto-populate.
3. Select the Opportunity object from the dropdown list.
4. Select All Conditions Are Met (AND). 
5. Set Filter Conditions
   1. Row 1:
      1. Field: AccountId
      2. Operator: Equals
      3. Value: {!$Record.Id}
6. Sort Opportunity Records
   1. Sort Order: Descending
   2. Sort By: Amount
7. How Many Records to Store:
   1. Select Only the first record
8. How to Store Record Data:
   1. Choose the option to Automatically store all fields. 
9. Click Done.

In the end, Jason’s Flow will look like the following screenshot:

Edge Communications account has four opportunities, and the Maximum value is $100,000.

It’s time to Debug the flow and validate the outcomes:

Business Use case II (MIN Function)

Jason Herr is a Solution Architect at Gurukul on Cloud (GoC). He wants to understand how to find the opportunity with the minimum amount for a given account. 

Automation Champion Approach (I-do):

We will use a Get Records element to solve the above business requirement. It doesn’t matter what type of flow you are working on, but the logic is the same. 

Step 1: Define Flow Properties   

1. Click Setup.
2. In the Quick Find box, type Flows.
3. Select Flows, then click on the New Flow.
4. Select the Record-Triggered Flow option, click on Create and configure the flow as follows:
   1. Object: Account
   2. Trigger the Flow When: A record is created or updated
   3. Set Entry Criteria
      1. Condition Requirements: None
   4. Optimize the Flow For Fast Field Updates
5. Click Done.

Step 2: Adding a Get Record Element to Find the Minimum Opportunity Amount

The next step is to use the Get Records element to find the minimum opportunity amount on a given account. 

1. On Flow Designer, click on the +icon and select the Get Records element.
2. Enter a name in the Label field; the API Name will auto-populate.
3. Select the Opportunity object from the dropdown list.
4. Select All Conditions Are Met (AND). 
5. Set Filter Conditions
   1. Row 1:
      1. Field: AccountId
      2. Operator: Equals
      3. Value: {!$Record.Id}
6. Sort Opportunity Records
   1. Sort Order: Ascending
   2. Sort By: Amount
7. How Many Records to Store:
   1. Select Only the first record
8. How to Store Record Data:
   1. Choose the option to Automatically store all fields. 
9. Click Done.

In the end, Jason’s Flow will look like the following screenshot:

Edge Communications account has four opportunities, and the Minimum value is $35,000.

It’s time to Debug the flow and validate the outcomes:

Business Use case III (Count Function)

Jason Herr is a Solution Architect at Gurukul on Cloud (GoC). He wants to understand how to count closed won Opportunity records for a given account. 

Automation Champion Approach (I-do):

We will use a Get Records element to solve the above business requirement. It doesn’t matter what type of flow you are working on, but the logic is the same. 

Step 1: Define Flow Properties   

1. Click Setup.
2. In the Quick Find box, type Flows.
3. Select Flows, then click on the New Flow.
4. Select the Record-Triggered Flow option, click on Create and configure the flow as follows:
   1. Object: Account
   2. Trigger the Flow When: A record is created or updated
   3. Set Entry Criteria
      1. Condition Requirements: None
   4. Optimize the Flow For Fast Field Updates
5. Click Done.

Step 2: Adding a Get Record Element to Find Out Closed Won Opportunity 

The next step is to use the Get Records element to get all opportunity records where stageName=Closed Won. 

1. On Flow Designer, click on the +icon and select the Get Records element.
2. Enter a name in the Label field; the API Name will auto-populate.
3. Select the Opportunity object from the dropdown list.
4. Select All Conditions Are Met (AND). 
5. Set Filter Conditions
   1. Row 1:
      1. Field: AccountId
      2. Operator: Equals
      3. Value: {!$Record.Id}
   2. Click Add Condition
   3. Row 2:
      1. Field: StageName
      2. Operator: Equals
      3. Value: Closed Won
6. How Many Records to Store:
   1. Select All record
7. How to Store Record Data:
   1. Choose the option to Automatically store all fields. 
8. Click Done.

Step 3: Adding an Assignment Element to Find Out the Number of Rows in Record Collection Variable 

The next step is to find out the record count in the record collection variable. For this, we will use an Assignment element. 

1. Create a Number Variable varN_Count.
2. Click on the +icon and select the Assignment element on Flow Designer.
3. Enter a name in the Label field; the API Name will auto-populate.
4. Set Variable Values
   1. Row 1:
      1. Field: {!varN_Count}
      2. Operator: Equals Count
      3. Value: {!Find_Closed_Won_Opportunity}
5. Click Done.

In the end, Jason’s Flow will look like the following screenshot:

Edge Communications account has three closed won opportunities.

It’s time to Debug the flow and validate the outcomes:

Business Use case IV (SUM Function)

Jason Herr is a Solution Architect at Gurukul on Cloud (GoC). He wants to understand how to determine the sum of closed won opportunity amounts for a given account. 

Automation Champion Approach (I-do):

We will keep using the flow we created for business use case III. Let’s add two more elements, Loop and Assignment, to calculate the summation. 

Step 1: Add a Loop Element to Extract the Records from Record Collection Variable

1. Create a Number Variable varC_SumOfAmount.
2. On Flow Designer, click on the +icon and select the Loop element.
3. Enter a name in the Label field; the API Name will auto-populate.
4. For Collection Variable, select {!Find_Closed_Won_Opportunity}.
5. For Specify Direction for Iterating Over Collection, select the option First item to last item.
6. Click Done.

Step 2: Adding an Assignment Element to Calculate the Sum of Opportunity Amount

1. Under For Each Node, click on the +icon and select the Assignment element on Flow Designer.
2. Enter a name in the Label field; the API Name will auto-populate.
3. Set Variable Values
   1. Row 1:
      1. Field: {!varC_SumOfAmount}
      2. Operator: Add
      3. Value: {!Loop.Amount}
4. Click Done.

In the end, Jason’s Flow will look like the following screenshot:

Edge Communications account has three closed won opportunities worth $225,000. 

It’s time to Debug the flow and validate the outcomes:

Business Use case V (AVG Function)

Jason Herr is a Solution Architect at Gurukul on Cloud (GoC). He wants to understand how to determine the average amount of closed opportunities for a given account.  

Automation Champion Approach (I-do):

We will keep building the flow we created for business use cases III & IV. Let’s add one formula field to calculate the average. 

Step 1: Formula to Calculate the Average

1. Click New Resource to calculate the average amount of closed won opportunities. 
2. Input the following information:
   1. Resource Type: Formula
   2. API Name: forC_AverageAmount
   3. Data Type: Currency
   4. Formula: {!varC_SumOfAmount}/{!varN_Count}
3. Click Done.

In the end, Jason’s Flow will look like the following screenshot:

Edge Communications account has three closed won opportunities worth $225,000. The average amount is $75,000. 

It’s time to Debug the flow and validate the outcomes:

If the above solution doesn’t work for you, I highly recommend using the Unofficialsf SOQL plug-in. 

Formative Assessment:

I want to hear from you!

What is one thing you learned from this post? How do you envision applying this new knowledge in the real world? Feel free to share in the comments below.