I just made an amazing discovery that is opening a world of possibilities with Airtable for me. Up until now, one of many ways that Airtable was and is different from Spreadsheet application is in the inability to reference specific fields (e.g. variables) and trigger changes across all records in multiple ways. Maybe this is nothing new and you all have long consider it normal, but I have to admin it is new discovery for me.
Here is the requirement:
I want to have a variable, which when I change, that variable will 1) replicate across all (***or only select) records in the table and 2) will modify values of many other fields in additional target fields in all records in the table.
Normally this is easy in Excel but in Airtable it’s not as straightforward. The way I solved it is via:
-
two additional tables (Table 1 and Table 2)
-
two additional automations
-
In the MAIN TABLE of my base I added a new field called TRIGGER
-
Created new table in the same base called TABLE 1
-
In TABLE 1 setup as follows: column called TRIGGER and column called VARIABLE
-
Crated new table in the same base called TABLE 2
-
This table contains values of the variable I am going to use. For example, it has 2 or more columns:
Column called VARIABLE
Column called VALUE 1
Column called VALUE 2…
Column VARIABLE:
Variable A
Variable B
Variable C …
Column VALUE 1
red
reen
blue
Column VALUE 2
2019
2020
2022
-
Back in TABLE, I add only single record. In the field TRIGGER I type “Link to Trigger”.
-
Still in TABLE 1, for the field VARIABLE, I will link it with the field VARIABLE in the TABLE 2. ( I don’t need any other Lookups) .
(As you will later see, I could have created a Single Select field here with the names of the options exactly matching the field names of the VARIABLE in Table 2, but in my case it would mean extra work maintaining the single select field)
-
Back to MAIN TABLE, now I link the column TRIGGER in this table with the column TRIGGER in the TABLE 1
-
In MAIN TABLE I add new Lookup field from TABLE 1 called VARIABLE
-
In MAIN TABLE I added a new field called VARIABLECOPY. Leave this field empty for now. I’ll explain further below.
-
In MAIN TABLE I added a new field called VARIABLESELECT.
-
In MAIN TABLE, link the field VARIABLESELECT with the field VARIABLE in the TABLE 2.
-
In MAIN TABLE I added fields VALUE 1 and VALUE 2. These will be Lookup fields for VALUE 1 and VALUE 2 from TABLE 2. I can then use this variables as input in many other operations in the table.
-
In MAIN TABLE, I added the 1st AUTOMATION. The trigger for the automation is “when a record is created” in MAIN TABLE and action is “update record” to fill in the field TRIGGER in MAIN TABLE with the text “Link to Trigger”.
-
Now my VARIABLE field in MAIN TABLE shows the content of the VARIABLE field in TABLE 1.
-
In MAIN TABLE in the field VARIABLECOPY make it a formula field and add simple formula to copy the content from the field VARIABLE. (=VARIABLE). This field will contain simply a copy of the field VARIABLE. … (I’ll explain later why this is needed. It is required because Airtable Automation can’t use Lookup field as source information for target, so this is a work-around).
-
In MAIN TABLE, I added 2nd AUTOMATION. The trigger is “when a record is updated” and the field to watch is “VARIABLECOPY”. The action is “update record”, the record ID is from the trigger step and the field to update is VARIABLESELECT with the content of the field VARIABLECOPY.
Done…
Now when I test this:
When changing the VARIABLE field TABLE 1, all my records in MAIN TABLE will get updated with information in Table 2.
Now you may argue, this could be done without the Automations just by linking TABLE 1 with MAIN TABLE. Yes, but the issue is, I would need to add the link for every new record created and even with copy-past method, it still requires another step. And if I was going to skip all of this and just link TABLE 2 with MAIN TABLE (and forget everything else), then I would need to update every record in the MAIN TABLE when the VARIABLE changes. And if I was going to leave 1st Automation and then just link the record from MAIN TABLE to TABLE 1, then I would not be able to control the VARIABLE in MAIN TABLE anymore and the MAIN TABLE would just display as read-only showing whatever is in the linked record from TABLE 1… and that’s not what I wanted… I wanted to be have a bulk-automated pre-fill and modification of all records BUT at the same time I still wanted to retain a full control over the variable in MAIN TABLE I decide to modify the field for individual records in MAIN TABLE. That’s why I needed all these steps above.
Also, the reason why the VARIABLECOPY field is needed is because Automations in Airtable can’t accept (right now) a linked value a source of data for action that will be updated in the target field. However, I found Automations is perfectly happy with a copy of that Linked field. That’s why that step is needed. Once Automations accept Linked field as data source, then this step won’t be required.
The method above is perhaps unconventional and complicated but I didn’t find any other way to do what I described as my requirement.
Notes: (***or only select)
If I wanted only select records to get considered in MAIN TABLE, I could just add a condition e.g. a separate View with only certain records being considered in MAIN TABLE.
So I wanted to share this in case anybody finds useful.
