2.3Table relationships (linked records)
The essentials in 30 seconds
- Never duplicate, always link: the linked record links tables — one piece of info exists once and is referenced everywhere.
- Lookup = bring back, rollup = aggregate: one shows a linked value, the other calculates over several linked ones — without a prior linked record, neither works.
- Fix at the single source: one change propagates everywhere — that is data integrity.
Ongoing mini-case — Lina links Dossiers and Bénéficiaires
Lina links each file to its beneficiary with a linked record — no more names recopied across ten rows. She adds a lookup that brings the beneficiary email into the file, then two rollups on the Bénéficiaires side: the file count and the amount total. When an email changes, she fixes it once: everything follows. Her base has become a true data model, ready for formulas (2.4) and the team interface (2.5).
Continuation of thread 2.2: Lina's tables become a relational model — and continue in 2.4 and 2.5.
1. Linked records, the relational heart
This is the heart of « relational »: linking tables with linked records. Instead of copying a beneficiary name into each file, you link each file to the matching Bénéficiaire record. You thus model reality: Dossiers ↔ Bénéficiaires, Orders ↔ Products, Tasks ↔ Projects. Information exists only once (in its table) and is referenced wherever needed — no more inconsistencies and copy-paste.
2. Lookup: bring back — rollup: aggregate
Two essential companions of relationships: the lookup (bringing back a field from the linked table — showing the beneficiary email directly in the file) and the rollup (aggregating linked records — beneficiary amount total, file count, nearest deadline). Lookups and rollups make information « come up » and « calculate » across links. Mnemonic: lookup = « fetch and show » (one value), rollup = « gather and calculate » (several values).
3. A true data model
Mastering linked records + lookups + rollups means having a true data model — the skill separating the advanced user from the beginner. The proving test: change data at its source (the email in Bénéficiaires) and see it update everywhere it is reported. That is data integrity: a single source of truth. Mandatory condition: lookups and rollups require a prior linked record — without a link, nothing to bring back or aggregate.
🔎 Concrete example — 🔎 Concrete example — Lina's Bénéficiaires ↔ Dossiers
One unique piece of info, used everywhere in Lina's base:
Dossiers.Bénéficiaire → Linked record to Bénéficiaires
Dossiers.E-mail → Lookup (from Bénéficiaires)
Bénéficiaires.Nb dossiers → Rollup COUNTA(values)
Bénéficiaires.Total → Rollup SUM(values)Reading: fixing the email once updates it everywhere.
Lesson: never duplicate, always link.
Section vocabulary
- Linked record
- Airtable field linking a record to one (or more) records in another table — that is the relationship.
Simple example: Each of Lina's files points to its beneficiary.
What it is not: the beneficiary name recopied as text in each file.
Reflex: repeated info = create a linked record.
- Lookup
- Field that brings back (displays) a field value from the linked table, without transforming it.
Simple example: The beneficiary email shown in Lina's file.
What it is not: a total calculated over several records (that is a rollup).
Reflex: « fetch and show » = lookup.
- Rollup
- Field that aggregates values of linked records (sum, count, min, max, concatenation).
Simple example: The file amount total for each of Lina's beneficiaries.
What it is not: a calculation inside a single record (that is a formula).
Reflex: « gather and calculate » = rollup.
- Data model
- Overall structure of tables and their relationships, reflecting business reality without duplication.
Simple example: Lina's Bénéficiaires ↔ Dossiers, with lookups and rollups.
What it is not: a catch-all table where everything is repeated.
Reflex: draw the links before building them.
- Data integrity
- Consistency guaranteed by information existing in one place and being referenced via relationships.
Simple example: The email fixed once by Lina, up to date everywhere.
What it is not: three different spellings of the same name in the base.
Reflex: test: fix at the source, check everywhere.
In practice — In practice — Linking tables and aggregating information
- In your base, create a link-type field linking Dossiers to Bénéficiaires (each file points to its beneficiary).
- Add a lookup on Dossiers to show the beneficiary email (brought back from the Bénéficiaires table).
- On the Bénéficiaires table, add a « file count » rollup (COUNTA) and an « amount total » rollup (SUM).
- Check: change the email in Bénéficiaires and see the update everywhere — a single source of truth.
Ready-to-say script — explaining the relationship to a colleague (30 s)
« We no longer copy the name and email into each file: the file is linked to the beneficiary record. If the email changes, we fix it once in the record, and all files follow automatically. One piece of data, one place — the rest is just links that bring back and calculate. »
Say in front of the base, showing the fix propagating — that is the convincing demo.
Copy-paste template — linked record, lookup, rollup trio
1) LINK (Dossiers table): {Bénéficiaire} field → Link to another record → Bénéficiaires table
2) LOOKUP (Dossiers table): {E-mail bénéficiaire} field → Lookup → {Bénéficiaire} record → {E-mail} field
3) ROLLUPS (Bénéficiaires table):
{Nb dossiers} → Rollup → {Dossiers} record → {N° dossier} field → COUNTA(values)
{Total montants} → Rollup → {Dossiers} record → {Montant} field → SUM(values)Lightweight CRM
Quotes linked to Customers: billing address lookup, revenue rollup per customer.
SME Algeria
Lina links Dossiers ↔ Bénéficiaires and tracks dinar amounts by wilaya via a rollup.
Project management
Tasks ↔ Projects: project owner lookup, rollup of completed tasks per project.
Order tracking
Lines ↔ Orders: product unit price lookup, order total rollup.
✓ Best practices
- Link tables rather than recopying information.
- Use a lookup to display, a rollup to calculate.
- Fix each piece of data at its single source.
✗ Common pitfalls
- Putting everything in one big table (repetition).
- Confusing lookup (one value) and rollup (aggregate).
- Wanting a lookup or rollup without a prior linked record.
Key takeaways
- Linked records link tables: information exists once and is referenced everywhere.
- Lookup brings back a field from the linked table (for example the beneficiary email in the file).
- Rollup aggregates linked records (sum, count, min/max).
- Linked records + lookups + rollups = a true data model, consistent and without duplication.
48h challenge — apply tomorrow
Create one real linked record between two of your tables, add one lookup and one rollup (count + sum), then run the single-fix test: change the source and check propagation.
- I can explain linked record, lookup and rollup without hesitating.
- I can create the link → bring back → aggregate trio.
- I proved the single source with the fix test.
Frequently asked questions
Why link tables rather than putting everything in one big table?
Because one big table forces you to repeat information (name, email, address recopied in each row) — a source of errors and inconsistencies: the day data changes, you must fix it everywhere. By linking tables, each piece of data exists once, in its place, and is referenced via linked records. You fix in one place, everything follows. This is the founding principle of relational bases: never duplicate, always link. Result: clean, reliable data that is easy to evolve.
What is the difference between a lookup and a rollup?
The lookup brings back a single value from a linked record, without transforming it: showing the beneficiary email, city, status. The rollup aggregates several linked records into a calculated result: summing all file amounts of a beneficiary, counting files, taking the nearest deadline. Mnemonic: lookup = « fetch and show » (one value), rollup = « gather and calculate » (several values). Both rely on an existing linked record; together, they fully exploit your relationships.
Lina wants to show the beneficiary email in each file without recopying it. Which field?