GUIDES / Airtable
Automated junction tables and copying fields
Russell Bishop
Last updated 29th April 2024
Dynamically populate select fields
Use formula outputs as values in select fields.
= Lead status
IF(
{Probability} >= .7,
"Hot",
"Cold"
}
Lead status
Hot ↓
Generate junction tables
Automatically create junction tables and link records to continuously summarise your data.
Sales
| Date | Amount | = Month | Month |
|---|---|---|---|
| Jan 14th 2024 | $500 | 2024-01 | 2024-01 |
| Jan 29th 2024 | $2,500 | 2024-01 | 2024-01 |
| Feb 20th 2024 | $1,500 | 2024-02 | 2024-02 |
| ↓ |
Months
| Month | Total sales |
|---|---|
| 2024-01 | $3,000 |
| 2024-02 | $1,500 |
Playground
- Open demo base
- Duplicate to your workspace
- Turn on automations
Now you can go absolutely hay-wire on the three editable columns. Try to make changes faster than the automation can handle – we dare you!
Video demonstration of a table full of records that are continually updating fields and automatically linking to junction tables
Configuration
Add fields
You'll need to add 2 × fields to your table.
| Toggle copy fields | 🔖 Copy fields? |
|---|---|
| Checkbox | Formula |
| Prevents the record staying in the "need to update" state before the automation has finished running. | Resolves whether this record needs to trigger the copying automation or not. |
| ✅ 🔲 |
|
Add a view
Add a grid view to the table called 🔖 Copy fields
Filter the view to show only records where 🔖 Copy fields? = "Yes"
Add an Automation
Add an automation to the base, set the Trigger to be when record enters view using the 🔖 Copy fields view.
Add 3 × Update record actions, each of which uses the triggering record ID as the record to update.
Action 1:
Toggle the Toggle copy fields checkbox
Action 2:
Copy all fields values (use Dynamic) from the source field to the destination field
Action 3:
Untoggle the Toggle copy fields checkbox
How and why it works
Why is there a checkbox?
The problem with older solutions to this problem is when the record tries to trigger once more while the automation is already running.
When the first automation run pastes field values, it does so with old data, and when the process has finished it has no way of triggering again until a person realises the mistake and manually re-runs.
The checkbox here acts as a way of untriggering the record before it makes any changes. Then, when the automation finishes it allows itself to retrigger if there are any new changes.
Where this really helps is when some of your fields are compounding, or dependent. For example, your first copy links to a record, and returns a lookup from the field it is now connected to. If we need to use that lookup value in another field, we have to run the automation twice.
This method will let the automation run for as many times as it requires until all fields have been copied.
How should I configure the junction tables?
- The
primary fieldin your junction table must be a single-line text field - You should not allow any method of manually creating new records in the junction table unless you have some method of preventing/merging duplicates
- The junction table should in most cases run its own copy fields process in order to 'save' the value(s) by which it is created (see demo base).
How do you prevent duplicate records in the junction tables?
Airtable does all of this for you – so you won't ever need to worry about deduping your data.
Airtable structures their linked records in such a way that when you attempt to link to a record by name (as we do when we paste text into a linked record field), it will perform an upsert. If a record with that name already exists, it will link to it. If it doesn't, it will create a new one with that name and then link to it.
You've probably done a check like this yourself before using Find records and a conditional action – but this is too slow to be trusted, and will lead to duplicates that cause misinformation.
