Formula, lookup or rollup: which one does your math

Every no-code database offers three fields that compute instead of store, and the menu never explains which one you're holding. One question sorts them every time:

Where do the inputs live?

• On the same record → formula.
• On the other end of a link, and you want to see a value → lookup.
• On the other end of a link, and you're asking about the group → rollup.

The rest of this page is those three answers with their scenes, a decision table, and the mistakes each choice exists to prevent.

Same-row math: formula

An order has a Total and a Discount. The amount to collect is arithmetic on the row in front of you:

{Total} * (1 - {Discount} / 100)

Both inputs live on the record, so no link is involved and neither of the other two fields could even ask the question. Formulas are the same-row tool: prices times quantities, days between two of the record's own dates, text assembled from the record's own fields, an IF on its own status.

The tell that you've left formula country: your formula wants a value the record doesn't have. "The customer's email" isn't on the order — it's across a link, and no amount of formula syntax reaches it.

A value from across the link: lookup

Each order links to one customer. To show the customer's email on the order, nothing needs computing — the value exists, it lives on the linked record, and copying it would create a second version to drift. A lookup is a window: the email stays owned by the customer record, every order shows it, one edit updates every window.

Lookups answer "show me the linked record's X". They're read-only where they appear, which is the point — the value has one home.

A question about the group: rollup

A customer links to many orders. "What has Nour spent?" isn't any single order's value — it's a fact about the collection: sum of the linked orders' totals. That's a rollup: pick the link, pick the field, pick the operation. Count of linked plants. Latest delivery date across lines. Average order value.

The mental test from lookup vs rollup, which argues this boundary properly: if deleting one linked record should change the value, it's a rollup. Deleting an order changes Nour's total — rollup. Deleting an order doesn't change her email — lookup.

The decision table

I want	Use
Amount to collect, from this order's total and discount	Formula
Days between this record's two dates	Formula
"GUEST-" + the record's number, as a reference code	Formula
The customer's email, shown on each order	Lookup
The supplier's phone, shown on each product	Lookup
How many plants this order holds	Rollup (count)
What this customer has spent across all orders	Rollup (sum)
The latest delivery date across this order's lines	Rollup (max)
Average order value per customer	Rollup (average)
The price actually charged, on the day it was charged	None — store it

That last row is the standing exception: a fact about a past event must not recompute when today's inputs change. Derived fields tell the present truth; invoices want history. Type it in, deliberately, once.

The classic mistakes

Maintaining a derived value by hand. The Total spent column someone updates when they remember. It's wrong within a month — not because anyone is careless, but because nothing marks it stale when an order changes. Every derived field on this page exists so that number is computed from what's true now, with no sync step to forget.

A lookup pointed at the many side. Wanting "the order total" on a customer who has thirty orders. A lookup through that link shows thirty values, because there are thirty answers — what the question wanted was one number about the thirty, which is a rollup. One → one, look it up; one → many, roll it up.

A formula reaching for a link. Writing {Customer email} in an order formula and finding the field isn't offered. A formula sees one record at a time; crossing the link is the other two fields' whole job.

Deriving what must not change. The invoice priced from a lookup of the current price list reprices history the day the list changes. See the last table row.

How this works in Tabla

The three fields exist under these names, and the shapes above are enforced rather than suggested:

• A formula is computed by the database itself — a real generated column, so it can't drift from its inputs. It reads the record's text, number, date, yes/no and select fields, and works out to a number, text, a date or a yes/no; anything else is refused when you write the expression, not discovered later. A formula that asks the clock — TODAY(), NOW() — has no stored column to go stale: it is worked out fresh on every read, and still filters and sorts like any other field.

Pick by the one question, and the field editor mostly finishes the thought.