Splitting a repetitive Airtable table into linked tables: how the matching works and where it goes wrong

Why one table becomes two

A table that repeats a customer's name and contact details on every row has no single place to correct them. Names drift into several spellings, summaries count variants as different customers, and an edit has to be repeated on every row. Splitting the table gives each customer one record in a parent table, and each row in the original table, now the child, links to its parent. Fixes then happen once, and values can be shown in the child through lookup fields. The risk is in the conversion, because the way Airtable creates the links has precise rules.

How Airtable matches text to records

Airtable's article says that when you paste into a linked record field, or convert an existing field to one, it matches each value against the linked table's primary field, and only that field. Matching is exact: spacing, capitalisation and punctuation all count. A near-match does not link. Depending on the primary field, it may create a blank or a new record. Values with no match become new records in the linked table, named after the unmatched text. So a column containing the three spellings 'Acme Ltd', 'ACME Ltd' and 'Acme Ltd.' produces three parents unless the variants are made identical first, or linked to an existing parent.

• Decide the key list before converting anything.
• Normalise variants on a duplicate base, using rules you write down.
• Create the parent records from the approved list, then link to them.

Commas, formulas and synced tables

Three traps are documented. Commas separate values: for a conversion, the article says a cell is split into several links at each comma unless the value is wrapped in double quotation marks, so a name such as Smith, Jones, and Co. becomes three links. If the linked table's primary field is a formula or other computed field, paste cannot create new records and unmatched values are silently dropped; the workaround Airtable describes is to change the primary field to an editable type, such as single line text, for the duration. And new linked records cannot be created in a read-only synced table. Check each of these before you start, not after the counts disagree.

Do it in this order

Work on a duplicate of the base. Record the starting counts: records in the table and distinct values of the key. Build and approve the key list, with a decision for every ambiguous variant. Create the parent table with an editable primary field. Link the children to their parents using the approved list, then add lookup fields that show the old columns' values. Reconcile: the child count must equal the original count at the time you took the snapshot, the parent count must equal the key list, and no child may have an empty or double link apart from listed exceptions. Compare a sample of records you choose. Keep the old columns until everything that used them has been repaired.

• Child records after equals records before.
• Parent records equals the approved key list.
• Children with no parent: none, or the signed-off exceptions.
• A sample of twenty records matches the old text, or differs only as the normalisation rule says.

Deleting fields is not undoing them

It is tempting to delete the old text columns once the lookups look right. Airtable's page on linking says deleting a linked record field does not delete records, but the field on the other side becomes a text field holding the former record names, and lookups, rollups, filters, sorts, automations and interface elements that depended on the field stop working. A field deleted by mistake can be restored from the base's trash, which restores the links. Keep the old columns, and hide them if you must, until every dependency has been listed and repaired.

What this guide does not cover

This guide does not clean free-text data beyond the rules you decide, move data between platforms or rebuild interface pages. The paid outcome splits one table into one parent and one child on a duplicate base, with a key list, a reconciliation sheet and a list of dependent views and automations. A duplicate is a separate base, so it comes with a cut-over note: what changes when your team moves onto it and a check for records added, edited or deleted in the original since the snapshot. You decide when to move.