# 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 |

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. |

## “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.

## Installation

Version 1.0 Unmanaged 5/11/20

This is amazing and is getting the creative idea drive churning.

Also, I’m jealous that my end users will have a better formula/expression builder than me 😉

[…] Check it out here. […]

I’m getting an install error:

package.xml: Cannot add component of type:ApexClass named:EvaluateFormula subjectId:01p4D00000019UR to another package because it is an installed component.