Extend Formula-building to Non-Admins with Formula Builder, Expression Builder and the ‘Evaluate Formula’ Action
Introduction
You can use formulas in both Flow….
and Process Builder:
But these tools only are available to admins building flows and processes. Recently we’ve been tinkering with rich flow-based applications where we wanted to let users who are running flows create formulas and see them put to use. These use cases tend to involve flows that automate or improve on various pieces of Salesforce administration. For example, we’re prototyping an approval process that’s based on flow. In the classic Salesforce Approval Processes feature, you can specify the Entry Criteria for an Approval Process with a formula:
In the Flow-based approach to approval processes, it’s in a flow where this entry criteria formula gets entered:
The Formula Builder component visible above outputs a single formula string that can be stored for later use (in this case, to be used each time a record is submitted for approval). At that later point, in a separate flow, the string is passed to an Evaluate Formula invocable action that processes the formula string and returns a result.
Deep Dive Video
Documentation
This package provides a set of tools that provide graphical formula and expression creation as LWC’s and also provides a portable, Apex-based formula engine. This package is optimized for use in Flow but these LWC’s can be useful across a range of Lightning applications.
This package includes three main elements:
Evaluate Formula invocable Action
There are also some demo flows you can use.
Formula Builder
Add the Formula Builder to Flow screens and store the resulting formula string for later evaluation by the Evaluate Formula invocable action.
In the Approvals example shown above, the formula is being defined as part of a flow that creates the approval definition. The formula string is stored as one of the pieces of the definition as a string in a normal record. Later, when an object is submitted for approval, a separate flow is triggered. That flow loads the appropriate Approval Process Definition record, extracts the formula string from its field, and passes it to an invocable action called Evaluate Formula. This action takes as its input a formula string (such as the one defined above) and two optional attributes:
Attributes supported on the formulaBuilder LWC
| formulaValue | The formula string |
| contextObjectType | Standard or custom object API name. Fields from this object will be displayed in the Fields picklist of the formula builder, and at save time, the field references will be saved as part of the formula string. For example, if this is “Quote”, then user will be able to select from Quote object fields in the Insert Field picklist, and insert (or type) values like “Quote.Value”. When Evaluate Formula invocable action evaluates a string, it will look for a passed-in recordId value and use that to replace tokens like Quote.Value with the appropriate runtime value. The solution currently supports a single contextObjectType. |
| supportedSystemTypes | Specify in a comma separated list the names of the system variables you want users to also be able to use in the formula. Values supported by the Evaluate Formula action at this time are User, Organization, Profile, Setup. The formula builder will add these to the Insert Field picklist and allow the insertion of tokens like $User.userid. Evaluate Formula will use the context of the running user to replace these tokens with appropriate values. |
Supported Functions
Currently, the Formula Builder and Formula Evaluator support the following functions:
| ‘AND’, ‘OR’, ‘NOT’, ‘XOR’, ‘IF’, ‘CASE’, ‘LEN’, ‘SUBSTRING’, ‘LEFT’, ‘RIGHT’, ‘ISBLANK’, ‘ISPICKVAL’, ‘CONVERTID’, ‘ABS’, ‘ROUND’, ‘CEILING’, ‘FLOOR’, ‘SQRT’, ‘ACOS’, ‘ASIN’, ‘ATAN’, ‘COS’, ‘SIN’, ‘TAN’, ‘COSH’, ‘SINH’, ‘TANH’, ‘EXP’, ‘LOG’, ‘LOG10’, ‘RINT’, ‘SIGNUM’, ‘INTEGER’, ‘POW’, ‘MAX’, ‘MIN’, ‘MOD’, ‘TEXT’, ‘DATETIME’, ‘DECIMAL’, ‘BOOLEAN’, ‘DATE’, ‘DAY’, ‘MONTH’, ‘YEAR’, ‘HOURS’, ‘MINUTES’, ‘SECONDS’, ‘ADDDAYS’, ‘ADDMONTHS’, ‘ADDYEARS’, ‘ADDHOURS’, ‘ADDMINUTES’, ‘ADDSECONDS’, ‘CONTAINS’, ‘FIND’, ‘LOWER’, ‘UPPER’ , ‘MID’, ‘SUBSTITUTE’, ‘TRIM’, ‘VALUE’, ‘CONCATENATE’, TODAY, WEEKDAY, BEGINS |
Supported System Variables
The following System Variables are supported, along with all of their fields:
| $User |
| $Organization |
| $Profile |
| $Setup |
| $Record (note that tokens using $Record will look for a context input labeled ‘recordId’ and use that record) |
If you need a function or variable that’s not here, feel free to leave a comment proposing it. And if you can write Apex code, feel free to add it to the code yourself!
Supported Operators
The following Operators are supported: [‘+’, ‘-‘, ‘/’, ‘*’, ‘==’, ‘!=’, ‘>’, ‘<‘, ‘>=’, ‘<=’, ‘<>’];
You can use the EvaluateFormula action independently of the Formula Builder (and independently of Flow). It should be able to handle any formula that Salesforce understands if you use supported functions from the list above.
Expression Builder LWC
This package also includes a graphical LWC-based Expression Builder:
The expression builder also outputs a single formula string, so it too can be evaluated at runtime by the same Evaluate Formula invocable action. Unlike formulas generated by the Formula Builder, formulas generated from Expression Builder only return true or false.
Attributes
| formulaValue | The formula string that the expression builder inputs and outputs |
| contextObjectType | Standard or custom object API name, determines set of fields which user will be able to choose from ‘Insert Field’ picklist |
| supportedSystemTypes | Specify in a comma separated list the names of the system variables you want users to also be able to use in the formula. Values supported by the Evaluate Formula action at this time are User, Organization, Profile, Setup. The ‘cost’ of adding more of these to your instance might be felt in either or both of 1) the time it takes to populate the Insert Field picklist at init time and 2) the handiness of negotiating a large picklist with many values. |
| customMergeFields | Allows the specification of a specific set of mergefields. See ‘Specifying CustomMergeFields’ below |
Considerations
Does not currently support quote marks
Only the following dataTypes are currently supported:
“Evaluate Formula” Invocable Action
To make use of the formula strings generated by the Formula Builder and the Expression Builder pass them to the Evaluate Formula action in a Flow. This demo flow shows the output from a Formula Builder getting handed to the Evaluate Formula action, which will output a calculated result:
Note that in real use cases, the formula builder and the Evaluate Formula action will be in separate Flows. One will be run at configuration time, and one will run later, perhaps when triggered by an event.
This action is based around the Apex Formula Engine built by Enrico Murru.
This action takes the following inputs:
| String | formulaString (required) |
| String | recordId (optional) |
It evaluates the formula, pulling data where appropriate from the record that you pass in, and also calculating the values of any global variables in the formula string. It evaluates any of the supported functions and comes up with a final result. The result is returned in one or both of these outputs:
| String | stringResult |
| Decimal | numberResult |
If the formula has any strings that begin with the name of the object type of the passed in recordId, the action will lookup the record whose Id has been passed in and use the appropriate values. There is no support currently to pass in upstream Flow variables/mergefields.
How Formula Evaluation Works
Evaluation of formulas takes part in two steps. In the first step (Token Replacement), any tokens in the formula are replaced by the value of what they’re pointing to. Then the resulting formula string is passed to the formula engine for calculation (Value Calculation).
Token Replacement
Here are the supported tokens:
| Token Type | Replacement Process |
| References to SObject fields, like Quote.Amount | If the first word in the string matches an existing SObject type name, look for a ‘recordId’ value in the provided context data, look up that record, access the appropriate field data and substitute it into the formula |
| References to system variables like $User.username | If it’s a supported system variable, query for the appropriate field data. |
Supported formula functions like “AND” and “ABS” are not changed during this first step.
Value Calculation
In the second step, the now-tokenless formula string is passed to the core formula evaluator, which evaluates the formula and returns an appropriate value. For convenience, the action comes with a string output and a number output, and it will try to set either or both of them if there are legitimate values. Here are the three supported cases:
| A String | Example Formula “Alex” + “Flow” | Returns “AlexFlow” |
| A Number | Example Formula “32.1 + (2/1)” | Returns 34.1 and “34.1” |
| A Boolean | Example Formula “43000 > 23” | Returns “true” |
The Formula Evaluator is implemented as a set of Apex classes and can be called invocably via runtimes that support invocable actions, which currently includes Flow, Process Builder, Einstein Bots, and Einstein Next Best Action. It has support for many functions and operators (see below for the full list). The evaluator was created by Salesforce MVP Enrico Muru. Support for context data was added by two excellent freelance developers: Andrii Kraiev and Aleksander Antonyuk.
Demo Flow 1: Simple Formula Create and Evalute
Test Formula Builder and Evaluate Formula by running this flow, which is included in the package:
Note that the formula builder in the Enter Formula screen is configured to use Account as the context object type. As a result, when you run this flow, you’ll be able to select from Account fields for inclusion in the formula builder:
In this demo flow, the output of the Formula Builder is passed directly to the Evaluate Formula invocable action (this isn’t likely how you’ll really use your formula strings). Note that you need to pass a specific Account recordId into this flow if you want any Account references to be properly resolved. However, you can enter simple formulas like: 5 + 7 or LEFT(“foobar”, 3) and see them immediately evaluated.
July 2020 updates:
Added new expression builder attribute customMergeFields:
This attribute is used to hold developer-defined values, it accepts the following structure:[{
label(String): Label
value(String): Value
dataType(String, supported values: "DOUBLE,DATETIME,STRING"): determines the type of the value
renderAs(String, supported values: "number,datetime,date,text") : determines input type, which will be rendered to enter the value
}]
For example if you need to let the user to create an expression, where you would only allow choosing three attributes/values, will look like this:
[{
label: "Record Name",
value: "Name",
dataType: "STRING",
renderAs: "text"
},
{
label: "Revenue",
value: "Revenue",
dataType: "DOUBLE",
renderAs: "number",
},
{
label: "Close Date",
value: "CloseDate",
dataType: "DATETIME",
renderAs: "date"
}]
This will render the following in the field selection combo box:
And completed component with all values set may look like this:
dataType controls the data conversion that happens when expressions are converted to formulas, this is why it is important to pass in the proper type. dataType may be different from renderAs, the possible use case is when the developer does not want to render the date picker to set the date field, so if dataType = date, and valueType = text, this will render string input for setting the date field, instead of date picker.
NOTE: as all these values are added to the lightning-combobox component where it is prohibited to have two identical values, the developer must guarantee that all values are unique for the whole data set.
Installation
Formula Builder and Expression Builder flow screen components are available in the Flow Screen Components Base Pack.
The Evaluate Formula action is currently available here. Packaging work is in progress.
Old versions:
Version 1.0 Unmanaged 5/11/20