Formulas

A formula field calculates its value from the row it is on, and from the rows that row's relations point at. Formula cells are read-only: the value is derived, and the result is stored on the row, so you can sort by it, filter on it, chart and group it on a dashboard, and read it over the API exactly like a column you typed.

Nothing you write runs as JavaScript, and no part of it reaches the database as SQL.

The short version#

1. Use a normal field reference such as {Amount} to read this row.
2. Use a relation such as {Transactions} to reach a list of related rows.
3. Use map, filter, some, every, or findItem with current to inspect each related row.
4. Reduce that list with functions such as sum, average, count, min, or max.
5. Check the three-row preview before saving. A blank, an error, and a real zero are shown differently.

For common “show, count, or summarise related rows” questions, start with a lookup, rollup, or count. Choose Open as formula only when you need logic beyond the guided controls.

Writing one#

Add a field, choose Formula, and the editor opens with a searchable palette on two tabs: Fields — this table's fields, inserted as a reference by clicking one — and Functions, grouped by category, each showing its arguments and a one-line description.

The editor checks as you type — syntax, types, unknown references, cycles, depth and complexity — and evaluates your draft against three real rows so you can see actual values, and tell a blank apart from an error, before you save.

{Amount} * (1 + {Tax rate})if({Status} = "Paid", 0, dateBetween({Due}, today(), "days"))concat({First name}, " ", {Last name})

A field is referenced by identity, not by its name. Renaming Amount to Gross updates the label the editor shows you and changes nothing about the formula.

Reading related rows#

A relation token evaluates to the list of rows it links to, and current refers to one of those rows inside map, filter, some, every and findItem.

sum(map({Invoices}, current.{Amount}))count(filter({Tasks}, not current.{Done}))join(map({Vendors}, primary(current)), ", ")

That is the same machinery lookups, rollups and counts are built on — they are formulas the configurator writes for you.

Operators#

Functions#

Control#

if, ifs, switch, empty, coalesce, isError, ifError

Text#

concat, toText, lower, upper, trim, length, contains, find, left, right, substring, replace, replaceAll, repeat, split, regexMatch, regexExtract, regexReplace, format

Number#

toNumber, abs, round, roundUp, roundDown, floor, ceil, mod, pow, sqrt, exp, ln, log10, sign

Date#

today, dateAdd, dateSubtract, dateBetween, formatDate, parseDate, startOf, endOf

dateAdd, dateSubtract, dateBetween, startOf and endOf take a unit: milliseconds, seconds, minutes, hours, days, weeks, months, quarters, years or business_days. Omit it and you get days.

List#

Shape: map, filter, some, every, findItem, first, last, at, slice, reverse, sort, unique, compact, flat, includes, join

Counts: count, countValues, countUnique, countEmpty, percentEmpty, percentNotEmpty

Numbers: sum, average, median, mode, min, max, range, stddev, stddevPop, variance, variancePop

Dates: earliest, latest, dateRange

Checkboxes: all, any, countChecked, countUnchecked, percentChecked, percentUnchecked

Options: countPerOption, percentPerOption

Related rows#

primary (the name of a related row), rowId (its identifier)

Empty lists answer rather than error: the numeric reductions are blank, the count family is 0, all is true and any is false.

Dates: today() but not now()#

today() is the only function whose answer changes on its own. It returns today's date in the workspace timezone, and rows using it are recalculated once a day at midnight. So dateBetween({Due}, today(), "days") is right every morning without anyone touching the table.

There is no now(). A to-the-second clock would make every row containing it permanently out of date, which would mean either recalculating your whole table continuously or showing you a number that is quietly wrong. Neither is worth it, so the function does not exist rather than existing and misleading you.

Errors#

An error is printed in the cell, using spreadsheet conventions, and is reported as a stable code over the API.

Use isError and ifError to handle one deliberately.

#REF! is recoverable: restore the trashed field or table and the value comes back. Deleting a field that a formula reads is refused until you have seen how many computed fields read it and confirmed.

While it recalculates#

A cell that owes a recalculation reads Recalculating. It never renders as a blank, because a blank is a real answer that some formulas legitimately give, and you would have no way to tell the two apart.

Editing a formula, or changing a field it depends on, marks the affected rows and works through them. Small tables finish before the save returns; large ones fill in as the work drains.

Caps#

Every one is a refusal with a reason, never a partial answer.

Regular expressions run on a restricted syntax with a step budget: no backreferences, no lookaround, and a pattern that would take too long to match is refused rather than allowed to run.

Cycles — two fields that would each need the other's answer — are rejected when you save, not discovered later.

Where to go next#

• Lookups, rollups and counts — the same engine, configured rather than typed.
• Field types in a table — what each kind gives a formula to work with.