Expressions

Expression syntax

Everything an expression can contain, and how it is worked out.

#Fields

Write a field's name in square brackets: [Price], [Due date]. Names are not case-sensitive ([price] works too). Inside an expression, the field means this record's value of it.

#Through a link

Follow a link field with a dot and another field in brackets:

[Customer].[Email]
[Order].[Customer].[Name]

When the link allows several records, the result is a list (one value per linked record): SUM([Items].[Price]).

#This row, inside FILTER and SELECT

Inside the condition of FILTER and SELECT, fields mean the other table's record being checked. [_THISROW] is the record the expression belongs to:

FILTER("Jobs", [Customer] = [_THISROW])

means "jobs whose Customer is this record". [_THISROW].[Region] reads a field of this record from inside the condition.

#A table's column

Table[Field] is every value of a field in a table, for SELECT: SELECT(Orders[Total], [Status] = "Paid"). This short form works for table names without spaces; for others, use FILTER("Table name", …) and follow the links.

#Values

Function names are not case-sensitive either: today() is TODAY().

#Operators

From the one that binds tightest to the loosest:

Use brackets to change the order: ([Price] - [Discount]) * [Qty]. One expression has at most one comparison at its top level: write AND([A] > 1, [A] < 5), not 1 < [A] < 5.

#AND, OR and NOT are functions

AND([Paid], [Shipped])
OR([Status] = "Open", [Urgent])
NOT([Done])

Writing [Paid] AND [Shipped] is an error; the helper suggests the function form.

#Types

Every expression has a type, checked when you save it (not later when it runs), so mistakes show up while you build:

Places that need yes/no (validation, filters, conditions) say so if an expression gives something else. A formula field's result type is set on the field.

#Blanks

A field with no value is blank. Use ISBLANK([Email]) and ISNOTBLANK([Email]) to test for it, and COALESCE([Nickname], [Name]) to fall back to another value.

• In maths, blank counts as 0: [Price] * [Qty] is 0 when either is blank.
• Joined with &, blank is empty text.
• In comparisons, blank equals blank and is less than any value, so [Due] < TODAY() is yes when Due is blank. Add ISNOTBLANK([Due]) when that matters: AND(ISNOTBLANK([Due]), [Due] < TODAY()).
• An empty list is blank too.

#Comparing values

• Text compares without case: "open" = "Open" is yes. Use CONTAINS, STARTSWITH and ENDSWITH for parts of text.
• When either side is a number, both are compared as numbers (text like "12" counts).
• Dates compare by time: earlier is smaller.
• A select field compares by its option's label: [Status] = "Done".
• A person field holds an email: [Owner] = USEREMAIL().

#Dates and times

• [Start] + 7 is a week later (numbers added to dates are days); [Start] - 1 is the day before.
• [End] - [Start] is the number of days between them (with a fraction for times).
• DATEADD([Start], 3, "month") adds months, years, weeks, hours or minutes.
• DATEDIFF([Start], [End], "week") counts whole units between two dates.
• TODAY() and NOW() are in the person's timezone (set in their profile). Scheduled automations use the timezone of their schedule.

#Templates in automations

Text settings of automation steps (email subject and body, messages, URLs, headers, prompts) are templates: plain text with expressions in double curly braces.

New order {{[Order number]}} from {{[Customer].[Name]}}: {{TEXT([Total], "0.00")}}

Each {{ … }} is worked out on the trigger record and put into the text. Automations started by a webhook or an API call read what they were sent with INPUT("path"): {{INPUT("customer.email")}}.

#Limits

An expression can be up to 4,000 characters long and nest up to 100 levels deep. One expression may do up to a million steps of work (for example FILTER over a big table inside another FILTER); beyond that it stops with an error rather than slowing the app down. Dividing by zero is an error, not a blank.

#Errors you may see