---
title: "Formulas"
description: "Write a formula over a row and over the rows its relations point at. The full function list by category, the operators, the error codes, the caps, and why today() refreshes daily and now() does not exist."
section: "Tables"
canonical: "https://banksync.io/docs/tables/tables-formulas"
---

A **formula** field calculates its value from the row it is on, and from the rows that row's [relations](/docs/tables/tables-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](/docs/tables/tables-rollups). 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](/docs/tables/tables-rollups) are built on — they are formulas the configurator writes for you.

## Operators

| Operators                  | Meaning                                       |
| -------------------------- | --------------------------------------------- |
| `+` `-` `*` `/` `%` `^`    | Arithmetic; `^` is a power and binds tightest |
| `=` `!=` `<` `<=` `>` `>=` | Comparison                                    |
| `and` `or` `not`           | Logic                                         |
| `? :`                      | Conditional, as an alternative to `if`        |
| `-x`                       | Negation                                      |

## 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.

| Cell      | Meaning                                                                    |
| --------- | -------------------------------------------------------------------------- |
| `#DIV/0!` | Division by zero.                                                          |
| `#VALUE!` | The value could not be used — a negative square root, an unparseable date. |
| `#REF!`   | A field or table the formula reads has been deleted or trashed.            |
| `#TYPE!`  | A value was the wrong type for what the formula asked of it.               |
| `#LIMIT!` | The formula is more complex than the engine will evaluate.                 |
| `#DEPTH!` | The formula reaches further than 5 hops from its own row.                  |
| `#LINKS!` | A relation cell it traverses holds more links than the engine will follow. |
| `#N/A`    | The value is not available.                                                |

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.

| Limit                                     | Value            |
| ----------------------------------------- | ---------------- |
| Formula length                            | 4,000 characters |
| Distinct fields one formula may reference | 100              |
| Expression size                           | 500 nodes        |
| Expression nesting                        | 32 levels        |
| Hops across tables                        | 5                |
| Links followed per relation cell          | 100              |
| Regular-expression pattern length         | 256 characters   |

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](/docs/tables/tables-rollups) — the same engine, configured rather than typed.
- [Field types in a table](/docs/tables/tables-fields) — what each kind gives a formula to work with.