Rollup vs lookup
Use a rollup to summarize many related entries into one number — how many orders a customer has, what they are worth in total. Use a lookup to pull one value across a single reference — showing the customer's city on the order. Rollup counts the many; lookup copies from the one. Both are computed and read-only.
Requirements: a role that can edit a collection's fields, and an existing reference between the two collections.
Both a rollup and a lookup show information that lives on another entry, and neither is typed in by hand — Sonta computes them and keeps them current. The difference is direction and shape. A rollup looks at the many entries pointing back at this one and reduces them to a single number. A lookup follows one reference outward and brings back one value as it is. Everything below follows from that.
Rollup — summarize the many
Reach for a rollup when the answer is a number that summarizes related records. On a customer: how many orders they have placed, the total value of those orders, their average order size, the largest one. On a company: how many open deals it has, and what they add up to.
The shape to recognize is one-to-many pointing at this entry — many orders reference one customer — and you want those many collapsed into one figure. A rollup can count the related entries, or add up, average, or take the smallest or largest value of one numeric field across them. The result is always a number, which is exactly what makes it useful for sorting, filtering, and reporting. A worked example is in reviews and rating rollups on a product.
Lookup — borrow one value from the one
Reach for a lookup when the answer already exists on a single related entry, and you simply want it visible here. On an order: the customer's city, so you can see the destination without opening the customer. On a work order: the customer's phone number, right where the technician is looking.
The shape here is the opposite direction — this entry points out at one other entry, and you want one of its fields shown alongside your own. A lookup does not summarize or transform; it brings the value across as it is, keeping its type: an option stays a set of options, a date stays a date, so it behaves the way that field behaves everywhere else.
What each costs
- Both are read-only. A rollup or lookup is computed, never typed. You cannot edit one on an entry, and an import cannot fill it. To change what it shows, change the underlying data — that is the point: there is one place to correct, not two copies to reconcile.
- Both need a reference to travel along. Neither works without a relationship between the collections — a rollup needs the other collection pointing back at this one; a lookup needs this entry pointing out. If the value you want is not connected by a reference, you need the reference first.
- A rollup only produces numbers. To total or average, it needs a numeric field to work on. "Show me the name of their latest order" is not a rollup — rollups count and total, they do not pick out a record.
- A lookup follows one link, not many. It reads across a single reference. If an entry points at several, a lookup is not the tool; that direction is a rollup's job.
- Neither chains onto another computed field. Build them on real stored data rather than stacking one derived value on top of another.
If a rollup shows a number you did not expect, my rollup is not recalculating walks through the usual causes.
Rule of thumb
Ask what you want to see. A number that summarizes several related records — rollup. One value borrowed from a single related record — lookup. If the value is not related by a reference at all, add the reference first — both of these travel along one.