Formulas

Overview

Appsuite Formula is an expression written in syntax based on subset of Javascript/ES6 version, when evaluated returns a value. Formula makes use of symbols, functions and variables to implement a piece of business logic.

This page documents the overall syntax and usage of Formulas. Functions page has list of supported functions that you can use in Formulas.

caution

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

Formula Playground

Formulas are advanced features of Appsuite and can be source of bugs due to inherent nature of complexity. To ensure users can try out and see what values formulas produce, we have built Formula Playground where you can enter any formula expression and see its result immediately. You can also simulate referring to variables (including standard variables) using custom input.

Try out some examples as you read through to better understand the concepts.

Concepts

Here are some concepts which lays out the fundamentals of Formulas. See subsequent sections for more information.

• Expression

  Formula expression is a string that you enter. It must be syntactically correct otherwise, it will be errored out either during the parsing or evaluation phase. Expressions are built using operators, functions, and variables.

• Data Type

  Every variable that you refer in a formula is of a particular type. Sometimes the value must be in correct data type for it to work with particular operators or functions.

• Symbols

  These are list of syntactical symbols that have special meaning inside the formulas. For ex., + means add two numbers or concatenate two strings.

• Literals

  These are constant values that you specify in expressions which doesn't change from execution to execution.

• Variable

  Variables are runtime data that you can refer. List of available variables in an expression depends on the context where 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. Function takes zero or more parameters and returns a value.

Literals

Literals are constant values hardcoded into the formula. There are 5 literal types.

• 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 a array with list of values. For ex., [1, 2] or ['PST', 'EST']
• null or undefined - represents a no value or empty value

Symbols

Operators are various symbols that has special meanings. There are some symbols

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 variables (which always starts with $) or values of a record with names same as what is defined by you, which doesn't start with $.

You cannot create your own variables (except 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 starts with $ character. List of available standard variables varies from product to product and where formula is used. Consult the documentation specific to each product to know list of standard variables.

Here are special variables that you can refer in formulas.

• $rec - refers to the current record being processed
• $job - refers to the current job where formula is being evaluated

Variables with Space

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

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

Temp Variables

Sometimes same calculation 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.

caution

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

Functions

Formula functions provides predefined functionality to speed up your business logic implementation. List of functions are 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