Columns, formulas and lookups

Every column type in Work, how formulas and lookups are calculated, and the errors you can get.

A board has typed columns. An item stores one value per column, in a cell. This page lists the types, then explains Formula and Lookup, the two columns that are calculated for you.

Column types

Type	Holds	Notes
Text	One line
Long text	Many lines
Number	A decimal	Display can be plain, percent or currency. This changes how it looks, not the value.
Date	A date
Status	One label	You define the labels. Each label can have a colour.
Multi-select	Several labels
Person	A Member	Human or Agent.
Link	A URL with an optional label
File	Attachments	See Integrations and realtime.
Doc	A Markdown document
Connect	A link to items on another board
Lookup	A value read through a Connect column	Read-only.
Formula	A value calculated from other columns	Read-only.
AI	Text written by an Agent from a prompt	Filled on request, on create or on update.
Created at, Updated at	Timestamps	Read-only.

Each change to a cell has its own version. If two people change the same cell at once, the second save gets a 409 conflict with a conflict_token. Read the item again and decide. It is not a fatal error.

You can reorder columns, rename them and delete them. You cannot delete or retype a column that a Formula reads.

Formula columns

A Formula calculates from the other columns of the same item. Write column names in square brackets, for example [Price] * [Quantity]. Names are not case sensitive and can contain spaces. Worksong stores the column ids, so renaming a column never breaks a formula.

Set two things when you create the column:

• Formula: the expression.
• Result type: Number (default), Text or Date. The result is converted to this type.

Changing the expression or the result type recalculates every item of the board. A board with more than 2,000 items is refused with formula_too_many_items.

To check an expression before you save it, call POST /v1/boards/{boardId}/formulas/validate with expression (and the columnId you are editing, to also check for cycles). It changes nothing and returns valid, error, code and references.

Syntax

• Operators: + - * / %, comparisons = != < <= > >=, and and or not.
• Join text with CONCAT(a, b, …). + also joins two texts. The operators ^ and & do not exist: use POW and CONCAT.
• Literals: numbers, "text" or 'text', true, false.
• A blank value makes an arithmetic result blank. It is never silently 0. Use IFBLANK([x], 0) if you want 0.
• A formula can read Text, Long text, Status, Multi-select, Date, Number, Link, AI and other Formula columns. It cannot read Person, Connect, File, Doc, Lookup, Created at or Updated at.

Functions

Group	Functions
Logic	IF(cond, then, else), IFS(c1, v1, c2, v2, …[, default]), AND, OR, NOT, IFBLANK(x, fallback), ISBLANK(x)
Math	ABS, ROUND(x[, digits]) (half away from zero), FLOOR, CEILING, MIN, MAX, SUM, AVG, COUNT, POW(x, y), SQRT, MOD(x, y)
Text	CONCAT, LEN, LOWER, UPPER, TRIM, LEFT(t, n), RIGHT(t, n), MID(t, start, len) (starts at 1), CONTAINS(t-or-list, x), REPLACE(t, old, new), TEXT(x[, format])
Date	DATE(y, m, d), DAYS(a, b) (whole days, a minus b), ADDDAYS(d, n), YEAR, MONTH, DAY, WEEKDAY (Monday = 1 to Sunday = 7)

SUM, AVG, MIN, MAX, COUNT and CONCAT accept lists, so COUNT([Tags]) counts the labels of a Multi-select. IF, IFS and IFBLANK calculate only the branch they use.

There is no TODAY() or NOW(). A stored result that depends on the clock would be out of date until someone edits the row. Use a Date column that an automation fills in.

How the value is kept

• The result is stored with the item, so sorting, filtering, dashboards and exports see it.
• It is recalculated in the same save that changed an input. It never lags, and a save that fails leaves nothing half-calculated.
• Numbers are kept to 12 decimal places, so 10/3 is stored as 3.333333333333.
• You cannot write to a Formula cell, not even to clear it. The API answers 400.
• A Formula cell does not start an automation.

Errors

A problem at save time is a 400 with a code:

formula_empty, formula_syntax, formula_too_long (over 1,000 characters), formula_too_deep (over 32 nested parentheses), formula_unknown_column, formula_ambiguous_column, formula_bad_reference, formula_unknown_function, formula_arity, formula_operator, formula_cycle, formula_chain_too_deep (over 10 formulas reading formulas), formula_dependency, formula_too_many_items.

A problem while calculating never blocks the save. The cell shows an error value that starts with #ERR:: div0 (division or MOD by zero), type, value, overflow, size (result over 64 KB), timeout (over 250 ms) or ref. An error flows into formulas that read the cell, and fixing the input heals them.

Lookup columns

A Lookup shows a column of the item that a Connect column points to. Set two things:

• Connect column: a Connect column on this board.
• Target column: a column on the board that the Connect column points to.

Allowed targets are Text, Long text, Status, Multi-select, Person, Date, Number, Link and AI. A Lookup cannot read another Lookup, so there are no chains. A Lookup cannot be created by changing the type of a column.

The value is read when you open the board, and is never stored. It shows as text: the label for Status, labels joined with commas for Multi-select, the member’s name for Person. It is blank when there is no connection, the connected item is gone, or you may not read the connected board. Because it is not stored, you cannot filter, sort or total on a Lookup, and dashboards ignore it.

Open boards update when a connected value changes.

_{Source: worksong-ai/worksong:services/work/docs/formulas.md@9f0684f, worksong-ai/worksong:services/work/docs/lookup-columns.md@9f0684f, worksong-ai/worksong:services/work/src/Boardy.Schema/Domain/Column.cs@9f0684f, worksong-ai/worksong:services/work/src/Boardy.Api/Program.cs@9f0684f. Docs version: Worksong 2.0 with Work 1.2.}