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)
relation[class=subclass].field // aggregate over related objects of a given class
relation[from=class].field // aggregate over one specific relation (role)
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 | Alias for =. "a" == "a" → true |
!= | Binary | 5 | Not-equals. null != null → false. "a" != "b" → true |
> | Binary | 5 | Greater than. x > null → true |
< | Binary | 5 | Less than. x < null → true |
>= | Binary | 5 | Greater than or equal. x >= null → true, null >= x → false, null >= null → true |
<= | Binary | 5 | Less than or equal. x <= null → true, null <= x → false, null <= 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) |
== is interchangeable with =. Use != to test inequality:
status == "active" && role != "guest"
>/</>=/<= compare Number, Currency, Percentage, Date, Time, DateTime, and Boolean operands numerically, and Text operands lexicographically — the same ordering max/min/maxBy/minBy use. Mixing incompatible types (e.g. Text with Currency), or passing a List/Array/File value, raises an error rather than silently returning false. >=/<= reuse >/<'s own null rule (x vs null → true, null vs x → false), but return true rather than false when both sides are null, since equality is now in play.
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. |
sysInfo | sysInfo(key) | Returns a system value for the given key. Supported keys: appUrl — the configured application URL; tenantId — the tenant's unique identifier (UUID); tenantName — the tenant's display name (null if not set). Example: sysInfo("tenantId"). |
identifier | identifier(name) | Returns the next integer in the named auto-increment sequence. Each distinct name maintains its own counter, starting at 1. The name argument can be any expression — passing a variable creates one sequence per distinct value. Returns null if name is null. |
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. |
lpad | lpad(value, length, padChar) | Left-pads value with padChar until the result is length characters long. padChar repeats as needed. If value is already length or longer, it is returned unchanged. Returns null if value or length is null. |
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 in the tenant's timezone 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, [locale]) | Formats a date or datetime as a text string using a DateTimeFormatter pattern (e.g., 'dd-MM-yyyy'). locale is a BCP 47 language tag (e.g. 'nl') controlling locale-dependent tokens like MMMM; defaults to English when omitted. |
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. |
maxBy | maxBy(relation.compareField, relation.resultField, [includeNullResults]) | Returns resultField from the related object that maximizes compareField. Both arguments must reference the same relation. |
minBy | minBy(relation.compareField, relation.resultField, [includeNullResults]) | Returns resultField from the related object that minimizes compareField. Both arguments must reference the same relation. |
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.
Relation qualifiers: from= and class=
A relation reference inside an aggregate function can carry a bracket that narrows it down: relation[key=value, ...]. There are two keys, and they can be combined:
| Key | Meaning |
|---|---|
from=<class> | Which relation to follow: the exact class on the other side of the relation, i.e. the class the relation is declared to on the formula's own side. Matched exactly, not through inheritance. |
class=<class> | Which related objects to include: only those that carry this class. |
Both keys take a REST name (company) or a qualified name (commons.company). Entries are separated by commas: shareholdings[from=company, class=person].code.
Qualifiers are supported in count, exists, sum, avg, sumIf, countIf, avgIf, max, min, maxBy and minBy, both on the bare relation (count(shareholdings[from=company])) and on a field path (sum(shareholdings[from=company].percentage)). In the two-argument functions (sumIf, countIf, avgIf, maxBy, minBy) both arguments must reference the same relation with the same bracket. Qualifiers only work inside these functions. A plain value path outside an aggregate (relation[from=company].field) and template iteration ({{#each relation}}) do not support them.
class=: filter on a subclass
An n-side relation declared against an ancestor class returns every related object, whatever its concrete class. class= keeps only the objects that carry a given class. A commons.dossier has a contents relation declared against commons.content, an ancestor of both commons.task and commons.reminder. To aggregate only the tasks:
count(contents[class=task])
sum(contents[class=task].duration)
sumIf(contents[class=task].duration, contents[class=task].billable)
class= is a class-membership check on the related object, not an inheritance restriction on the relation: an object matches whenever it has the class, even when it received that class through an unrelated relation. count(shareholders[class=person]) counts only the related shareholders that are persons.
A class= class that no related object can carry is not an error; the aggregate evaluates to zero (count, sum) or empty (max, min, maxBy, minBy, exists).
from=: pick a relation by role
A class can be related to the same class through two different relations. In a shareholding register, entityhub.shareholding has a relation to the company whose shares are held (commons.company) and one to the shareholder (entityhub.shareholder, which inherits commons.entity). A company can play both roles: it has shares, and it holds shares in other companies. Seen from the company, both reverse relations are called shareholdings, so count(shareholdings) does not say which shareholdings are meant.
from= names the class on the company's own side of the relation — the role:
count(shareholdings[from=company]) // shareholdings in which this company's shares are held
count(shareholdings[from=shareholder]) // shareholdings in which this company is the shareholder
There is no union syntax. To cover both roles, combine two expressions:
exists(shareholdings[from=company].code) || exists(shareholdings[from=shareholder].code)
When the object does not carry the from class — a company that is not a shareholder — the aggregate evaluates over zero related objects, just like an unmatched class=.
Ambiguous references are rejected when the model loads
When a relation reference without from= could match more than one relation for any object of the formula's BASIC class, including roles the object may only receive later, the model fails to load with an error naming the formula, the basic class and the possible from values:
Ambiguous relation reference in formula, qualify it with from=<class> using one of [company, shareholder]
Add from= to the reference to resolve it. A from= class that matches no relation anywhere in the model is also a load error.
Definition format 0 called the class filter type=. Files in format 0 are converted on load; in a current file, type= is refused with a message naming class=.
The bare bracket form is no longer supported
The earlier form relation[subtype], without a key, is rejected when the model loads. Replace contents[task] with contents[class=task].
max/min argument types: both the relation-aggregate form (max(relation.field)) and the scalar form (max(a, b, ...)) support any combination of Number, Currency, Percentage, Date, Time, DateTime, and Boolean values — these are compared numerically (currency/percentage by amount, date/time/datetime by point in time, boolean as 0/1). Text arguments are compared lexicographically, but all arguments in a single call must be Text — mixing Text with a numeric-family type, or passing a List/Array/File value, raises an error rather than silently returning a default value. On a tie (equal numeric value — e.g. two equal Currency amounts in different currencies), the first argument is returned, matching the convention used by +, -, and * of always keeping the first operand's currency when combining two Currency values.
maxBy/minBy argument types and tie-breaking: compareField is compared using the same rules as max/min above (same numeric-family/Text grouping, same error on an incompatible or List/Array/File value). A related object whose compareField is null is excluded from consideration entirely — it can never be selected as the winner. Unlike max/min's "first argument wins" tie rule, a maxBy/minBy tie (equal compareField across several related objects) is broken by the lowest id, so the result is deterministic regardless of relation ordering. Example: maxBy(memos.timestamp, memos.text) returns the text of the most recently timestamped memo.
maxBy/minBy null results: by default, a related object whose resultField is null is also excluded from consideration, same as a null compareField — the winner is the extreme object that actually has a result value. Passing true as the third argument (maxBy(memos.timestamp, memos.text, true)) includes such objects: a null compareField still always excludes an object (it's the ordering key), but an object with the extreme compareField and a null resultField can now win and return null. Omitting the third argument, or passing false, keeps the original two-argument behavior.
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. |
For Date values, all boundaries — day start, period start, period end — are determined in the tenant's configured timezone. A date that crosses midnight in Europe/Amsterdam triggers recalculation at that local midnight, not at UTC midnight. DateTime values already carry timezone information and are not affected.
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.
{ "field": "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 (locale-dependent tokens like MMMM default to English):
formatDate(startDate, "dd MMMM yyyy")
Format a date with Dutch month names:
formatDate(startDate, "dd MMMM yyyy", "nl")
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")
Text of the most recently timestamped memo:
maxBy(memos.timestamp, memos.text)
Zero-padded invoice number:
lpad(toText(invoiceNumber), 4, "0")
Per-year invoice number sequence (resets to 1 each year, e.g. 2025-0001):
concat(
formatDate(invoiceDate, "yyyy"),
"-",
lpad(identifier(concat("INVOICE", formatDate(invoiceDate, "yyyy"))), 4, "0")
)
The sequence name concat("INVOICE", year) namespaces the counter to invoices, so it stays independent from other identifier sequences in the same tenant. Each distinct year gets its own counter starting at 1.