Formulas, rollups & summaries

Not everything worth showing is something someone types in. A deal’s margin follows from its amount and cost; a company’s open pipeline follows from its deals; a grid’s total follows from every row in it. Tessule computes all of these for you — per-row formula, lookup, and rollup columns, and whole-table summary rows — so a derived number is never a copy someone has to keep up to date.

If you only remember one thing: derived values are lenses over the data, not data. They are always read-only, they are computed from current values (there is no cache to go stale), and they respect permissions — a computed value is blanked rather than let it reveal a field or rows the viewer is not allowed to see.

Who can do what

See computed columns and summary rows, create personal ones, and validate formulas — derivedViews:read, held by every built-in role including Reader. A personal computed column or summary is visible only to its author.
Create, edit, and delete workspace-shared (or group-shared) computed columns and summary rows — derivedViews:manage, held by Member and Admin by default. Deleting your own personal one needs only derivedViews:read.
Computed fields in the table schema (formula/lookup/rollup/autonumber field types) are schema changes: they ride fields:manage like any other field — see Tables & fields.

Two homes for a computed column

The same three kinds of computation — formula, lookup, rollup — can live in two places, and the difference matters:

	Schema field (Table Administration → schema editor)	Computed column (Sheets grid → add computed column)
What it is	Part of the table’s schema, like any field	A view over the table, separate from the schema
Who sees it	Everyone (subject to field visibility)	Its scope: the whole workspace, a group, or just you
Permission to create	`fields:manage`	`derivedViews:manage` (shared) / `derivedViews:read` (personal)
Formula storage	A real database column, recomputed by the database itself on every write to the record — this is what makes it usable as a computed lifecycle stage	Computed at read time, nothing stored
What a formula can read	Stored columns only	Stored columns, plus the table’s lookups, rollups and formulas, and other computed columns
Lookup/rollup storage	Computed at read time	Computed at read time
Changing a formula	Edit the field (the column is rebuilt)	Delete and recreate — computed columns are deliberately disposable

Use a schema field when the value is part of the table’s contract — everyone should see it, a lifecycle should key off it. Use a computed column when it is an analytical lens — especially one only you or your team cares about. Computed columns appear in the sheet grid and everywhere records are read, marked computed and read-only.

A formula reads sideways (the record's own fields); a rollup reads upstream (every record referencing this one). Both produce read-only values.

The formula language

One expression language powers formula fields, computed columns, rollup filters, and summary expressions. It is Excel-familiar and deliberately small: every construct is deterministic and type-checked before anything is saved.

Syntax

Optional leading = — =IF([Amount] > 0, "pos", "neg") and if(amount > 0, 'pos', 'neg') are the same formula.
Field references — a bare internal field name (check_in), or the display name in brackets ([Check In], case-insensitive, spaces allowed). Real field names win over display labels; an ambiguous label (two fields with the same display name) is refused rather than guessed.
Strings — 'single' or "double" quotes; double the quote to escape one.
Numbers, true, false, null — as literals.
Function names are case-insensitive — IF, if, and If all work.
Limits — an expression can be up to 2,000 characters, and complexity is bounded, so a runaway formula is refused rather than accepted slowly.

Operators

Operators	Meaning
`+ - * / %`	Arithmetic on numbers. `/` always yields a decimal (7 / 2 = 3.5). Dates and times use functions instead — see below.
`&` or `\|\|`	Text concatenation. Text only — there are no implicit casts; wrap numbers with `to_text()`.
`= !=` (or `<>`) `< <= > >=`	Comparison, producing a boolean. Both sides must be the same type.
`and or not`	Boolean logic — also available as functions: `AND(a, b, …)`, `OR(a, b, …)`, `NOT(x)`.

Functions

Logic and nulls

Function	Notes
`if(condition, then, else)`	Both branches must be the same type
`coalesce(a, b, …)`	First non-empty value (2–5 arguments)
`is_null(x)` (alias `isblank`)	True when the value is empty
`nullif(a, b)`	Empty when a = b, else a — the idiomatic divide-by-zero guard: `amount / nullif(quantity, 0)`
`least(…)` / `greatest(…)`	Smallest/largest of the arguments (2–8, one shared type)

Math

Function	Notes
`abs(x)`, `sign(x)`
`round(x)`, `round(x, digits)`
`floor(x)`, `ceil(x)` (alias `ceiling`), `trunc(x[, digits])`
`mod(a, b)`	Remainder
`power(base, exp)` (alias `pow`), `sqrt(x)`
`exp(x)`, `ln(x)`, `log(x)`, `log(base, x)`	`log(x)` is base 10

Text

Function	Notes
`length(t)` (alias `len`)
`lower(t)`, `upper(t)`, `trim(t)`, `initcap(t)` (alias `proper`)
`concat(a, b, …)` (alias `concatenate`)	2–8 text arguments; same as `&`
`substring(t, start[, length])` (aliases `substr`, `mid`)	1-based start
`replace(t, from, to)`
`left(t, n)`, `right(t, n)`, `repeat(t, n)`
`lpad(t, len[, fill])`, `rpad(t, len[, fill])`
`strpos(haystack, needle)` (aliases `find`, `search`)	1-based position, 0 if absent
`contains(haystack, needle)`, `starts_with(t, prefix)`	Booleans
`to_text(x)`	Converts numbers, booleans, dates, and times to text. Not datetimes — their text form depends on a timezone, which a deterministic formula cannot have

Dates and times — temporal arithmetic uses functions, not +/-:

Function	Notes
`days_between(later, earlier)` (alias `days`)	Whole days between two dates; negative if reversed
`add_days(date, n)`
`hours_between(later, earlier)`, `minutes_between(…)`, `seconds_between(…)`	Between two datetimes or two times; fractional (2.5 hours) — wrap in `round`/`ceil` to bucket
`add_hours(value, n)`, `add_minutes(value, n)`	On a datetime or a time (times wrap past midnight)

There is deliberately no now() or today(): a formula’s value may only depend on the record’s own fields, so it never silently changes as time passes. “Days until deadline” style values belong in queries and dashboards, which are evaluated at view time.

What a formula can reference

Text-like fields (text, textarea, richtext, email, phone, url, picklist), numbers (integer, number, currency, percent), booleans, and date/datetime/ time fields — the field types that carry a single comparable value. It cannot reference reference fields (use a lookup), multipicklists, attachments, compound fields or autonumbers.

Whether a formula may read other computed values depends on where it lives. A schema formula field is stored by the database, so it cannot read lookups, rollups or other formulas — chaining is refused with a suggestion to inline the other expression, or to use a computed column. A computed column in the sheet is evaluated as records are read, so it may also reference the table’s lookups, rollups and formula fields, and the other computed columns: three per-source rollups plus [Stays] + [Activities] + [Transport] gives a cross-table actual-spend total, and [Budget] - [Actual total] on top of it gives the variance. Computed columns may nest at most four formulas deep, and a computed column another computed formula reads cannot be deleted until that formula is.

Live validation

Editors validate as you type via POST /tables/{tableId}/formula/validate, which type-checks the expression against the table’s fields without saving anything and returns either the inferred result type and the fields it references, or a teaching error — what is wrong, where, and a suggested fix. The same check runs again on save, so a formula that validates is a formula that saves.

Lookups and rollups: reading across tables

Both ride a reference field and are computed at read time — never stored, so they are always current and cost nothing to change.

Lookup — follows a reference forward and projects one field of the target record: a deal’s company_industry via its company reference. You pick the reference field (via) and the target field to project.
Rollup — aggregates backward over every record whose reference points at this one: a company’s open_pipeline as sum of amount over deals referencing it. You pick the source (child) table, its reference field, an aggregate — count, sum, min, max, or avg — a value field (except for count), and optionally a filter: a boolean expression in the formula grammar over the child table’s fields, e.g. status = 'open'.

A table can carry at most 10 lookup/rollup columns in total (they cost a per-row subquery each); formulas are not capped this way.

Computed columns in the sheet

In Sheets, anyone can add a computed column without touching the schema: the grid toolbar’s Add computed column button (the ƒ icon, next to Add summary row) opens a dialog with a Formula / Lookup / Rollup toggle, the shared configuration editor, and a scope choice:

Everyone in this workspace — requires derivedViews:manage.
Only me — a personal column, requires only derivedViews:read.

(A third scope, sharing with one of your groups, exists in the API — POST /tables/{tableId}/derived-fields with a group owner — but has no picker in the dialog yet.)

Computed columns live beside the schema, not in it: the column name must not collide with a real field, the values are read-only, and deleting one (DELETE /derived-fields/{derivedFieldId}) affects nothing but the column itself. They are deliberately disposable — there is no edit; delete and recreate to change a definition.

If a field a computed column depends on is later deleted or re-typed, the column degrades to blank values rather than blocking the schema change or breaking the grid. A permanently blank computed column usually means its definition no longer matches the schema — delete and recreate it. (Schema- level lookup/rollup fields are stricter: they block the deletion of fields they read.)

Summary rows: totals pinned under the grid

Summary rows are pinned rows at the bottom of the Sheets grid showing whole-table calculations — a count of rows, the sum of a column, or a conditional total. Each summary row has a label and one cell per column; each cell is either:

A quick pick — click a summary cell and choose from the aggregates offered for that column’s type: Sum and Average for numeric columns, Min/Max for numeric and date/time columns, and Count (rows) / Filled / Empty for any column.
An expression — an aggregate call in the formula grammar: SUM, COUNT, AVG, MIN, MAX, plus the conditional forms SUMIF(condition, value), COUNTIF(condition), and AVGIF(condition, value) — e.g. SUMIF([status] = 'open', [amount]). Expression cells declare a result type, and every cell is validated against the table’s fields when saved.

Summary rows have the same three ownership scopes as computed columns, with the same permissions (derivedViews:manage for shared, derivedViews:read for personal), managed via GET /tables/{tableId}/summaries, POST /tables/{tableId}/summaries, PUT /summaries/{summaryId}, and DELETE /summaries/{summaryId}. A summary’s scope is fixed at creation — re-scope by deleting and recreating.

When summaries recompute

Values are never stored. POST /tables/{tableId}/summaries/compute aggregates each visible summary’s cells over the table at that moment, and the grid calls it automatically — on load and again (briefly debounced) whenever records are added, edited, or deleted — so totals track the data live. If one compute fails, the footer keeps the last known values rather than going blank.

Derived values and permissions

Derived data is where hidden information could leak — a total over rows you cannot open, a formula echoing a private column. Tessule’s rule is uniform: a derived value is blanked or narrowed rather than reveal anything the viewer cannot see directly.

Derived thing	What the viewer gets
Computed column (formula)	Blank if the formula references any field the viewer cannot read.
Lookup / rollup (either home)	Blank if the projected/aggregated/filtered fields are hidden from the viewer, or if the viewer has only partial row visibility of the other table — a rollup is computed over all referencing rows or not shown at all, so a count can never disclose rows the viewer can’t see.
Summary row	Computed only over the rows the viewer can see (their record scope is applied), and a cell that reads a hidden field is omitted entirely. Two people can legitimately see different totals in the same footer.
Formula schema field (stored)	It is a real column with its own field visibility: its value shows to anyone who can see the column, even if an input field is private. Mark the formula field private too when its inputs are sensitive.

Admins see everything, as everywhere else — see Authorization.

Staleness and recompute semantics

There is no background refresh job and no cache to invalidate — each shape has one recompute rule:

Formula schema fields are stored columns maintained by the database itself: the value is recalculated in the same write that changes the record, so it can never disagree with the record.
Lookups, rollups, and computed columns are computed at read time, every time. Change a child record and the parent’s rollup is correct on the very next read.
Summary rows are computed on request; the grid re-requests them as the row set changes.

The practical consequence: a derived value can never be stale, but the read-time shapes do their work when you read them — which is why cross-row columns are capped per table.

Computed lifecycle stages

A lifecycle stage is usually a picklist someone sets — but it can also be worked out: “a job with a completion date is finished”. To model that, create a formula schema field returning text and designate it as the table’s stage via PUT /tables/{tableId}/fields/{fieldId}/stage-config with a declared value list. Boards, stage history, and time-in-stage all work; what a computed stage deliberately does not have is enforcement — no transition matrix, no per-stage required fields — because nobody writes to it. The full treatment is in Lifecycles.

This is one place the two homes differ sharply: only a stored column can be a stage (there must be a physical value to record changes of and group boards by), so a virtual computed column cannot. The API reserves PUT /tables/{tableId}/derived-fields/{derivedFieldId}/stage-config for stored computed columns, but promoting a computed column to stored is not yet available — today, a computed stage means a formula schema field.

FAQ

Q: Why is my computed column blank?

In likely order: (1) a field it references is private and you lack a grant — ask an admin; (2) for a lookup/rollup, the other table is visibility-restricted for you; (3) the definition no longer matches the schema (a source field was deleted or re-typed) — delete and recreate the column; (4) for a lookup, this row’s reference field is simply empty.

Q: Can a formula reference another formula?

No — in either home. Chained computation is refused at validation time with a suggestion to inline the other expression. Rollups over a child table’s formula schema fields do work, because those are real stored columns.

Q: Why does my summary total differ from my colleague’s?

Summaries respect each viewer’s record visibility. On a private table where you see only your own records, your Sum is the sum of your rows. An admin’s footer shows the whole table.

Q: Why can’t I write [due_date] - [start_date]?

Temporal arithmetic is function-based: days_between([due_date], [start_date]). The error message tells you which function fits the types involved.

Q: Can I use a computed value in a SQL query?

Only the stored kind. A formula schema field is a real column, so SQL queries and views can read it like any other. Read-time values — lookups, rollups, computed columns — exist only where records are read through the app and API record endpoints; they are not columns a SQL query can select. Selecting one is refused with a message that says which kind it is and the equivalent expression to write instead, derived from the same definition, so the value has one source of truth and SQL restates it rather than keeping a copy — see Computed values.

Q: Who can see a personal computed column or summary?

Only its author (plus nothing is ever hidden from workspace admins in the underlying data — but they do not see your personal columns; scoping is by owner, not permission level).

Open the app