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] |
| CONTAINS | [new, vip] CONTAINS |
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: ''
}
Updated 9 months ago