Linked records: the one idea that replaces half your formulas

If you learn one concept from this library, make it this one. Almost everything else in relational data is a consequence of it.

The distinction that matters

In a spreadsheet, when you want a project to belong to a client, you type the client's name into a column.

That is a string. A sequence of characters. The spreadsheet has no idea it refers to anything. As far as the file is concerned, Acme Ltd in the projects tab and Acme Ltd in the clients tab are two unrelated pieces of text that happen to look alike.

In a relational workspace, you use a relation field. You pick the client from a list, and what gets stored is a pointer to that client record.

The difference sounds academic. It is not. It changes five things at once.

What changes

1. Typos become structurally impossible

You cannot mistype a link. You choose from records that exist. There is no way to create Acme Ltd , acme ltd and ACME Limited as three separate clients by accident, because you are not typing — you are selecting.

Every filter, group and count built on that field is therefore correct by construction. In a spreadsheet, the same three variants silently split one client into three across every pivot table you own.

2. Renaming happens once

The client rebrands from Acme to Northwind. In a spreadsheet, you find and replace across every tab — and hope you caught them all, including the one with the trailing space.

With linked records, you edit the client record's name. Every project pointing at it now shows Northwind. Not because an update ran, but because there was only ever one place the name lived.

3. The link works from both ends

This is the part people underestimate.

Type a client name into a project row, and you can find projects by client — by filtering. You cannot stand on the client and see their projects, because the client row has no idea it is referenced.

A relation field is bidirectional. Link projects to clients, and the clients table automatically gains the reverse: every client record now shows its projects. You did not build that. It is what a link is.

So "show me everything about this client" becomes opening one record, rather than filtering four tabs.

4. You can borrow values across the link

Once a project points at a real client record, the project can display any of that client's fields — their region, their account manager, their payment terms — without storing a copy.

That is a lookup. The value is read through the link at display time, so it cannot drift. Change the client's region and every project's displayed region changes with it.

Compare with the spreadsheet approach: a VLOOKUP that has to be maintained, breaks when columns move, and returns #N/A or — worse — a confidently wrong value when the key does not match exactly.

5. You can summarise across the link

Standing on the client, you can total their projects' values, count the open ones, or find the date of the most recent. That is a rollup, and it recalculates itself as projects change.

This is the feature that deletes the "summary" tab you have been maintaining by hand.

What it looks like

Concretely, in a projects table:

And in the clients table, automatically:

Nothing in the right-hand table was built twice. The reverse relation and the rollups exist because the link exists.

The rule for when to use one

Use a relation field whenever a column refers to a thing that has its own identity and its own attributes.

Ask: does this thing deserve a record of its own?

• A client has a region, an owner, a payment term, a history. Yes — link it.
• A supplier has contact details and a lead time. Yes — link it.
• A status is one of five words with no attributes of its own. No — a single-select field is right.
• A priority is High/Medium/Low. No — single select.
• A country, if you only ever need its name. No. If you need its tax rate and currency, yes.

The test is not "is it repeated text?" It is "does it have properties I care about?" Repeated text with no properties is a select field. Repeated text with properties is a table.

The mistake almost everyone makes first

Modelling a many-to-many relationship as a single relation field.

If a project has one client, a relation field on the project is correct. But if a candidate can apply to several roles, and each role has many candidates, a relation field alone cannot hold what you need — because the interesting data (which stage, which source, what outcome) belongs to the pairing, not to either side.

That pairing needs its own table. It is called a junction table, and it is the single most useful structure in operational data modelling. It has its own guide: what is a junction table.

Why this removes work rather than adding it

The objection to relational modelling is that it feels like more setup. Choosing a client from a list is marginally slower than typing one.

The payoff is that a whole category of maintenance stops existing:

• No reconciling name variants.
• No find-and-replace across tabs when something is renamed.
• No VLOOKUP chains to repair when a column moves.
• No hand-built summary tab.
• No "which version of this is current?"
• No afternoon spent answering a question that should take ten seconds.

You pay a few seconds per record and get back hours per month. For anything that lives longer than a fortnight, that trade is not close.

Where to go next

• Lookup vs rollup — the two ways to use a link, and which to reach for.
• One-to-many vs many-to-many — picking the right shape.
• What is a junction table — the structure that handles the hard case.