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.

1697

Compare Operators

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

Array Compare Operators

1600
OperatorExample
ININ [new, vip]
NOT_INNOT_IN [new, vip]

Math Operators

1600
SymbolName
*Multiply
+Add
-Subtract
/Divide

String Functions

1600
SymbolDescription
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

1600
SymbolDescription
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

1600
OperatorExample
betweenbetween 10 MIN AGO and 1 HOUR AGO
NOWbetween NOW and 1 HOUR AGO
andbetween NOW and 2 HOUR AGO

Date Operators

1600
OperatorDescription
SECONDseconds
MINUTEminutes
DAYdays
HOURhours
WEEKweeks
MONTHmonths
YEARyears

Duration Operators:

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

1600
OperatorExample
SECOND10 SECOND AGO
MINUTE10 MINUTE AGO
DAY10 DAY AGO
HOUR10 HOUR AGO
WEEK10 WEEK AGO
MONTH10 MONTH AGO
YEAR10 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: ''
}