Skip to main content

Formulas

Formula is an expression consisting of literals, variables, operators and functions, and when evaluated results in some (null or non-null) value of various types (string, number, date etc.)

Formulas allows users to represent the complex business rules in an expressive way and apply those to modify input records resulting in updated records.

Formula engine is a runtime service which parses and evaluates Formulas. Formula engine is built on top of Spring Expression. This documentation covers the specifics of formula usage in Realfire and custom functions, and it is assumed that you have read through Spring documentation to get initial overview.

Formulas can be used various places in Realfire as listed below.

  • Dataloader Field Mapping
  • Dataloader Sql Update

Formula Constructs

As stated earlier, formulas consists of literals, variables, operators and functions, This sections walks through each of these constructs with some examples.

Literals​

Literal is any fixed value of a particular type whose value is known at the time of writing a formula and doesn't change at runtime.

Formula engine supports numbers (integer, real and hex), boolean and null literals. Date and datetime literals can be specified using functions.

Numbers​

Numbers are either positive or nevative integral or real numbers. There are three different type of numbers integer, long and double.

Integer is integral value in the range -2,147,483,648 and 2,147,483,647. They are represented as numbers.

For ex., 6, 1000, or 178634

Long is integral value in the range -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807. They are represented as numbers with suffix l.

For ex., 1000l or 3487l

Double is real value in the range -1.23456e-300d and 1.23456e300d;

For ex., 100.76, 3736.78, or 100.0

Strings​

String literals any value enclosed between two single quotes. Contents of strings can be any valid char and formula engine treats it as strings even if it consists only digits.

For ex., 'John Doe', '2018-01-01', 'Property_Info__c', or '1987.65'

Boolean​

Boolean literals are true and false (case insensitive).

Nulls​

Null literal is represent with keywod null. You can use this to pass a value to a function or check if a value is null.

Operators​

Formulas supports following types of operators.

Relational​

  • < (less than)
  • > (greater than)
  • <= (less than or equals to)
  • >= (greater than or equals to)
  • == (equals to)
  • != (not equals than)
  • % (mod)
  • ! (not)

Logical​

  • &&
  • ||
  • !

Mathematical​

  • +
  • -
  • *
  • /

Addition operator can be used for numberss and strings but all other operators can be used only on numbers.

Examples

10 > 5 // true
10 < 5 // false
10 >= 5 // true
10 <= 5 // false
10 > 5 || 5 > 6 // true
true and false // false
!true //false
1 + 1 // 2
'executorTest' + ' ' + 'string' // executorTest string
1 - -3 // 4
-2 * -3 // 6

Variables​

Variables are placeholders of particular type and holds a value at runtime. Variables are typically the field names that you want to refer to use those values in some expressions.

Variables are referenced using their names without any quotes.

For ex.,

Proposal_Name__c + '-' + Propossal_Id__c

Variable names are case insensitive. Below two expressions results in same result.

PROPOSAL_NAME__C + '-' + PROPOSSAL_ID__C
proposal_name__c + '-' + propossal_id__c

Formula Functions​

Functions are piece of reusable logic identified a name. Functions takes zero or more arguments and returns a value. Return value from a function can be passed as input to other functions hence chaining them to achieve the result you are looking to get.

For ex., min(2, 3) or upper_case('executorTest')

Realfire provides many functions and are documented here

Things to watch out for

Formula and null values​

When you define formulas, you need to understand the impact of null values (null means absense of an value). Since null means no value, you cannot usually act on it.

For example formula Revenue__c + 2 would result in an error, if Proposal_Id__c is null.

Many functions are designed to handle the null gracefully. If you want to make sure some default value is used insetad of null, you can use global function first_not_null(value1, value2) to ensure nulls are converted to a value before acting upon.

Formula Playground

Realfire version 4.0 added "Formula Playground" utility. It allows you to enter various formulas, enter input values and visualize what output formulas produce.

It should greatly help you famialiarize with formulas and build confidence in using them.

It can be be accessed from Top Menu -> Tools -> Formula Playground, as shown below.

Examples

upper_case('Boing') // BOING

add(10, 20) // 30

//Assuming value is Boing International
first(split(value, ' ')) => Boing