1. LeanData Help Center
  2. Orchestration
  3. Routing

Orchestration - Formula Builder

Updated
Have more questions? Submit a request

Contents:

 

Overview

LeanData's Formula Builder enables you to perform calculations and manipulate text values directly within FlowBuilder. These functions allow you to create dynamic routing logic, stamp calculated values on fields, and include dynamic content in email notifications.

With Formula Builder you can:

  • Perform mathematical calculations using variables or field values
  • Extract specific characters from a text field to create custom identifiers
  • Calculate aggregate values like sum, average, minimum, and maximum
  • Combine multiple text fields with proper formatting for email notifications and field updates
  • Build more sophisticated routing logic based on calculated values
  • Calculate date differences or add days to existing dates using nested functions

 

Accessing the Formula Builder

The Formula Builder is available in the following FlowBuilder nodes under the Insert menu:

  • Update Record Node
  • Set Variable Node
  • Decision Nodes (when comparing against a calculated value)
  • Send Notification Node (and within notifications in other Action Nodes)

To open the Formula Builder:

  1. Navigate to one of the supported nodes in your graph
  2. Click the Insert dropdown
  3. Select Formula Builder

 

 

Building Your Formula

The Formula Builder modal guides you through creating formulas step by step:

 

Selecting a Function

When you open the Formula Builder, you'll see a list of available functions.

Select the function you want to insert into your formula, and the builder will present the required inputs needed to build that function.

 

Entering Inputs

After selecting a function, the builder will prompt you to enter the required inputs. These will be the values that you want to act upon with your function. While these inputs will differ depending on which function you selected, generally, you can:

  • Select whether you want to use the function with a Collection or with Values / Variables
  • If using with Values / Variables, enter each input as a value or select the variable or field you wish to get the value from
  • Add more Values / Variables if needed in your function
  • Add any additional details that may be required for your particular function, such as an operator or separator
  • Nest other functions within your function (see Using Nested Functions below)
  • Click Add Function to add the function to the formula editor at the bottom of the modal

Available Functions

Average Function

Syntax: [!AVG({!x},{!y},{!z}...)]

The Average function calculates the mean value of multiple numeric values.

First, select whether you want to use this with a Collection or with individual Values / Variables.

Collection

If using with a Collection, you must first identify a Collection variable in a previous Get Records node. Within that Collection Variable, select the field you want to average across all records in the collection.

Value / Variable

If using with values or variables, enter the individual values you want to average, or select the variable / field that contains the values you want to average. To include more values or variables in your average, use + Add Value / Variable.

Examples:

  • To calculate the average deal size across all opportunities on an account, the formula output would be:
    • [!AVG({!AccountOpportunities.amount})]
  • To find the average between the Lead Modified score and the Opportunity score:
    • [!AVG({!routed lead.modified_score__c}, {!matched opportunity.score})]

Click Add Function and your function will be added to the formula editor box.

 

Calculate Function

Syntax: [!CALC(({!x}+{!y}-{!z})/{!a})]

The Calculate function performs mathematical operations on variables or values. You can use standard mathematical operators including addition (+), subtraction (-), multiplication (*), and division (/). Collections are not supported with the Calculate function.

Enter the values you wish to use for your calculation. You may also get the values contained in variables or fields using the Variable option. Use + Add Value / Variable to include additional values or variables.

In the Function section, enter the mathematical operators you wish to use to calculate with each of your inputs. Each input will have a corresponding number representing where it should be used in your calculation.

Examples:

  • To calculate the commission amount based on deal value and commission rate:
    • [!CALC({!DealValue}*{!CommissionRate})]
  • To calculate a weighted score:
    • [!CALC(({!Score1}*0.4)+({!Score2}*0.3)+({!Score3}*0.3))]

Click Add Function and your function will be added to the formula editor box.

 

Concatenate Function

Syntax: [!CONCAT({!x},{!y},{!z}...,"separator")]

The Concatenate function combines multiple text values into a single string. You must include a separator as the last parameter, which will be inserted between each value. The separator is required even if you want to concatenate values without any separator (use an empty string "" in that case).

First, select whether you want to use this with a Collection or with individual Values / Variables.

Collection

If using with a Collection, you must first identify a Collection variable in a previous Get Records node. Within that Collection Variable, select the field whose values you want to combine across all records in the Collection.

Enter a Separator — this will appear between each value in your combined string. A separator is required, though you can use a space if you don't want any other character between values.

Value / Variable

When using values or variables, enter the individual values you want to combine or select the variable / field containing those values. To add more values or variables to your string, click + Add Value / Variable.

Enter a Separator — this will appear between each value in your combined string. A separator is required, though you can use a space if you don't want any other character between values.

Examples:

  • To create a string with the different products listed on each Opportunity on an Account, the output would be:
    • [!CONCAT({!AccountOpportunities.productname__c}, "|")]
  • To create a comma-separated list from City and State:
    • [!CONCAT({!City},{!State}, ", ")]
  • To combine values without a separator:
    • [!CONCAT({!AccountId},{!RecordType},"")]

Click Add Function and your function will be added to the formula editor box.

 

Sum Function

Syntax: [!SUM({!x},{!y},{!z}...)]

The Sum function adds multiple numeric values together and returns the total.

First, select whether you want to use this with a Collection or with individual Values / Variables.

Collection

If using with a Collection, you must first identify a Collection variable in a previous Get Records node. Within that Collection Variable, select the field whose values you want to sum across all records in the Collection.

Value / Variable

When using values or variables, enter the individual values you want to sum or select the variable / field containing those values. To add more values or variables to your sum, click + Add Value / Variable.

Examples:

  • To calculate the total of Opportunity amounts across all the Opportunities included in the Collection variable:
    • [!SUM({!AccountOpportunities.amount})]
  • To calculate a total score from multiple components:
    • [!SUM({!routed lead.leandata__modified_score__c}, {!matched opportunity.score})]

Click Add Function and your function will be added to the formula editor box.

 

Min Function

Syntax: [!MIN({!x},{!y},{!z}...)]

The Min function returns the smallest value from multiple numeric values.

First, select whether you want to use this with a Collection or with individual Values / Variables.

Collection

To use a Collection, first identify a Collection variable from a previous Get Records node. Then select the field whose values you want to compare to find the minimum.

Value / Variable

When using values or variables, enter the individual values you want to compare to find the minimum or select the variable / field containing those values. To add more values or variables for comparison, click + Add Value / Variable.

Examples:

  • To find the lowest amount among the Opportunities in a Collection:
    • [!MIN({!AccountOpportunities.amount})]
  • To return the smallest annual revenue between the routed Lead and its matched Account:
    • [!MIN({!routed lead.annualrevenue}, {!matched account.annualrevenue})]

Click Add Function and your function will be added to the formula editor box.

 

Max Function

Syntax: [!MAX({!x},{!y},{!z}...)]

The Max function returns the largest value from multiple numeric values.

First, select whether you want to use this with a Collection or with individual Values / Variables.

Collection

To use a Collection, first identify a Collection variable from a previous Get Records node. Then select the field whose values you want to compare to find the maximum.

Value / Variable

When using values or variables, enter the individual values you want to compare to find the maximum or select the variable / field containing those values. To add more values or variables for comparison, click + Add Value / Variable.

Examples:

  • To find the highest amount among the Opportunities in a Collection:
    • [!MAX({!AccountOpportunities.amount})]
  • To return the largest annual revenue between the routed Lead and its matched Account:
    • [!MAX({!routed lead.annualrevenue}, {!matched account.annualrevenue})]

Click Add Function and your function will be added to the formula editor box.

 

Count Function

Syntax: [!COUNT({!Collection})]

The Count function returns the number of records in a collection. It will not work with individual values or variables.

First, identify a Collection variable from a previous Get Records node. Then select the Collection whose records you want to count.

Example:

  • To find the number of Opportunities in a Collection:
    • [!COUNT({!AccountOpportunities})]

Click Add Function and your function will be added to the formula editor box.

 

Repeat Function

Syntax: [!REPEAT({!Collection}, "text template", "optional separator")]

The Repeat function generates formatted text for each record in a collection using a text template. You can reference collection fields within the template using the standard variable syntax. It will not work with individual values or variables.

Select the Collection variable you want to use with the Repeat function.

Enter a text template. You can directly enter text and insert variables into your text template.

To use a separator other than a new line, enter your custom separator in the Separator box. If you leave this empty, the separator will be a new line (represented by a newline character).

Example:

  • To create a list of opportunity names and amounts:
    • [!REPEAT({!AccountOpportunities}, "Opportunity: {!matched opportunity.name} - Amount: {!matched opportunity.amount}")]
  • This would generate output like:

Opportunity: Deal 1 - Amount: $50,000 
Opportunity: Deal 2 - Amount: $75,000 
Opportunity: Deal 3 - Amount: $100,000

Click Add Function to insert your completed function.

 

Relative Date Function

Syntax: [!Datetime.daysAfter([!Datetime.today()],{!numberofdays})]

The Relative Date function inserts a date relative to today.

Select whether you want to insert today’s date or a date relative to today, up to 365 days before or after today.

Example:

  • To output a date three days from today:
    • [!Datetime.daysAfter([!Datetime.today()],3)]

Click Add Function to insert your completed function.

 

Summary of Functions

The following table summarizes all the functions available in the Formula Builder:

Function Syntax Description
Average [!AVG({!x},{!y},{!z}...)] Calculates the average of multiple numeric values
Calculate [!CALC(({!x}+{!y}-{!z})/{!a})] Performs mathematical operations (+, -, *, /) on variables or values. Not supported for Collections.
Concatenate [!CONCAT({!x},{!y},{!z}...,"separator")] Combines multiple text values with a specified separator
Count [!COUNT({!Collection})] Counts the number of records in a collection (only available for use with Collections)
Max [!MAX({!x},{!y},{!z}...)] Returns the maximum value from multiple numeric values
Min [!MIN({!x},{!y},{!z}...)] Returns the minimum value from multiple numeric values
Relative Date [!Datetime.daysAfter([Datetime.today()],{!numberofdays})] Inserts a date relative to today
Repeat [!REPEAT({!Collection}, "text template", "separator")] Generates text for each record in a collection using a template (only available for use with Collections)
Sum [!SUM({!x},{!y},{!z}...)] Adds multiple numeric values together

For more information on how these functions operate with Collections, see the Get Records Node / Related Object Based Routing Guide.

 

Using Nested Functions

The Formula Builder supports nested functions, allowing you to build more complex logic by combining multiple functions together. This capability aligns with Salesforce Formula Builder functionality and enables you to handle sophisticated use cases.

For example, you can get the difference between the maximum amount from the Opportunities in a Collection and the minimum amount from the Opportunities in that Collection.

Example:

  • Find the difference between the maximum and minimum opportunity amounts:
    • [!CALC([!MAX({!AccountOpportunities.amount})] - [!MIN({!AccountOpportunities.amount})])]

 

Editing, Validating, and Inserting the Formula

Before inserting your formula, it appears in the formula editor at the bottom of the modal. This displays the complete formula syntax so you can verify it's correct.

The formula editor lets you edit directly and make changes easily.

As you edit, LeanData validates your formula syntax and highlights any errors.

Once you've finished editing and validating, click Insert Formula to add it to the node.

 

FAQs

Can I edit formulas created with the Formula Builder?

  • Yes. You can reopen the Formula Builder to modify existing formulas, or switch to freeform editing mode if you prefer to manually edit the syntax.

What happens if I make a syntax error?

  • The Formula Builder includes in-modal syntax validation that will alert you to errors before you insert the formula into your flow. This helps reduce syntax validation errors significantly.

Can I use the Formula Builder with Collections?

  • Yes. The Formula Builder is compatible with Collections.

How many functions can I nest together?

  • The Formula Builder supports nested functions to handle complex logic. While there's no specified limit mentioned, you can combine multiple functions as needed for your use case.

 

Best Practices

  • For complex calculations, break down the logic into smaller, nested functions that are easier to understand and maintain
  • Take advantage of in-modal validation to catch errors early in the formula creation process
  • When working with nested functions, build from the inside out — start with the innermost function and work your way outward
  • Use descriptive variable names when building formulas with Set Variable or Get Record nodes to make your formulas easier to understand

For questions or additional assistance please contact LeanData Support.

Articles in this section

See more
Was this article helpful?
0 out of 0 found this helpful
Share