Rollups, formulas and when to reach for each

Calculated fields come in three kinds, and picking the wrong one is why your numbers drift. Here is the decision rule, plus the patterns worth stealing.

5 min read Relational basics

There are three ways to get a number that you did not type, and they are not interchangeable.

A formula calculates from fields on the same record.
A lookup borrows a value from one linked record.
A rollup summarises values from many linked records.

Choosing wrongly is the single most common cause of "why is this number wrong?"

The decision rule

Ask where the inputs live.

Inputs are…	Use	Example
On this record	Formula	`Quantity × Unit price`
On one record I link to	Lookup	The client's payment terms
On many records linking to me	Rollup	Sum of this order's line items

If you cannot answer "where do the inputs live?", that is the actual problem — the model is unclear, not the formula.

Formulas: same-record maths

Formulas are the closest thing to a spreadsheet cell, with one important difference: a formula field applies to every record in the column, always. There is no such thing as a formula that exists on row 12 but not row 13, and no such thing as a formula that was accidentally overwritten by a pasted value.

That constraint is the feature. Most spreadsheet corruption is a formula column that stopped being a formula somewhere around row 400.

Useful patterns:

Derived amounts. Quantity × Unit price, Subtotal × Tax rate, Hours × Rate.
Durations. Completed − Started for cycle time. Group by month and you have a trend without building anything.
Flags. Due date < today AND status ≠ Done → an "Overdue" boolean you can filter and colour by.
Display names. Combining fields into a readable label — Client — Project (Year) — so linked-record pickers show something meaningful instead of "Untitled".

That last one is underrated. If your relation pickers show ambiguous labels, people pick the wrong record, and no amount of downstream validation saves you.

Lookups: borrowing across one link

Covered in depth in lookup vs rollup, but the key property bears repeating: a lookup is a live read, not a copy.

Change the source and every lookup reflects it instantly. That is exactly what you want for current truth — a client's region, a supplier's lead time, a product's category.

It is exactly what you do not want for history. Which brings us to the mistake worth spelling out.

The freezing rule

This is where real money gets lost.

An order line looks up the product's price. Today it shows £40. Next quarter the product costs £45 — and now every historical order shows £45, because a lookup always shows current truth. Your revenue history has silently rewritten itself.

Rule: if a value is a snapshot of a moment, store it. If it is current truth, link to it.

So on a line item, the price is a stored number, copied from the product at the moment of sale. It looks like duplication and it is deliberate: "the price this sold for on this date" is a genuinely different fact from "the product's current price", and it deserves its own home.

The same applies to exchange rates, tax rates, addresses on issued invoices, and titles on signed contracts. Anything that appears on a document you have already sent someone must be frozen.

A practical way to get both: keep the stored Price as sold, and also a lookup of Current price. A formula comparing them gives you a margin-drift report for free.

Rollups: summarising many

The three parts of every rollup: which link, which field, which operation.

The operations that earn their keep:

Sum — totals. Order value, stock on hand, hours logged.
Count — volume. Open tickets, applications received, tasks remaining.
Min / Max — first and last. First contact date, most recent activity, highest bid. Max(activity date) on a customer is the single most useful field in most CRMs.
Average — mean deal size, average score. Watch for empty values skewing it.
And / Or — "are all line items shipped?", "is any dependency blocked?". Cheap, powerful, underused.

Filtered rollups are the real tool

Unfiltered rollups are rarely what a business watches. Almost every number that matters has a condition attached:

Open pipeline = sum of value where stage is not Won or Lost.
Overdue invoices = count where due date < today and paid is false.
Active headcount = count where leave date is empty.
Stock on hand = sum where type is Receipt − sum where type is Issue.

If your tool supports conditions on rollups, this is where the hand-maintained summary tab finally dies. If it does not, the workaround is a formula field on the child that zeroes out the rows you do not want, then a plain Sum over it — slightly clumsy, works everywhere.

Chaining, and when to stop

Rollups can feed rollups. Line items roll into orders; orders roll into a customer's lifetime value. This is legitimate and often necessary.

Two cautions:

Debug one hop at a time. When a chained number looks wrong, do not stare at the final field. Check the deepest level first and walk outward. The error is almost always at hop one and merely visible at hop three.

Watch the empty case. An average over zero records, a sum over an empty set, a max over nothing — each returns something (0, blank, or an error depending on the tool), and that value then propagates. Decide deliberately whether "no line items" should mean zero or blank, because the two behave very differently in a chart.

A complete worked example

An order system with Products, Orders, Line items.

On a line item:

Product — relation.
Product name — lookup, for readability.
Unit price — stored number, copied at sale time. Not a lookup.
Quantity — typed.
Line total — formula: Quantity × Unit price.

On an order:

Items — count rollup of line items.
Subtotal — sum rollup of Line total.
Tax — formula: Subtotal × 0.2.
Total — formula: Subtotal + Tax.
Fully shipped — And rollup of line items' Shipped.

On a product:

Units sold — sum rollup of line items' Quantity.
Revenue — sum rollup of Line total.
Last ordered — max rollup of the order date.
Current price — a plain field, edited freely, because history is already frozen on the line items.

Every number maintains itself, history is accurate, and nothing is typed twice. Full build in orders and line items.

The four rules

Inputs on this record → formula. On one linked record → lookup. On many → rollup.
Snapshots get stored. Current truth gets linked. This one prevents financial errors.
Filter your rollups. The unfiltered version is rarely the number anyone wants.
Debug chains from the deepest hop outward.

Get these right and calculated fields stop being a source of doubt and start being the reason you trust the numbers.