Formula Syntax Reference
This page documents the complete syntax for formula expressions used in Formula definitions. The engine uses the Shunting Yard algorithm to evaluate infix expressions. All function names are case-insensitive.
Syntax
Literals
| Type | Syntax | Example |
|---|---|---|
| Number | Integer or decimal | 123, 45.67 |
| Text | Double or single quotes | "hello", 'world' |
| Text (with quotes) | Double the quote character to escape | "say ""hi""", 'it''s fine' |
| Boolean | Keywords | true, false |
| Null | Keyword | null |
Variables
fieldName // field on the current object
relation.field // field on a related object (dot notation)
Function call
functionName(arg1, arg2, ...)
Examples
round(amount, 2)
if(status = "ACTIVE", amount * 1.21, amount)
sumIf(lines.amount, lines.type, "EXPENSE")
dateAdd(today(), 30, "day")
concat(firstName, " ", lastName)
Operators
Operators are evaluated in precedence order (higher number = higher precedence).
| Symbol | Type | Precedence | Description |
|---|---|---|---|
|| | Binary | 2 | Logical OR |
&& | Binary | 3 | Logical AND |
= | Binary | 5 | Equality. null = null → true |
> | Binary | 5 | Greater than. x > null → true |
< | Binary | 5 | Less than. x < null → true |
+ | Binary | 10 | Addition. Null-safe: null + x = x |
- | Binary | 10 | Subtraction. Between two dates: difference in days. Null-safe: null - x = 0 - x |
* | Binary | 20 | Multiplication. Also: currency * number |
/ | Binary | 20 | Division (DECIMAL128 precision) |
! | Unary | 25 | Logical NOT |
^ | Binary | 30 | Exponentiation (right-associative) |
- (unary) | Unary | 30 | Negation (right-associative) |
Functions
General
| Function | Signature | Description |
|---|---|---|
if | if(condition, thenValue) or if(condition, thenValue, elseValue) | Returns thenValue if condition is true, elseValue otherwise. Returns null if no elseValue and condition is false. |
ifNull | ifNull(value, fallback) | Returns fallback if value is null, otherwise returns value. |
switch | switch(expr, case1, value1, ...) | Returns the value for the first case that matches expr. Optional trailing default value. |
String
| Function | Signature | Description |
|---|---|---|
concat | concat(a, b, ...) | Null-safe concatenation of one or more values into a string. |
contains | contains(text, search) | True if text contains the search string. |
len | len(text) | Number of characters in the string. |
lower | lower(text) | Converts text to lowercase. |
proper | proper(text) | Capitalizes the first letter of each word. |
substitute | substitute(text, old, new) | Replaces all occurrences of old with new. |
substring | substring(text, start) or substring(text, start, end) | Extracts characters from start to end (exclusive). Omitting end or passing -1 returns to the end of the string. |
trim | trim(text) | Removes leading and trailing whitespace. |
upper | upper(text) | Converts text to uppercase. |
Numbers
| Function | Signature | Description |
|---|---|---|
abs | abs(number) | Absolute value. Also works on currency values. |
round | round(value, decimals) | Rounds to decimals decimal places (HALF_UP). |
roundSig | roundSig(value, digits) | Rounds to digits significant figures. |
sin | sin(radians) | Sine of an angle in radians. |
cos | cos(radians) | Cosine of an angle in radians. |
Date and time
| Function | Signature | Description |
|---|---|---|
today | today() | Current date (UTC) at the moment of evaluation. Snapshot — does not trigger recalculation. |
now | now() | Current datetime (UTC) at the moment of evaluation. Snapshot — does not trigger recalculation. |
dateAdd | dateAdd(date, amount, unit) | Adds an amount of time to a date or datetime. Units: year, quarter, month, week, day. |
formatDate | formatDate(date, pattern) | Formats a date or datetime as a text string using a DateTimeFormatter pattern (e.g., 'dd-MM-yyyy'). |
inPast / isPast | inPast(date) | True if the date is in the past. Schedules recalculation at the boundary. |
inFuture / isFuture | inFuture(date) | True if the date is in the future. Schedules recalculation at the boundary. |
inPeriod | inPeriod(date, offset, unit) | True if date falls in the period at offset relative to today. Offset: 0 = current, -1 = previous, 1 = next. Units: week, month, quarter, year. Schedules recalculation at period boundaries. |
Logical
These are function equivalents of the logical operators, and accept a variable number of arguments.
| Function | Description |
|---|---|
and(a, b, ...) | True if all arguments are true. |
or(a, b, ...) | True if any argument is true. |
not(a) | Negates a boolean value. |
Aggregate
Aggregate functions operate on related objects. Use dot notation to reference a field on the relation: relation.field.
| Function | Signature | Description |
|---|---|---|
avg | avg(relation.field) | Average of numeric values across all related objects. Also supports currency. |
avgIf | avgIf(rel.field, rel.testField) | Average where testField is truthy. |
avgIf | avgIf(rel.field, rel.testField, value) | Average where testField equals value. |
count | count(relation) | Count of related objects. |
countIf | countIf(relation.field) | Count where field is truthy (non-null, non-zero). |
countIf | countIf(relation.field, value) | Count where field equals value. |
sum | sum(relation.field) | Sum of numeric values across all related objects. |
sumIf | sumIf(rel.field, rel.testField) | Sum where testField is truthy. |
sumIf | sumIf(rel.field, rel.testField, value) | Sum where testField equals value. |
max | max(relation.field) or max(a, b, ...) | Maximum value, either across a relation or from a list of arguments. |
min | min(relation.field) or min(a, b, ...) | Minimum value, either across a relation or from a list of arguments. |
exists | exists(relation) | True if at least one related object exists. |
The value argument in countIf/sumIf/avgIf can be a string literal ("EXPENSE"), a number, or a variable reference.
Conversion
| Function | Signature | Description |
|---|---|---|
toDate | toDate(value) | Converts a text or datetime value to a date. |
toDateTime | toDateTime(value) | Converts a text or date value to a datetime. |
toTime | toTime(value) | Converts a text or datetime value to a time. |
toText | toText(value) | Converts any value to its text representation. |
Files
| Function | Signature | Description |
|---|---|---|
documentExtract | documentExtract(file, type) | Extracts content from a PDF or image file. Types: TEXT (raw text), LAYOUT (structured layout), INVOICE (parsed invoice data). |
Time-sensitive recalculation
inPast, inFuture, and inPeriod return a boolean that depends on when they are evaluated. When the result will change at a known future moment, the engine automatically schedules recalculation at that point — without any manual trigger or polling.
| Function | When it schedules recalculation |
|---|---|
inPast(date) | When currently false: schedules at the start of the day after date (i.e. when the date moves into the past). |
inFuture(date) | When currently true: schedules at date itself (i.e. when it stops being future). |
inPeriod(date, offset, unit) | Always schedules at both the start and end of the relevant period. |
Example: a field isExpired defined as inPast(expiryDate) returns false today. The engine records the recalculation time and automatically re-evaluates the formula on the day after expiryDate. No cron job or manual update needed.
today() and now() are snapshots
today() and now() behave differently: they return the current date or datetime at the moment of evaluation, but they do not schedule recalculation. They are intended for snapshot use — capturing the current moment when a record is created or updated.
{ "valueType": "createdOn", "formula": "today()", "onlyWhenMissing": true }
To build a formula that stays up to date over time, use inPast / inFuture / inPeriod instead.
Examples
Full name from first and last name:
concat(firstName, " ", lastName)
Add one year to a contract start date:
dateAdd(startDate, 1, "year")
Return a default value if a field is empty:
ifNull(jobTitle, "Unknown")
Senior/Junior label based on salary:
if(salary > 50000, "Senior", "Junior")
Format a date for display:
formatDate(startDate, "dd MMMM yyyy")
Total of invoice lines:
sum(lines.amount)
Count lines of a specific type:
countIf(lines.type, "EXPENSE")
Strip spaces from an IBAN:
substitute(iban, " ", "")
True if a deadline is in the current month (auto-recalculates at month boundaries):
inPeriod(deadline, 0, "month")