Relation, Mirror, Rollup and Formula: Which One Do You Need?

These four field types are where most people's understanding of a work platform stalls. They sound similar, they appear next to each other in the field picker, and choosing wrong produces something that almost works — which is harder to diagnose than something that plainly does not.

Here is the whole distinction in four sentences:

• A relation links two sheets together.
• A mirror shows a field from the other side of that link.
• A rollup computes across a collection of rows.
• A formula calculates within a single row.

The rest of this article is one example carried through all four, because that is the only way this ever actually lands.

What problem do connected fields solve?

Duplication. Without them, the same fact gets typed into several places and the copies disagree within a month.

Say you run an agency. You have a Clients sheet and a Projects sheet. Every project belongs to a client, and you want to see the client's name, their account manager and their tier while you are looking at a project.

The tempting move is to add those three columns to Projects and type them in. It works on day one. By month three the client has changed account manager, forty project rows still name the old one, and nobody can tell which sheet is right. That is not a discipline failure — it is what copying data always does.

Connected fields solve it by keeping one copy and pointing at it. This is the same instinct behind not letting a sheet sprawl into columns it should not own; connected fields are the mechanism that makes the smaller, cleaner sheet possible.

What is a relation?

A relation is a field whose value is another item, in another sheet. It is the link itself.

On the Projects sheet you add a relation field called Client, pointing at the Clients sheet. Now each project row holds a genuine reference to a client row — not the client's name as text, the actual row. Rename the client and every project referencing it updates, because there was only ever one copy of the name.

Two things to know:

• A relation can hold one item or many. A project has one client; a client has many projects. Which side is which shapes what you can do next.
• Two-way is a setting, not a default. A relation can be configured to create the matching field on the other sheet, so that from a client you can also see all their projects. This is usually what you want — but it is a choice made when you create the field, so check it rather than assuming the reverse view exists.

If you take one thing away: a relation stores a link, not a value. That is why you usually want a mirror or a rollup alongside it.

What is a mirror, and when do you want one?

A mirror pulls a single field from the item on the other end of a relation and displays it here.

You have a Client relation on Projects. You want to see the client's account manager in the projects table without opening each client. Add a mirror field: through the Client relation, show Account Manager. Now it appears on every project row.

Mirrors come in two flavours, and the difference matters more than the name suggests:

• Dynamic — a live window onto the original. Change the value on the client and it changes here. Nothing can drift, because nothing was copied.
• Static — captures the value as it was. Useful when you deliberately want the value at the time, such as the rate that applied when a project was signed. But a static mirror is a copy, with all a copy's usual caveats.

Dynamic is what people mean when they say mirror, and it is the right default. Reach for static only when the historical value is the point.

Use a mirror whenever you catch yourself about to retype something that already exists on a related item. The test is simple — is this fact really about this row, or about something this row points at? If it is about the other thing, mirror it.

What is a rollup?

A rollup computes one value — a count, sum, minimum, maximum, average, distinct count, first or last — across a collection of rows.

The part people get wrong is assuming a rollup always reaches through a relation. It is the most common case, but not the only one. A rollup can aggregate over:

• Related items — through a relation. From the Clients side: how many projects does this client have, what is their total contract value, when is their earliest deadline.
• Sub-items — the children of this row, with no relation involved at all. How many sub-tasks does this task have, how many are done, what is the total estimate.
• The sheet — an aggregate across rows grouped by some attribute, so each row can show a figure for its group.
• The item itself — an aggregate over the row's own values.

The sub-item case is worth knowing about, because a lot of the work people try to do with clever formulas is really a sub-item rollup: a parent task that shows the total effort of its children needs no relation, just a rollup over its sub-items.

This is what turns a list into a report. Without rollups, answering "which clients are we most exposed to" means opening each client and adding up their projects by hand. With them, it is a column you can sort by — and group a view on.

Rollup or formula — which do you need?

This is the most common wrong turn, and the rule is one question: does the answer involve rows other than this one?

A formula calculates within a single row, from that row's own fields. Multiply hours by rate to get a project's cost. Subtract start from end to get its length. Everything the formula needs is already on the row.

A rollup reaches across a collection — related items, sub-items, or the sheet — and reduces it to one number.

So: cost per project is a formula. Total cost across a client's projects is a rollup through a relation. Total effort of a task's sub-tasks is a rollup over sub-items. People reach for a formula because formulas feel more powerful and general — but a formula cannot see other rows at all, and no amount of clever expression will change that.

The two combine well, which is where it gets useful: a formula on each project computes its cost, and a rollup on the client sums those costs. Neither could do it alone.

What breaks if you skip the relation and duplicate the column instead?

Four things, in a reliable order.

1. The copies diverge. Something changes on one side and not the other. Now there are two answers and no way to tell which is right.
2. Reporting quietly lies. Group by a text client name and you get "Acme", "Acme Ltd" and "acme" as three clients. Every total is wrong by an amount nobody can quantify.
3. You cannot go backwards. From a client you cannot see their projects, because nothing links them — only a string that happens to match, sometimes.
4. Automations misfire. A workflow conditioned on a text field breaks on a trailing space. One conditioned on a relation cannot, because a link either points at the row or does not.

The warning sign to watch for is numbered columns. Contact 1, Contact 2, Contact 3 is always a relation trying to happen, and it always fails the same way: someone has a fourth.

How do you add a relation to a sheet that already sprawled?

Most sheets that need this have already been running for a while with duplicated text columns. The migration is manageable if you do it in this order.

1. Create the sheet the values should live in, if it does not exist — Clients, Vendors, Products.
2. Get the distinct values out of the text column and create one row per real thing. This is where you discover "Acme" and "Acme Ltd" are the same client; merge as you go.
3. Add the relation field to the original sheet, alongside the old text column. Do not delete anything yet. Decide at this point whether you want the two-way version, since the reverse field is what makes the other sheet useful.
4. Fill in the relation row by row. Tedious but safe, and with the old column still visible you can see what each row should point at.
5. Replace the other duplicated columns with dynamic mirrors through the new relation.
6. Hide the old text columns for a fortnight, then delete them once nothing has broken.

Step 3 is the one people skip, and it is what makes the whole thing reversible. Keeping the old column until the new one is fully populated means a half-finished migration is still a working sheet.

Start with the relation

Everything except the sub-item rollup depends on the link existing. Find the place in your workspace where you retype the same value on multiple rows, create the sheet it should live in, and connect them. The mirrors and rollups become obvious once the relation is there — usually within about a minute of looking at it.

For how relations sit alongside the rest of the model, see the building blocks of your workspace.

Start free and replace one duplicated column with a real link.