# Formulas, threads and gathers

> Computed properties for your databases. Formulas calculate a value for each note, threads link notes to other notes, and gathers roll values up across a thread.

Source: https://terramd.io/docs/formulas-threads-and-gathers

A *computed property* is a column that a [database](https://terramd.io/docs/databases) works out for you instead of one you type. There are four kinds:

| Kind | What it is | Also known as |
| --- | --- | --- |
| **Formula** | A value calculated for each note from its other properties | A formula column |
| **Thread** | Links from each note to other notes | A relation |
| **Gather** | A value rolled up across a thread, such as a total or the latest date | A rollup |
| **Person** | A thread to notes about people | A person property |

![The All work table with three computed columns: Days left showing numbers or a dash, milestone showing First draft or Launch, and milestone_due showing a date.](https://terramd.io/docs/shots/db-computed-table.webp "A formula (Days left), a thread (milestone) and a gather (milestone_due), next to ordinary properties.")

## Add a computed property

1. Open a database you made, in a Table view.
2. Click **+** (**Add property**) at the right end of the header row.
3. Under **Computed**, choose **Formula**, **Thread**, **Gather** or **Person**.
4. Type a name where it says `field_name`, fill in the definition, and click the check (**Add field**).

![The Add property menu: New property, Existing property, and a Computed section with Formula, Thread, Gather and Person.](https://terramd.io/docs/shots/db-add-computed.webp "Computed kinds sit below New property and Existing property.")

Names are stored in lower case with underscores, so *Days left* is `days_left` in formulas. To change a computed property, click its column header; the editor opens with **Apply** and **Remove field**. **Save to library** keeps a copy to reuse in another database, and **Load preexisting** brings one back.

Computed properties need a database you made; built-ins, folders and tag pages don't offer them. Filters don't offer them yet either, but a [chart](https://terramd.io/docs/views-and-layouts#layouts) can add up or average a formula or a gather.

## Formulas

A formula is an expression worked out for each row, much like a spreadsheet formula. Refer to a property with `@`:

```text
if @status == "Done" then null else diff_days(@due, today())
```

For *Draft chapter 2: Boards*, due 2026-10-17, that gives `1` on October 16; for finished items it's empty.

![The Edit formula popover for days_left, with the formula, a preview row picker set to Draft chapter 2: Boards showing 1, and the result type number.](https://terramd.io/docs/shots/db-formula-editor.webp "The row picker under the formula previews the result for one note while you type.")

The pieces:

| Write | Means |
| --- | --- |
| `@effort` | The note's `effort` property. Use quotes for names with spaces: `@"due date"`. |
| `@title`, `@type`, `@tags`, `@created`, `@updated` | The note's built-in fields. `@tags` is a list. |
| `@days_left` | Another computed property on the same database |
| `@milestone.@due` | The `due` property of every note the `milestone` thread links to, as a list |
| `12`, `"late"`, `true`, `false`, `null` | A number, text, yes or no, nothing. Text can't contain a `"`. |
| `+` `-` `*` `/` `%` `^` | Arithmetic on numbers. `%` is the remainder, `^` a power. |
| `++` | Joins two pieces of text: `@owner ++ " (owner)"` |
| `==` `!=` | Equal, not equal. Text must match exactly, capitals included. |
| `<` `<=` `>` `>=` | Compare numbers, dates or text |
| `&&` `\|\|` `!` | And, or, not |
| `a ?? b` | `a`, or `b` when `a` is empty |
| `if … then … else …` | Choose between two results. Write it in lower case. |
| `let x = … in …` | Name part of a formula to use it more than once |

Things that trip people up:

- If a property a formula uses is empty, the result is empty, unless you use `==`, `!=`, `??` or `coalesce()`.
- Dates written as `YYYY-MM-DD` in a property are read as dates, so `@due < today()` works. Do arithmetic on dates with `add_days()` and `diff_days()`; `+` and `-` only take numbers.
- `today()` and `now()` use UTC.
- Function names ignore capitals, except `if`: a lower-case `if` starts `if … then … else`, so write the function form as `IF(@effort > 3, "big", "small")`.
- A formula that can't be worked out for one note shows an error in that cell only.
- **Ask AI** in the editor writes or fixes a formula from a description, once an AI provider is connected.

### Functions

| Kind | Functions |
| --- | --- |
| Numbers | `abs(x)`, `ceil(x)`, `floor(x)`, `round(x)` or `round(x, places)`, `min(…)`, `max(…)`, `sum(…)`, `average(…)`, `avg(list)`, `median(…)` |
| Text | `length(t)`, `upper(t)`, `lower(t)`, `slice(t, start, end)`, `contains(t, part)`, `replace(t, find, with)`, `split(t, separator)`, `join(list, separator)`, `regex_match(t, pattern)`, `concat(…)` |
| Dates | `today()`, `now()`, `year(d)`, `month(d)`, `day(d)`, `weekday(d)` (Monday is 1), `add_days(d, n)`, `diff_days(a, b)` (a minus b, in whole days), `format(d, pattern)`, `date(t)` |
| Logic | `IF(test, a, b)`, `not(x)`, `and(…)`, `or(…)`, `coalesce(a, b, …)` |
| Conversion | `to_number(x)`, `to_string(x)`, `to_date(t)`, `to_bool(x)` |
| Lists | `count(list)`, `counta(…)`, `unique(list)`, `any(list)`, `all(list)` |

`contains()` and `replace()` respect capitals. `format()` takes patterns like `"MMM D"` (*Oct 17*) or `"YYYY-MM-DD"`. `date()` and `to_date()` need a full timestamp, such as `"2026-11-01T00:00:00Z"`. `vlookup`, `index` and `match` exist for sheets and have nothing to look up in a database.

## Threads

A thread links each note to other notes: work items to their milestone, papers to their authors, meetings to a project. Choose how it finds those notes:

- **Linked**: you pick them. Set **Link to** **one** or **many**, and the notes **from** **anywhere** or from one database. A thread that picks from a database gets a picker in its table cells.
- **Matched**: conditions pick them. Choose the database to **Match from**, then add conditions as in a [filter](https://terramd.io/docs/filter-sort-and-group). In a text condition's value, `@title` stands for the note's own title and `@id` for its id, so a *Projects* database can match every task whose `project` **is** `@title`. Switch **notes** to **blocks** to match Sprouts, headings and other blocks inside notes instead of whole notes.

![The Edit thread popover: Link to one note from Milestones, the Linked tab selected, and Two-way switched on with the name work in Milestones.](https://terramd.io/docs/shots/db-thread-editor.webp "A thread to one note in Milestones. With Two-way on, Milestones gets a work column listing the items that point at each milestone.")

**Two-way** appears once a thread draws on a database. Switch it on and name the other side: that database gets a column of the notes that point at each of its notes. For a Linked thread you can edit the link from either side; for a Matched one the other side is read-only.

Threads live in your notes. Each note keeps its links under `relations:` in its [properties](https://terramd.io/docs/properties), by title:

```yaml
relations:
  milestone: '[[First draft]]'
```

For a thread to notes anywhere, type the links there yourself. Matched threads write their matches into the same block, refreshed when you open or save the note. The other side of a two-way thread is never written into the other notes; Terra works it out from the links.

## Gathers

A gather reads through a thread and sums, counts or picks from the linked notes. It needs a thread on the same database first. Write it as one function around a thread and a field:

```text
LATEST(@milestone.@due)
```

![The Edit gather popover for milestone_due with the formula LATEST(@milestone.@due) and a preview of 2026-10-24.](https://terramd.io/docs/shots/db-gather-editor.webp "The work item's milestone date, read from the milestone it links to.")

| Function | Gives |
| --- | --- |
| `SUM(@thread.@field)`, `AVG(…)`, `MIN(…)`, `MAX(…)` | A total, average, smallest or largest number |
| `COUNT(@thread)` | How many notes the thread links to |
| `EARLIEST(@thread.@field)`, `LATEST(…)` | The first or last date |
| `UNIQUE(@thread.@field)` | Each value once, as a list |
| `CONCAT(@thread.@field)` | The values joined with commas |

The field can be any property of the linked notes, or a built-in such as `title`, `created` or `updated`. Notes without a value are skipped. On a *Milestones* database with a `work` thread, `SUM(@work.@effort)` totals each milestone's effort. For anything more, use a formula: `@work.@effort` there is the list of values, so `round(avg(@work.@effort), 1)` works.

## Person

**Person** adds a thread called `people` (then `people_2`, and so on) to notes whose type is `person`. Pick people in its table cells; they show as chips with their photos. Click the column header to rename it.

## How they're stored

| Kind | Its definition | Its values |
| --- | --- | --- |
| Formula, gather | In the database's file, `.terra/databases/<id>.json` | Worked out when the view opens; never written to notes |
| Thread, Person | In the database's file | In each note's `relations:` |

So formulas and gathers belong to their database, and a note shows them only there. For a note that isn't in any database, open its properties, click **+**, and choose **Formula**, **Thread**, **Gather** or **Person**: Terra asks which database to **Evaluate against**, adds the definition to that database, and records the link in the note:

```yaml
computed:
  days_left: 01JQ8ZB4K2M7X9T3R5V6W8Y0C1
```

## Related

- [Databases](https://terramd.io/docs/databases) — where computed properties live.
- [Properties](https://terramd.io/docs/properties) — the typed values formulas read.
- [Views and layouts](https://terramd.io/docs/views-and-layouts) — charts that sum a formula or a gather.
- [Run a project](https://terramd.io/docs/project-suite) — threads that roll milestone progress up a timeline.