Formula Engine

Realtask/Brobench Formula engine is an advanced capability that supports executing user provided expressions to implement custom business logic.

This capability is integrated in various places in Datasert Products. For ex., It can be used to update Records in SOQL Datagrid in Brobench, to map fields in Dataloader in Brobench/Realtask and in other places.

Expression is a string written in a syntax based on a subset of JavaScript ES6. The Expression can make use of literals, operators, functions and business data to implement a piece of business logic, for ex., add 5% discount to existing value.

This page documents the overall syntax and usage of Formulas. Formula Functions page has the list of supported functions that you can use in a Formula and use Formula Playground to try out formulas and see the results.

caution

Note that this documentation refers to the Formula engine used in Datasert online products like Brobench/Realtask/Realsync/Metasync etc., The formula engine used in Realfire product is different and documented here

Concepts

Here are some core concepts that lay out the fundamentals of Formulas. See later sections for more information.

• Expression

  Formula expression is a string that you enter. It must be syntactically correct adhering the expression rules otherwise you will get an error either during the parsing or evaluation phase. Expressions are built using literals, operators, functions, and variables.

• Data Types

  Every variable that you refer in a formula is of a particular type. Most of the functions are designed to auto-convert the value to the appropriate type, but sometimes the value must be in the correct data type for it to work with particular operators or functions. So check your variable data type if you are not getting the output you are expecting.

• Literals

  These are constant values that you specify in expressions which don't change from execution to execution. For ex., string literals or boolean literals, etc.,

• Operators

  These are list of symbols that have special meaning inside the formulas. For ex., + means add two numbers or concatenate two strings etc., We support a subset of JavaScript operators.

• Variables

  Variables are runtime data that you feed into the formula and refer using evaluation of an expression. The list of available variables in an expression depends on the context where the formula is being executed.

• Temp Variables

  Temp Variables are temporary variables that you can create within an expression to simplify a complex logic and avoid repeating the same calculation.

• Functions

  Functions pre-defined logic with a name. A function takes zero or more parameters and returns a value.

Data Types

Formula Engine supports the following data types.

• boolean - represents a boolean value
• number - represents a number value. for ex., 10, 10.5, 0.5
• string - represents a string constant. For ex., 'Accounting', 'Department'
• array - represents an array with list of values. For ex., [1, 2] or ['PST', 'EST']
• object - represents an object with list of values. For ex., {name: 'John', age: 25}

Literals

Literals are constant values hardcoded into the formula. Literals can be created in each of the above defined data types.

• null or undefined - represents a no value or empty value
• true or false - represents a boolean value.
• number - represents a number value. for ex., 10, 10.5, 0.5
• string - represents a string constant. For ex., 'Accounting', 'Department'
• [<values>] - represents an array with list of values. For ex., [1, 2] or ['PST', 'EST']
• {key:value} - represents an object with key/value pairs. For ex., {name: 'John', age: 25}

Operators

Operators are various symbols that have special meanings. There is the list of operators supported in the formula.

Symbol	Description
+	Addition Operator adds given numbers or concatenates strings
-	Used to subtract numbers. When used with number, they change numbers from positive or negative and visa versa
*	Used to multiply numbers.
/	Used to divide numbers.
=	Assignment Operator is used to assign a value to a variable
>, >=, <, <=, ===, !==	Comparison Operators compares left and right operands and returns true or false
%	Remainder Operator returns remainder left over
**	Exponentiation Operator returns the result of raising the first operand to the power of the second operand.
,	Comma Operator is used to assign a value to a variable
&&	Logical And Operator evaluates set of boolean operands and returns true if and only if all the operands are true. Otherwise it will be false
||	Logical Or Operator evaluates set of boolean operands and returns true if any of the operands are true. Otherwise it will be false
!	Unary Negation Operator is negate the operand which it preceds.
? and :	Conditional (ternary) Operator is used to simulate if/else conditions.
( and )	Grouping Operator controls the precedence of evaluation in expressions.
[ and ]	Used to create arrays.
{ and }	Used to objects

Variables

Variables are runtime data elements made available to a formula. Variables can be standard variable (which always starts with $) or custom variables, which are fields of a record.

You cannot create your own variables inside the expressions except for Temp Variables

Here are valid variable names.

• firstName
• last_name
• phone1

Standard Variables

Formula engine sometimes provides special variables that give access to contextual information. These special variables start with $ character. The list of available standard variables available in a formula varies from product to product and the context where the formula is used. Consult the documentation specific to each product/context to know the list of standard variables.

Here are some of the special variables that you can refer in formulas and where they are available.

Name	Where Available	Description
$record	Realtask/Brobench during field mapping	Indicates the current record which being field mapped
$value	Realtask/Brobench during field mapping	Indicates the current value of the field being mapped

Variables with Space

Sometimes the fields you load data from (for ex., csv file) may contain spaces or other special chars that are invalid variable name chars. In such cases, you can refer to the field using qualified accessor of $record variable.

For ex., $record['first name'] + ' ' + $record['last name']

Temp Variables

Sometimes the same logic needs to be referred in multiple places. Since you cannot create your own variables in Appsuite formulas, you will end up duplicating the sub-expression in multiple places. This can slow things down and more importantly source of bugs as you might forget to change in all places.

In such cases, you can define temporary variables. Temp Variables starts with $temp.. For ex., $temp.discount or $temp.leftOverAmount. You can define any number of temp variables and use them in the expression.

caution

Temp variables are only valid for the duration of a single expression evaluation. Once the expression is evaluated, the value is discarded.

Usage

$temp.discount = 0.05;
$temp.discountedAmount = totalPrice * $temp.discount;

totalPrice - $temp.discountedAmount

Functions

Formula functions provides predefined functionality to speed up your business logic implementation. The list of functions is documented here.

Examples

Formula	Description	Playground
1 + 5 * 5		Link
amount * 0.15	Calculate 15% discount over amount	Link
first(Zip_Code__c, 5)	Returns first 5 digits of zip code	Link
'PST'	Returns hardcoded timezone as PST	Link
map_value(Timezone_Code__c, $params.tzCodeToIdMap, 'America/Los_Angeles')	Converts timezone code to long form timezone	Link
first_not_null(MailingStreet, BillingStreet)	Returns first not-blank value among MailingStreet and/or BillingStreet	Link
ensure_suffix(Sales_Rep_Email__c, '.invalid')	Ensures that sales rep email is suffixed with .invalid	Link
(Opportunity.Type === 'Add-on' || Opportunity.Type === 'Third Party Add-on') ? true : false	Checks if OpportunityType is either 'Add-OnorThird Party Add-On`	Link
Account__r.BillingStreet + ' - ' + Account__r.BillingPostalCode	Concatenates Account billing street and postal code	Link
format_date(Site_Survey_Complete__c, 'yyyy MMM dd', 'America/Los_Angeles')	Formats date	Link