Understanding the Text Formula Builder (Advanced Formula)

The dynamic model builder uses a free text entry to apply filters and formulaic logic. The main reason for this is flexibility. With a text input we can apply mathematical operators to the Variables created.

Compare Operators

Symbol	Name
`!=`	Not equal
`==`	Equal
`>=`	Greater than or equal to
`<=`	Less than or equal to
`<`	Less than
`>`	Greater than
`LIKE`	SQL-style LIKE Operates on strings. Wildcard syntax is standard SQL % (e.g. entity.name LIKE "FirstName %")

Array Compare Operators

Operator	Example
IN	IN [new, vip]
NOT_IN	NOT_IN [new, vip]

Math Operators

Symbol	Name
`*`	Multiply
`+`	Add
`-`	Subtract
`/`	Divide

String Functions

Symbol	Description
UPPERCASE(string)	Convert string to uppercase
LOWERCASE(string)	Convert string to lowercase
CAPITALIZE(string)	Convert word-initial letters to caps
TRIM(string)	Trim leading and trailing whitespace

Numeric Functions

Symbol	Description
ABS(x)	Absolute value of x
SQRT(x)	Square root of x
EXP(base, n)	raise to the power of
MODULO(a, b)	a mod b
LN(x)	Natural logarithm of x
LOG(x)	Base-10 log of x

Time Compare Operators

Operator	Example
between	between 10 MIN AGO and 1 HOUR AGO
NOW	between NOW and 1 HOUR AGO
and	between NOW and 2 HOUR AGO

Date Operators

Operator	Description
SECOND	seconds
MINUTE	minutes
DAY	days
HOUR	hours
WEEK	weeks
MONTH	months
YEAR	years

Duration Operators:

Includes everything in Date Operators with the Keyword “AGO” appended at the end. e.g. YEAR AGO (edited).

Operator	Example
SECOND	10 SECOND AGO
MINUTE	10 MINUTE AGO
DAY	10 DAY AGO
HOUR	10 HOUR AGO
WEEK	10 WEEK AGO
MONTH	10 MONTH AGO
YEAR	10 YEAR AGO

NULL Checking:

You can check to see if the value of an object is NULL such as custom_data_field_2 IS NULL:

{
   custom_data_field_1: 'random text',
   custom_data_field_2: NULL
}

NULL can also check for the existence of an item (must be number of string), where custom_data_field_2 IS NULL would work for:

{
   custom_data_field_1: NULL
   // NO custom_data_field_2
}

The DMB statement custom_data_field_2 == "" would work for:

{
   custom_data_field_1: 'random text',
   custom_data_field_2: ''
}