Links, lookups, rollups and formulas
Links, lookups, rollups and formulas
Who can do this: Owner on the space or Manage on the table. Adding or changing any of these columns saves the table's columns, and that needs the owner right on the table. People with only Edit can fill ordinary cells and pick links, but cannot create or change these columns. Everyone who can read the table sees the results. See the .
What it's for
Four column types connect tables or calculate from other cells. They are the difference between a list and a working system: an order that knows its customer, a line that shows its product's price, an invoice that adds up its own lines, a task that knows it is late.
Type | Use it to… | Cell content | You type in it? |
|---|---|---|---|
Reference | Point a row at rows of another table (or the same table) | A list of linked rows: each is a row id and a copy of its label | Yes, you pick rows |
Lookup | Show one value from a linked row | A stored copy of that value | No |
Rollup | Add up, count or collect many related rows | A stored result | No |
Formula | Calculate from other cells of the same row | A stored result | No |
"Stored" means the table keeps the result in the row, like any other value. Sorting, filtering, grouping, exports, dashboards and automations read the stored value. The engine behind this is explained in . The basic column types are on ; the formula functions are on .
A typical chain: Order lines has a Reference to Orders and another to Products. A Lookup on the line shows the product's price, a Formula multiplies it by the quantity, and a Rollup on Orders sums the line totals.
1. Reference
Add one
In the table, choose Add column, type a Field name, and under Relational & computed pick Reference.
The Link to table dialog opens before the column exists. Select linked table picks the target (any data table of the space, or this one).
Choose the Display field, the Selection mode, and optionally a filter and extra display fields (below). Save creates the column.
Open the column menu later and choose Configure reference to change these.
Settings
Setting in the dialog | What it does |
|---|---|
Select linked table | The target table. Once the column holds links you cannot change it: "This column contains data. Clear the column first, then change the target table." |
Display field | The target column whose value is the label of every link. Choices are text-like columns (Text, Long text, Email, URL, Phone, Number, Currency, Sequence) and also Reference, Lookup and Formula columns, so "First name + Last name" can be a formula column. Auto (first text field) is offered, but the dialog saves a concrete field when you press Save |
Additional display fields | Extra target fields shown under the display field in the picker, and searched there. Also show in cells puts each on its own line in the link badge. It also changes exports and formulas that read the link as text |
Selection | Single selection or Multiple selection |
Relationship type | An optional label ("Cross-sell", "Component of", or your own text) shown in the row detail panel. It does not change behaviour |
Limit selectable rows | Narrows which target rows may be linked: Restrict by group field (only rows sharing a group with the person) and Filter formula (see below) |
Further options (a category link, a booked resource, billing and ledger roles) are set from other menus or by modules. This dialog does not show them, and they do nothing unless the table has the matching behaviour.
Careful: a reference column allows several links unless it is set to Single selection. A new column starts on Multiple selection in the dialog, and so does a column created by an integration or an AI agent without that setting. When a row should link to one record, choose Single selection; Save always records the choice. A parent link (Enable sub-tasks) is always single. Connected apps are stricter: an app can send several links only to a column saved with Multiple selection, so open and save Configure reference once on an older column an app writes several links to.
What the cell stores and shows
Each link stores the target row's id and a copy of its label, taken when you pick it. The grid shows the label without loading the other table. The platform keeps these copies current:
The target row is renamed (its display field changes). Every link to it is relabelled automatically, up to 5,000 links per change. Anything beyond that is left for Validate references….
You change the Display field of the column. Labels are re-baked once, automatically.
The target row is deleted. The row goes to Recently deleted rows and can be restored with its original id; links to it resolve again. While it is deleted, a link keeps showing its old label, a Lookup through it becomes empty, and a Rollup drops that row. A row that a locked record points at cannot be deleted; reverse or void that record first.
Validate references… (column menu) scans the column. Stale labels are fixed with Update stale labels (or Refresh all labels). Invalid links (the target is gone) can be marked with Mark (the label gets a #ERR- prefix so you can filter and fix by hand) or deleted with Remove, which asks for confirmation and cannot be undone in bulk.
What is checked when someone links a row
People, forms, the portal, the API and AI agents are checked. Imports and server-internal writes are trusted.
Every newly added id must be a live row in the target table. Otherwise the write is refused: "received N id(s) that do not resolve to rows in the target table". Links already stored are not re-checked by an unrelated edit.
A column with no target table cannot receive links.
The target row must have a readable label. A link to a row whose display field is blank is refused, because it would show as an empty badge.
The Limit selectable rows rules are enforced on write for people, not only in the picker. A portal user or a public form visitor gets one generic refusal whether the row does not exist or is not an allowed choice.
One write may add at most 500 links to one column.
From forms, imports and pasting
Pasting text into a reference column opens Link pasted values: for each distinct value you link a suggested row, create a new one, or leave empty, then Link values.
Turning a text column into a reference opens Map text to records; Apply mapping links the matches and clears the rest.
2. Lookup
A lookup copies one column of a related row into this row, read-only. It shows the copy the way the source column shows (currency, person, list value, link).
Add column → Lookup. Configure lookup opens.
From rows this row references (the default): choose Through reference, a Reference column of this table. If none exists the dialog says "This table has no reference columns".
From rows that reference this row: choose Parent table (the table whose rows point at this one; only tables that already have a Reference to this table are offered, otherwise "No tables reference this one yet") and Linked by column.
Value to copy: the column to read.
Backlink only: Filter (optional), a formula written with the related table's columns, such as {Status} = "Active".
Save. Later, use Edit lookup in the column menu.
Situation | What the cell shows |
|---|---|
The link is empty, or the linked row is deleted | Empty |
A multiple reference holds several rows (forward) | The value from the first link only |
Backlink: several rows point here | The value from the first row, in table row order, that passes the filter |
Backlink: none point here | Empty |
The source value is empty | Empty |
If the source column is itself a link, Displayed field shows, read-only, what labels the copy; it is inherited from the source column and changed there.
A lookup updates in the same save in which the linked row, the link, or the source cell changes. Tick Disable automatic recalculation to make it a snapshot (see section 5).
3. Rollup
A rollup summarises many related rows into one value.
Add column → Rollup. Configure rollup opens.
Direction: From rows that reference this row (the default; children point at this row) or From rows this row references.
Choose the source: Roll up from Sub-tasks (only when sub-tasks are enabled) or Other table, then Parent table, Linked by column, and Column to aggregate. For counting rows, leave the column on Count rows.
Function (below). 5. Optional Filter (optional). 6. Optional Value when there are no related rows (optional). Save. Later, Edit rollup.
Functions
The names in the Function list are fixed in English in every interface language.
Function | What it does | Reads | Result |
|---|---|---|---|
Sum | Adds the numbers. Empty and non-numeric cells add nothing. With no rows: 0 | Numbers | Number |
Average | Mean of the numeric values; empty if there are none | Numbers | Number |
Min | Smallest number; empty if there are none | Numbers | Number |
Max | Largest number; empty if there are none | Numbers | Number |
Count | With Count rows: the number of related rows (after the filter). With a column: the number of rows where that column is not empty | Any column | Number |
Count unique | Number of distinct non-empty values | Any column | Number |
Concatenate | Non-empty values joined with a comma and a space | Text-like columns | Text |
Count by value | How many related rows hold each value, as coloured badges; blanks go in an "(empty)" badge | Select, Multi-select, Status, Priority | Tally (count map) |
| Adds the tallies of the children. Not in the list; created by Include all sub-levels with Count by value | A tally column | Tally |
Collect references | Joins the children's links into one de-duplicated list. Offered only when the column is a link | Reference (or a lookup that copies one) | Reference |
"Numbers" means number, currency and rating columns, and number-valued formulas, rollups and lookups. Min and Max read numbers only and skip values that are not numbers. Sum and Average round to the column's decimals, or to 4 decimals when none is set, to hide floating-point noise.
Count by value saves from Configure rollup with the tally result type, as it does from Roll up from sub-tasks… (below). A column that an earlier version saved with the text type does not block other changes to its table. Open it in Configure rollup and press Save to correct its type.
Filter and the empty case
Filter (optional) is a formula evaluated for each related row, written with the related table's columns: AND(TODAY() >= {From}, TODAY() <= {To}). Only rows where it is true count. It cannot call the cross-table functions (SUM_TABLE, FIND_VALUE…), and @Column is not allowed. A filter that errors on a row silently drops that row, so a wrong filter shows 0, not an error.
Value when there are no related rows (optional) is a formula over this row's own columns. It applies only when the row has no related rows at all, before the filter. Its use is a hierarchy rolling up into itself, where a leaf row would otherwise contribute nothing.
On a selected rollup cell, a corner button (Show the rows behind this value) lists the rows that add up to it.
Sub-task (hierarchy) rollups
With Enable sub-tasks (menu Data) the table gets a parent link and rows nest. Then Roll up from sub-tasks… (same menu) builds a rollup in one dialog: Aggregate, Field to roll up, Include all sub-levels, Column name, Create roll-up. Without Include all sub-levels it reads direct children. With it, it totals the whole subtree. For Sum this creates a helper rollup plus a formula own value + children. For Count by value it creates a visible rollup of descendants and a hidden helper. The field list follows the aggregate: numbers for the numeric ones, option columns for Count by value, any plain column for the rest.
Worked examples
Order total from lines. Order lines has a Reference "Order" (single) to Orders, plus Quantity and Unit price.
In Order lines, add a Formula "Line total": {Quantity} * {Unit price}. An amount that is an expression must be a column in the related table; the rollup then reads it.
In Orders, add a Rollup "Order total": Direction From rows that reference this row, Parent table Order lines, Linked by column "Order", Column to aggregate "Line total", Function Sum.
Optional: add a Formula in Orders: ROUND({Order total} * 1.2, 2) for the amount with 20% tax.
Adding, editing or deleting a line updates the order's total in the same save.
Open tasks per project. Tasks has a Reference "Project" and a Select "Status" with an option Done. In Projects, add a Rollup: Parent table Tasks, Linked by column "Project", Count rows, Function Count, Filter (optional) {Status} <> "Done". Compare with the option value as written in the column's field settings.
4. Formula
A formula calculates a value from other cells of the same row (and, with the functions below, from other tables). Add Formula under Relational & computed; the column is created and Edit formula opens. Reopen it from the column menu.
Writing a formula
Refer to a column with its name in braces: {Unit price}. Names are not case-sensitive. The editor converts names to column ids when you save, so renaming a column does not break formulas; deleting one does.
Insert a reference by clicking a chip under Insert column reference, or type the name in braces.
Text goes in "double" or 'single' quotes. Numbers use a dot: 12.5. Words true and false are booleans; null, blank and empty mean no value.
Function names are not case-sensitive and are always in English. Block comments /* … */ are allowed. A leading = is ignored.
IN (a, b, …) tests membership: {Priority} IN ("high", "urgent").
Pick AND / OR as words or as && / ||.
Operator | Meaning | Notes |
|---|---|---|
| Arithmetic |
|
| Remainder, power |
|
| Join text |
|
| Equal, not equal | Text compare is case-sensitive. A blank cell is not equal to |
| Order | Dates compare as dates; a blank date counts as earlier than every date |
| Logic | Zero, blank, empty text and false are false; everything else is true |
Precedence, high to low: unary - + !; ^; * / %; + -; &; comparisons and IN; AND; OR. Use brackets when in doubt.
Blanks, text and dates
A blank cell counts as 0 in arithmetic and as empty text when joined. That is convenient and hides gaps: use ISBLANK({X}) or COALESCE({X}, default) to test.
Text that is not a number used in arithmetic is an error: "abc" cannot be converted to a number.
Dates are text in the form 2026-10-28. {Date} + 7 adds days; {A} - {B} is the number of days between (positive when A is later); DAYS(a, b) is positive when b is later. A blank date minus a date is blank.
TODAY() is the UTC date; around midnight in your time zone it can be a day off.
Result type
The editor works out the Result type and shows it under the formula: Number, Text, Boolean (or a reference when the formula ends on a linked row). You do not choose it. The type drives how the column sorts, filters and totals. A formula that returns a date is Text; there is no date result type. If branches of an IF disagree (a number in one, text in the other) the result is Text.
Errors
A formula that cannot be evaluated stores an error message as the cell value. Messages that start with # are shown in red: #DIV/0, #REF: unknown column "…", #VALUE: …. Other messages (Cannot convert…, Unknown function: …) show as text.
Errors spread. A formula that reads an erroring column errors too. Only ISERROR(x) and IFERROR(x, fallback) catch an error.
Saving checks only syntax and column names. A misspelt function name or a wrong number of arguments saves, and then every row errors. Check Preview (it evaluates the first row of the table) before saving. Calendar, price and cross-table functions show blank in Preview because the editor cannot see that data; they work in the saved column.
Errors in hidden helper columns (Use only in formulas (hide by default)) are counted in the columns list next to the show-helpers checkbox.
Select part of the formula to see that part's value under the editor.
Cross-table formulas
If rows are already linked by a Reference, use a Lookup, a Rollup, or LOOKUP({Reference}, "Column") in a formula. They follow the link by row id and update in the same save. Use the value-matching functions only when no link exists (a SKU, a date window, a whole-table total): SUM_TABLE, COUNT_TABLE, AVG_TABLE, MIN_TABLE, MAX_TABLE, FIND_VALUE, FIND_ROW, LOOKUP_TABLE, LOOKUP_ROW. The save check looks at them:
Check | What it says | Outcome |
|---|---|---|
No | Nothing ties the cell to the other table, so it is calculated once and never updates when that table changes | Error. With |
Large target table | More than 1,000 target rows: warning. More than 250,000: refused | Per source row the whole target is scanned |
Large product | Source rows × target rows above 1,000,000: warning; above 25,000,000: refused | |
Cut off | A recompute stops after 60 seconds, so only some rows would be updated; the editor estimates how many | Error |
A link exists already | Advice to use a Rollup, a backlink Lookup, or | Warning |
While you type one of these functions, the editor shows the findings under the formula: errors are titled Will not finish, warnings Performance. A cross-table formula refreshes after a short delay, and on any write to a matched row.
Needs your administrator: whether an "Error" row above blocks the save is an installation switch. By default the platform only reports and the save succeeds. If your installation enforces it, the save is refused with the message. The time budget (60 seconds) is also an installation setting.
JOIN_SUM has no automatic recalculation; the editor requires Disable automatic recalculation.
Editor aids
Functions (tooltip Function reference), or Shift+F1: the browsable panel, grouped by family, with Search functions… and Insert. It marks functions Conditions apply or Not available here for the place you edit.
Type the first letters and press ⌘/Ctrl/Alt+Space for the name picker.
While your cursor is inside a call, the editor shows the argument being typed.
Descriptions are in English, Russian, Spanish and Portuguese, following your interface language. Names and signatures are never translated. Old names TALLY, TALLYGET, TALLYTOTAL still work as COUNTMAP, COUNTMAP_GET, COUNTMAP_TOTAL.
Cookbook
Column names are examples. Each was parsed and run against sample rows, and every function name and argument count was checked against the editor's function catalog.
Goal | Formula | Result |
|---|---|---|
Line total |
| Number |
Gross with tax |
| Number |
Due date, 14 days out |
| Text (date) |
Due at end of next month |
| Text (date) |
Status label |
| Text |
Full name |
| Text |
Margin % (no division by zero) |
| Number |
Days overdue |
| Number |
Working days between dates |
| Number |
Price with a quantity tier |
| Number |
Months employed |
| Number |
Price from the product, via link |
| Number |
Default for a blank |
| Same type as the column |
Safe ratio |
| Number |
Notes. The status label guards ISBLANK first: a blank due date would otherwise count as earlier than today, so every row without one would read "Overdue". WORKDAYS_BETWEEN counts both end days and uses the space's default working calendar (Working Calendars in the space menu); it returns 0 when the start is after the end. LOOKUP needs a real Reference column as its first argument. Compare a Select or Status column with the value the editor offers after =.
5. Recalculation and performance
What | When it recalculates |
|---|---|
Formula | When a cell of the same row is saved, in the same save. Also in the background after you change its definition |
Formula with | Also once a day, just after midnight server time (the hour is an installation setting). Until then the stored value is as of the last recalculation; the status bar says so |
Lookup, Rollup | In the same save, when the source cell, a column its filter reads, or the link changes. Deleting a related row updates them too |
Reference label | On the next change of the target's display field, as described in section 1 |
Cross-table formula | After a short delay, when a matched row changes |
Snapshot | Never automatically. See below |
After you change the definition of a formula, rollup or lookup, the table refills existing cells in the background. The column shows Recomputing…. Until it is done the column is partly updated, so do not total it yet.
Rows that depend on rows (a task in a story in an epic) settle over several passes, up to 30. In the same write, the engine orders columns so that a column is calculated after the ones it reads.
Snapshot columns. Disable automatic recalculation (formula, lookup) freezes a column: a new row starts blank, and the column is refreshed only by Refresh calculations on the row, Recalculate this cell, or a Recompute row automation. It is the cheap way to keep a heavy calculation out of every save. The status bar marks such a cell snapshot — not recalculated automatically and a time-dependent one as of last recalculation. A rollup has no such switch.
Circular references. The editor does not offer a column to itself, but two columns can read each other. The platform does not refuse this. It breaks the loop at one column, which then reads its previous value, and repeats up to 30 passes. The values are wrong or unstable. Remove the loop.
Tips:
Prefer Rollup and Lookup to SUM_TABLE and friends: they read related rows by id instead of scanning the other table once per row.
Keep intermediate calculations in helper columns marked Use only in formulas (hide by default), and roll up the helper.
Avoid TODAY() where a stored date will do: every table with such a formula is recalculated each night.
Keep rollup filters simple; a filter runs on every related row.
One reference column per relationship; do not copy the same name into several tables when a link and a lookup do it.
Limits you can verify: 500 links added per write; 5,000 links relabelled per change; 30 recalculation passes; 60 seconds per cross-table recalculation and 2 seconds per query (installation settings).
6. Troubleshooting
Symptom | Likely cause | Fix |
|---|---|---|
Cell shows | The formula uses a column that was deleted | Edit the formula and replace the reference |
Cell shows | Division by zero or by a blank (a blank counts as 0) |
|
| Text in arithmetic | Fix the data, or wrap in |
| A date compared to a number or text | Compare date to date; use |
| Misspelt function; saving did not warn | Insert it from Functions |
Result is Text but you wanted a date | Formulas return dates as text | Expected; the value is |
A blank cell is not caught by | A blank is not equal to empty text |
|
Blank due dates read "Overdue" | A blank date sorts before every date | Test |
Old date or day count | A | Use Refresh calculations; ask your administrator about the nightly hour |
Calendar or price function is blank in Preview | The editor cannot read that data | Save and look at the stored cell |
Lookup is empty | Link empty, target deleted, backlink filter excludes all, or the header shows "the value is frozen" (source column deleted) | Check the link; click the header warning to reconfigure |
Lookup shows one of several values | A lookup takes the first | Use a Rollup with Concatenate |
Rollup shows 0 | A wrong filter drops every row, values are text, or no rows are linked | Test the filter on its own; use Show the rows behind this value |
Rollup or lookup does not update | Disable automatic recalculation is on; or Recomputing… is still running | Refresh it; wait for the chip to clear |
Cross-table formula never updates | No | Add one, or switch to a Rollup |
Save of a cross-table formula is refused | Your installation enforces the check | Add the condition, or ask your administrator |
"do not resolve to rows in the target table" | Linking a row that is deleted or in another table | Pick a live row; check Configure reference |
"would be stored without a display label" | The target row's display field is blank | Fill it, or change the Display field |
Link shows an old name | More than 5,000 links, or the scan is behind | Validate references… → Update stale labels |
Import leaves the link empty | No row matched the value | Use Match by field… and check the field |
Values never settle | Two columns read each other | Remove the circular reference |