Kuovonne's Guide to Airtable

Explore

Kuovonne's Guide to Airtable

Kuovonne's Guide to Airtable

Metadata fields

Original Record URL (Thumbprint) Field

⁠

⁠ Tip Jar ⁠ Thank Kuovonne for creating this content! ⁠ ⁠ ⁠

⁠

Inspiration

When you duplicate a base, such as restoring from a snapshot, all the records get new IDs. This can make it difficult to find matching records across the original base and the duplicate. If you are restoring from a snapshot, something has already gone wrong.

One method is to a primary field that is always unique. A unique, yet meaningful primary field is always a good idea! However, Airtable does not enforce having unique field values (except for autonumber fields, which are not meaningful), and gives no warning when duplicate records are created. (It is possible to build systems that warn of duplicate records, but they are not foolproof.)

Another method is to use an autonumber field. However, if you create new records in both bases, the new records will have the same autonumber, but be very different records.

An alternative identifying field

I suggest having an {Original Record URL} field that uniquely identifies each record that is set via automation.

A record URL includes the IDs of the base, table, and record. This combination is guaranteed to be unique.

If records are created in both bases after the duplication, you can easily identify which base a record was created in.

If you are looking at a duplicate base, you can find the original base from the URL.

Clicking on the URL will bring you directly to the original record (if you have access to it).

An {Original Record URL} field looks more meaningful than an autonumber field and is less likely to be deleted by a well-meaning creator trying to clean up a base.

Setting the {Original Record Url} field

Because an {Original Record Url} field is not native to Airtable, it must be created as an editable field with an automation to set the value.

If you are worried about editors accidentally deleting the value of this field, you can lock down the permissions for the field so that only automations can edit it.

Method 1 (no code)

Use an automation when a record is created.

Pros and Cons of this method

Pros:

very easy to configure

{Original Record Url} field is filled out immediately

Cons:

requires an automation for every table

uses one automation run for every record

How to implement this method

Create the {Original Record Url} field in the table.

Create an automation that triggers when a record is created.

⁠

Configure the Update record action to update the triggering record with the record url.

⁠

Method 2 (No code - Scheduled Find Records)

Use a scheduled automation with a Find records action.

Pros and Cons of this method

Pros:

If many records are created throughout the day, this method can use fewer automation runs.

Cons:

Requires an automation for ever table.

Can update a maximum of 100 records per automation run.

During the delay between when a record is created and when the automation runs, the {Original Record Url} field will be blank.

How to implement this method

Create the {Original Record Url} field in the table.

Create a scheduled automation with your desired frequency.

⁠

For the Find records action, look for fields where the {Original Record Url} is empty.

⁠

For the repeating group, use the list from the Find records action.

⁠

For the Update record action, use the record ID and record URL from the current list item (not the Find records action).

⁠

Method 3 (Automation Script)

Use a scheduled automation that runs a script to update the {Original Record Url} field.

Pros and Cons of this method

Pros:

Uses only one automation slot, no matter how many tables are in your base.

Can update multiple hundreds of records, especially when chaining multiple instances of the script.

Cons:

During the delay between when a record is created and when the automation runs, the {Original Record Url} field will be blank.

Kuovonne’s script is not free.

How to implement this method

Get the script from

Kuovonne's Ko-Fi Shop

⁠

Create the {Original Record Url} field in each table.

Create a scheduled automation with your desired frequency.

⁠

Create a scripting action, paste the script into the editor, and setup the input variables. (You can leave the processedTableNames input variable blank for the first instance of the script.

⁠

Test the script.

⁠

If you might have more tables or records than can be processed by a single script, chain multiple instances of the script. For example, you might have more than 30 tables in your base, or you might have several hundreds of records that will need updating.

Duplicate the automation and set the processedTableNames input variable to the output from the previous script.

⁠

If you want to chain even more instances of the script, repeat the process, testing the script each time, and then updating the processedTableNames input variable for the new script to the previous instance.

⁠

⁠ ⁠ Thank Kuovonne for creating this content! ⁠ Give Kuovonne a sandwich ⁠ Signup for Coda w/ referral link ⁠ ⁠ ⁠Buy from my Gumroad Store ⁠ ⁠ ⁠ ⁠ ⁠

⁠

Gallery

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.