# Flexlists formulas — guide for AI agents

A Formula field computes a value from the other fields of the SAME row. The language is
Airtable-compatible: write Airtable function names (Excel / Baserow aliases also work, but prefer
the names below). No code runs: the formula is parsed and checked before it is saved.

Human docs: https://flexlists.com/docs/formulas · Function catalogue below is generated from the engine's registry.

## Writing a formula

- Fields: `{Field name}` exactly as the field is named (case-insensitive). Never invent a field.
- Text: `"double"` or `'single'` quotes; `\"` inside. Numbers: `1.5`. Booleans: `TRUE()`, `FALSE()`.
- Operators: `+ - * / %`, `&` joins text, `= != <> < <= > >=`, `&&` (AND), `||` (OR), `!` (NOT).
- Arrays: `[1, 2, {Price}]`; multi-select Choice, multi-user User and file fields are arrays of text.
- Lambdas: in FILTER, MAP, SORT, ANY, ALL and REDUCE the second argument runs per item with
  `item` (and `index`, 1-based; `acc` in REDUCE) bound: `FILTER({Tags}, item != "internal")`.
- Blank: a field with no value is blank; `IF({Date}, …)` tests for a value. `""` is blank too.
- Dates: Date fields are dates, DateTime fields dates and times. Text is NOT a date: use
  `DATETIME_PARSE({Text field})` first. `TODAY()` / `NOW()` make the formula update daily / every 15 min.
- Linked rows: a Link field ("Link to another list") is an array of its linked rows, each reading as
  its label. Inside FILTER / MAP / SORT / REDUCE / ANY / ALL over it, `item.Field` reads a field of
  the linked row (`item.{Unit price}` for names with spaces): `COUNTA(FILTER({Tasks}, item.Status = "Open"))`,
  `SUM(MAP({Lines}, item.Qty * item.Price))`, `SORT({Tasks}, item.Due)`. Only fields of the linked
  list; the value follows every change of the linked rows and their links.
- Pasted from Excel: a leading `=` is fine and Excel names (AVG, CONCAT, IFS, SUMIF, ISERR, …) work;
  cell references (A1, B2:B10) do not: use `{Field}`.

## Result types

A formula's result is typed and stored in a column of that type: `number`, `text`, `boolean`,
`date`, `datetime`, `array`. Number results sort, filter and total as numbers; date results drive
calendar, timeline and Gantt views; arrays show as chips. The type is worked out by the checker:
e.g. `IF({Done}, 1, "")` is a number.

## Errors

`#VALUE!` wrong kind of value (text where a date is needed), `#DIV/0!`, `#NUM!` (SQRT(-1)),
`#NAME?` unknown function, `#REF!` unknown field, `#CYCLE!` the formula uses itself, `#ERROR!`
anything else. Wrap a risky part in `IFERROR(x, fallback)`.

## Tools (Dave and MCP)

- `add_formula_field` { list / listId, name, description | expression }: with a `description` Flexlists
  writes the formula (the same AI and validate loop as the field editor's "Describe it instead");
  with an `expression` it is checked as is. Invalid formulas are never saved; the error comes back.
- `explain_formula` { list / listId, field | expression }: plain-language explanation.

## Examples

| Wish | Formula |
|---|---|
| Days in shelter, from intake to adoption or until today | `DATETIME_DIFF(IF({Adopted on}, {Adopted on}, TODAY()), {Intake Date}, "days")` |
| Price with VAT | `ROUND({Price} * 1.21, 2)` |
| Full name | `TRIM({First name} & " " & {Last name})` |
| Overdue | `AND({Due}, IS_BEFORE({Due}, TODAY()), NOT({Done}))` |
| Tags without "internal" | `FILTER({Tags}, item != "internal")` |
| Number of tags | `COUNTA({Tags})` |
| Domain of an e-mail | `REGEX_EXTRACT({Email}, "@(.+)$")` |

## Functions

### Numbers

| Function | Gives | What it does | Example |
|---|---|---|---|
| `ABS(value)` | number | The value without its sign | `ABS({Balance})` |
| `AVERAGE(value1, value2, ...)` | number | Average of the values (blanks are skipped); also AVG | `AVERAGE({Q1}, {Q2}, {Q3})` |
| `CEILING(value, [significance])` | number | Rounds up to the nearest multiple of significance (1 when left out) | `CEILING({Hours}, 0.25)` |
| `COUNT(value1, value2, ...)` | number | How many of the values are numbers | `COUNT({Q1}, {Q2}, {Q3})` |
| `COUNTA(value1, value2, ...)` | number | How many of the values are not empty (the length of an array) | `COUNTA({Tags})` |
| `COUNTALL(value1, value2, ...)` | number | How many values there are, empty ones included | `COUNTALL({Tags})` |
| `EVEN(value)` | number | Rounds away from zero to the nearest even integer | `EVEN({Guests})` |
| `EXP(power)` | number | e raised to the power | `EXP(1)` |
| `FLOOR(value, [significance])` | number | Rounds down to the nearest multiple of significance (1 when left out) | `FLOOR({Price}, 5)` |
| `INT(value)` | number | The largest integer not above the value | `INT({Hours})` |
| `LOG(number, [base])` | number | Logarithm in a base (10 when left out) | `LOG(1000)` |
| `LN(number)` | number | Natural logarithm | `LN({Growth})` |
| `MAX(value1, value2, ...)` | any | The largest value (the latest, for dates) | `MAX({Score}, 0)` |
| `MEDIAN(value1, value2, ...)` | number | The middle value | `MEDIAN({Q1}, {Q2}, {Q3})` |
| `MIN(value1, value2, ...)` | any | The smallest value (the earliest, for dates) | `MIN({Price}, 100)` |
| `MOD(value, divisor)` | number | The remainder after dividing (it has the sign of the divisor) | `MOD({Row}, 2)` |
| `ODD(value)` | number | Rounds away from zero to the nearest odd integer | `ODD({Seats})` |
| `POWER(base, power)` | number | base raised to power | `POWER({Side}, 2)` |
| `ROUND(value, decimals)` | number | Rounds to a number of decimals (0 to 10) | `ROUND({Amount} * 1.21, 2)` |
| `ROUNDDOWN(value, [decimals])` | number | Rounds toward zero (negative decimals round to tens, hundreds, …) | `ROUNDDOWN({Price}, 1)` |
| `ROUNDUP(value, [decimals])` | number | Rounds away from zero | `ROUNDUP({Hours}, 0)` |
| `SIGN(value)` | number | 1 for a positive value, -1 for a negative one, 0 for zero | `SIGN({Balance})` |
| `SQRT(value)` | number | Square root | `SQRT({Area})` |
| `SUM(value1, value2, ...)` | number | Adds the values (and the values of arrays) | `SUM({Q1}, {Q2}, {Q3})` |
| `VALUE(text)` | number | Reads text as a number ("€1,250.50" → 1250.5, "50%" → 0.5) | `VALUE({Amount as text})` |
| `SUMIF(range, criterion, [sum_range])` | number | Adds the values (of sum_range) where range meets the criterion (5, ">5", "<>x", "a*") | `SUMIF({Scores}, ">50")` |
| `COUNTIF(range, criterion)` | number | How many values meet the criterion | `COUNTIF({Tags}, "urgent")` |
| `AVERAGEIF(range, criterion, [average_range])` | number | Average of the values (of average_range) where range meets the criterion | `AVERAGEIF({Scores}, ">0")` |

### Text

| Function | Gives | What it does | Example |
|---|---|---|---|
| `CONCATENATE(text1, text2, ...)` | text | Joins texts (same as &); also CONCAT | `CONCATENATE({First}, " ", {Last})` |
| `ENCODE_URL_COMPONENT(text)` | text | Text made safe to put in a link | `"https://maps.example/?q=" & ENCODE_URL_COMPONENT({Address})` |
| `FIND(search, text, [start])` | number | Position of search in text (1 is the first character), 0 when not there; case-sensitive | `FIND("@", {Email})` |
| `LEFT(text, count)` | text | The first count characters | `LEFT({Postcode}, 4)` |
| `LEN(text)` | number | Number of characters | `LEN({Name})` |
| `LOWER(text)` | text | Text in lower case | `LOWER({Email})` |
| `MID(text, start, count)` | text | count characters from position start (1 is the first) | `MID({Code}, 3, 2)` |
| `REPLACE(text, start, count, replacement)` | text | Replaces count characters from position start | `REPLACE({Phone}, 1, 1, "+31 ")` |
| `REPT(text, times)` | text | The text repeated | `REPT("★", {Rating})` |
| `RIGHT(text, count)` | text | The last count characters | `RIGHT({IBAN}, 4)` |
| `SEARCH(search, text, [start])` | number | Position of search in text ignoring case; blank when not there | `SEARCH("dog", {Notes})` |
| `SUBSTITUTE(text, old, new, [index])` | text | Replaces old by new everywhere, or only its index-th occurrence | `SUBSTITUTE({Phone}, " ", "")` |
| `T(value)` | text | The value when it is text, else empty | `T({Notes})` |
| `TRIM(text)` | text | Text without spaces at the start and end | `TRIM({Name})` |
| `UPPER(text)` | text | Text in capitals | `UPPER({Code})` |
| `PROPER(text)` | text | Every word starting with a capital | `PROPER({Name})` |
| `TEXTJOIN(separator, ignore_empty, text1, ...)` | text | Joins the values (and arrays) with a separator, optionally skipping empty ones | `TEXTJOIN(", ", TRUE, {City}, {Country})` |
| `SPLIT(text, [separator])` | array | Splits text into an array (at commas when no separator is given) | `SPLIT({Keywords}, ";")` |
| `REGEX_MATCH(text, regex)` | boolean | Whether the text matches the regular expression (RE2 syntax) | `REGEX_MATCH({Email}, "^[^@]+@[^@]+$")` |
| `REGEX_EXTRACT(text, regex)` | text | The first part of the text that matches; blank when nothing does | `REGEX_EXTRACT({Notes}, "[0-9]{4}")` |
| `REGEX_REPLACE(text, regex, replacement)` | text | Replaces every match ($1 is the first group) | `REGEX_REPLACE({Phone}, "[^0-9+]", "")` |

### Logic

| Function | Gives | What it does | Example |
|---|---|---|---|
| `AND(value1, value2, ...)` | boolean | True when every value is true (also &&) | `AND({Paid}, {Shipped})` |
| `OR(value1, value2, ...)` | boolean | True when any value is true (also \|\|) | `OR({Urgent}, {Due} < 3)` |
| `XOR(value1, value2, ...)` | boolean | True when an odd number of the values is true | `XOR({A}, {B})` |
| `NOT(value)` | boolean | The opposite (also !) | `NOT({Archived})` |
| `IF(condition, then, [else])` | any | One value when the condition is true, another when not | `IF({Stock} > 0, "In stock", "Sold out")` |
| `IFS(condition1, value1, ...)` | any | The value of the first true condition | `IFS({Score} >= 90, "A", {Score} >= 75, "B", TRUE, "C")` |
| `SWITCH(expression, pattern1, result1, ..., [default])` | any | The result of the first pattern equal to the expression, else the default | `SWITCH({Status}, "Open", 1, "Done", 2, 0)` |
| `TRUE()` | boolean | True (also written TRUE) | `TRUE()` |
| `FALSE()` | boolean | False (also written FALSE) | `FALSE()` |
| `BLANK()` | any | An empty value | `IF({Done}, BLANK(), {Due})` |
| `ISBLANK(value)` | boolean | True when the value is empty (blank, "" or an empty array) | `ISBLANK({Email})` |
| `ERROR([message])` | any | An #ERROR! value, with an optional message | `IF({Qty} < 0, ERROR("Negative quantity"), {Qty})` |
| `ISERROR(value)` | boolean | True when the value is an error (#DIV/0!, #VALUE!, …) | `ISERROR({Amount} / {Qty})` |
| `IFERROR(value, value_if_error)` | any | The value, or value_if_error when it is an error | `IFERROR({Amount} / {Qty}, 0)` |

### Dates and times

| Function | Gives | What it does | Example |
|---|---|---|---|
| `DATETIME_PARSE(text, [format], [locale])` | date | Reads text as a date and time; format uses moment tokens (DD/MM/YYYY HH:mm) | `DATETIME_PARSE({Date text}, "DD-MM-YYYY")` |
| `DATETIME_FORMAT(date, [format])` | text | A date as text; format uses moment tokens (D MMMM YYYY, LL, h:mm A, …) | `DATETIME_FORMAT({Due}, "D MMM YYYY")` |
| `DATESTR(date)` | text | The date as YYYY-MM-DD | `DATESTR({Due})` |
| `TIMESTR(date)` | text | The time as HH:mm:ss | `TIMESTR({Starts})` |
| `YEAR(date)` | number | The year | `YEAR({Born})` |
| `MONTH(date)` | number | The month, 1 to 12 | `MONTH({Due})` |
| `DAY(date)` | number | The day of the month, 1 to 31 | `DAY({Due})` |
| `HOUR(date)` | number | The hour, 0 to 23 | `HOUR({Starts})` |
| `MINUTE(date)` | number | The minute, 0 to 59 | `MINUTE({Starts})` |
| `SECOND(date)` | number | The second, 0 to 59 | `SECOND({Starts})` |
| `WEEKDAY(date, [start])` | number | Day of the week: 0 is Sunday (or Monday with start "Monday") up to 6 | `WEEKDAY({Due}, "Monday")` |
| `WEEKNUM(date, [start])` | number | Week of the year (week 1 has January 1st; weeks start on Sunday, or Monday) | `WEEKNUM({Due})` |
| `DATEADD(date, count, unit)` | date | The date plus count units (days, weeks, months, years, hours, …) | `DATEADD({Intake}, 14, "days")` |
| `DATETIME_DIFF(date1, date2, [unit])` | number | date1 minus date2 in whole units (seconds when left out) | `DATETIME_DIFF(TODAY(), {Intake}, "days")` |
| `WORKDAY(start, days, [holidays])` | date | The working day (Monday to Friday, not a holiday) days working days after start | `WORKDAY({Ordered}, 5, "2025-12-25, 2025-12-26")` |
| `WORKDAY_DIFF(start, end, [holidays])` | number | Working days from start to end, both counted (negative when end is earlier) | `WORKDAY_DIFF({Start}, {End})` |
| `IS_BEFORE(date1, date2)` | boolean | Whether date1 is before date2 | `IS_BEFORE({Due}, TODAY())` |
| `IS_AFTER(date1, date2)` | boolean | Whether date1 is after date2 | `IS_AFTER({Delivered}, {Due})` |
| `IS_SAME(date1, date2, [unit])` | boolean | Whether the dates are the same (the same day, month, … with a unit) | `IS_SAME({Due}, TODAY(), "day")` |
| `TODAY()` | date | Today in the formula’s timezone (updated daily) | `DATETIME_DIFF(TODAY(), {Intake}, "days")` |
| `NOW()` | date | The current date and time (updated every 15 minutes) | `DATETIME_DIFF(NOW(), {Created}, "hours")` |
| `TONOW(date)` | number | Days from the date until now (positive for a past date) | `TONOW({Last contact})` |
| `FROMNOW(date)` | number | Days from now until the date (positive for a future date) | `FROMNOW({Due})` |
| `SET_TIMEZONE(date, timezone)` | date | The date shown in another timezone ("Europe/Amsterdam") | `DATETIME_FORMAT(SET_TIMEZONE({Starts}, "America/New_York"), "LLL")` |
| `SET_LOCALE(date, locale)` | date | The date shown in another language ("nl", "de", …) by DATETIME_FORMAT | `DATETIME_FORMAT(SET_LOCALE({Due}, "nl"), "D MMMM YYYY")` |

### Arrays (lists of values)

| Function | Gives | What it does | Example |
|---|---|---|---|
| `ARRAYJOIN(array, [separator])` | text | The items joined into text (", " when no separator is given) | `ARRAYJOIN({Tags}, " · ")` |
| `ARRAYUNIQUE(array)` | array | The array without repeated items | `ARRAYUNIQUE({Tags})` |
| `ARRAYCOMPACT(array)` | array | The array without empty items | `ARRAYCOMPACT({Values})` |
| `ARRAYFLATTEN(array)` | array | Nested arrays turned into one flat array | `ARRAYFLATTEN([{Tags}, {Labels}])` |
| `ARRAYSLICE(array, start, [end])` | array | Items start to end (1 is the first item, -1 the last; end included) | `ARRAYSLICE({Tags}, 1, 3)` |
| `FILTER(array, condition)` | array | The items for which the condition is true; item is the element, index its position (1 is the first) | `FILTER({Tags}, item != "internal")` |
| `MAP(array, expression)` | array | The expression for every item; item is the element, index its position (1 is the first) | `MAP({Tags}, UPPER(item))` |
| `SORT(array, [key], [order])` | array | The items sorted; key is an expression of item, order "asc" (default) or "desc" | `SORT({Tags}, "desc")` |
| `REDUCE(array, initial, expression)` | any | Combines the items into one value: acc starts as initial and becomes the expression for each item; item is the element, index its position (1 is the first) | `REDUCE({Scores}, 0, acc + item)` |
| `FIRST(array)` | any | The first item | `FIRST({Tags})` |
| `LAST(array)` | any | The last item | `LAST({Tags})` |
| `INDEX(array, position)` | any | The item at a position (1 is the first, -1 the last); blank past the end | `INDEX(SPLIT({Full name}, " "), 1)` |
| `CONTAINS(array, value)` | boolean | Whether the array has the value as an item (or the text contains it) | `CONTAINS({Tags}, "urgent")` |
| `ANY(array, [condition])` | boolean | Whether the condition is true for any item (any item is true, without a condition); item is the element, index its position (1 is the first) | `ANY({Scores}, item > 90)` |
| `ALL(array, [condition])` | boolean | Whether the condition is true for every item; item is the element, index its position (1 is the first) | `ALL({Checks}, item = "OK")` |

### The row itself

| Function | Gives | What it does | Example |
|---|---|---|---|
| `RECORD_ID()` | text | The id of the row | `"https://example.com/rows/" & RECORD_ID()` |
| `CREATED_TIME()` | date | When the row was added | `DATETIME_DIFF(NOW(), CREATED_TIME(), "days")` |
| `LAST_MODIFIED_TIME([field1, ...])` | date | When the row last changed (of the whole row; fields are not tracked one by one) | `LAST_MODIFIED_TIME()` |
| `CREATED_BY()` | text | Who added the row | `CREATED_BY()` |
| `LAST_MODIFIED_BY()` | text | Who last changed the row | `LAST_MODIFIED_BY()` |