Lookup vs rollup: which one you actually want

Two fields, next to each other in every menu, doing what looks like the same job. The difference is one word.

A lookup shows you a value from somewhere else. A rollup does arithmetic on the values it finds. That's it — the rest of this page is why that distinction keeps mattering, and how to tell which one a column wants.

They both need a link first

Neither field works on its own. Both of them ride on a link between two tables, and the link has to exist before either becomes available.

Say you have Projects and Time entries, and each time entry is linked to one project. That link is what makes the other two possible:

• On a time entry, a lookup can show the project's client — because a time entry has exactly one project.
• On a project, a rollup can total the hours — because a project has many time entries.

That's the whole rule, and it's directional:

One → one, use a lookup. One → many, use a rollup.

If you find yourself wanting a lookup on the "many" side, what you actually want is a rollup. If you find yourself wanting a rollup on the "one" side, you want a lookup — there is nothing to aggregate.

What a lookup is for

A lookup keeps one fact in one place. The client's name lives on the client record, and every invoice, every project and every time entry can show it without storing its own copy.

That matters the day the client changes their name. With a lookup, you edit one record and every row that displays it is already correct. With a copied text column, you're finding and replacing across four tables and missing one.

The rule of thumb: if you were about to type the same value into a second table, you wanted a lookup.

A lookup is also read-only wherever it appears, and that's the point rather than a limitation. The value has an owner; this is a window onto it.

What a rollup is for

A rollup answers a question about a group. Given all the records linked to this one, it gives you a number, a date or a count.

The usual ones:

• Sum — hours on a project, line items on an invoice, nights across a stay.
• Count — how many bookings this guest has made.
• Max / min — the latest delivery date across a purchase order's lines.
• Average — mean order value per customer.

The useful mental test: if you deleted one of the linked records, should this number change? If yes, it's a rollup.

The mistake almost everyone makes once

You build a Projects table with a Total hours number field, and you keep it up to date by hand — or with an automation that recalculates it on save.

It works for a month. Then somebody edits a time entry from a phone, the automation runs on a different trigger than you assumed, and the total is wrong in a way nobody notices until it's on an invoice.

A rollup can't drift, because it isn't stored. It's derived, every time it's read, from the records that exist right now. Deriving beats syncing — and the same argument is why a lookup beats a copied column.

When you genuinely want neither

Two honest exceptions.

A number that must not change later. The price you charged on the day you charged it isn't a rollup of the current price list — it's a fact about a past event. Store it. A derived field would rewrite history the next time the price list changed, which is precisely what you don't want on an invoice.

Arithmetic within a single record. Nights × nightly rate needs no link and no group; it's a formula. Reach for a formula when both inputs are already on the row in front of you.

How this works in Tabla

Link a table, then add the field:

• Lookup — pick the link, then the field on the other side you want shown.
• Rollup — pick the link, then the field, then the operation (sum, count, min, max, average).

Both recalculate as the underlying records change. Both are readable through the API under the same field name they have on screen, and both can be filtered and sorted on like any other column — a rollup is a real value at query time, not a label the screen paints afterwards.

The API reference shows the exact JSON each one returns.