Next slot: Tomorrow

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 the demo base

  1. Open demo base
  2. Duplicate to your workspace
  3. 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

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.
✅ 🔲 
IF(
  AND(
    {Toggle copy fields} = 0,
    OR(
      {= Field 1} != {Field 1},
      {= Field 2} != {Field 2}
    )
  ),
  "Yes"
)

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.

Overview of the automation trigger and actions

Add 3 × Update record actions, each of which uses the triggering record ID as the record to update.

Overview of the automation trigger and actions

Action 1:

Toggle the Toggle copy fields checkbox

Overview of the automation trigger and actions

Action 2:

Copy all fields values (use Dynamic) from the source field to the destination field

Overview of the automation trigger and actions

Action 3:

Untoggle the Toggle copy fields checkbox

Overview of the automation trigger and actions

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?

  1. The primary field in your junction table must be a single-line text field
  2. You should not allow any method of manually creating new records in the junction table unless you have some method of preventing/merging duplicates
  3. 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.