I'm sharing a solution to a problem I had; hopefully this will help others. Note: you will need to be on a plan that includes Automations.
When you try to reference information from a linked field in a primary field, Airtable will warn you of a circular reference, even if no dependencies will be affected by the contents of the primary field.
For example: I work with cars, and I have two tables (among others) in my base: Vehicle Generations and Vehicle Years.
In Vehicle Generations, I'll have a record with the primary field "Jeep Wrangler, 2007–2018"; in Vehicle Years, I'll have a record with a primary field "2007 Jeep Wrangler", or "2008 Jeep Wrangler", and so on. And each of these Vehicle Years records is linked to a Vehicle Generations record.
Now, if I want to dynamically generate the primary field for Vehicle Generations (e.g. update the end year as new model years are added), I could use a formula field like
{Make & Model} & ", " & {Start Year} & "–" & {End Year}, where {Start Year} is a rollup field of linked records from Vehicle Years, with the formula MIN({Model Year}), and {End Year} is the same, except with the formula MAX({Model Year}), where {Model Year} is a number field in Vehicle Years.
In theory, it should work; and it does work in a non-primary field. However, Airtable will warn of a circular reference if you use such a formula in a primary field. (You also cannot use the formula in a non-primary field and then refer to it in a primary field.) Until Airtable changes this check, we'll have to bypass it by providing this information as text.
If you have mostly static data, you could simply enter the formula in a non-primary field, convert that field into a text field, and then refer to that field in your primary field. (For further updates, you could duplicate the original formula field and then copy the data over, or you could repeat the process.)
For example:
- Create the field {Generation Years} with the formula {Start Year} & "–" & {End Year} (Output: 2007–2018)
- Change the field type for {Generation Years} from Formula to Single-Line Text.
- Change the primary field to use the formula {Make & Model} & ", " & {Generation Years}
If your data constantly changes (or you don't want to rely on a manual process), you can automate the update process. There are a couple of ways to go about this*, but for this solution, since Airtable's automation conditions don't allow for complex comparisons, I'll use a helper field with the formula
SUBSTITUTE( {Dynamic Data} , {Static Data} , "" )Going back to the example, before I convert the formula field to text, I duplicate it first, so now I have the fields {Generation Years (Text)} and {Generation Years (Formula)}. I then convert the former to Single-Line Text, as before; and update the formula field:
SUBSTITUTE( the original formula , {Generation Years (Text)} , "" )The idea is that if the original {Generation Years} formula output equals {Generation Years (Text)}, then the field will be empty; otherwise, it will contain the output.
Based on this, we can then create the automation:
Trigger: When a record matches conditions
Condition: if {Generation Years (Formula)} is not empty
Action: Update record
Field: {Generation Years (Text)}
Value: {Generation Years (Formula)}
*You can also use "When record updated" and skip the helper field, but it doesn't run when a record is first created.
It's a bit of a hack, but pretty easy to adapt to other bases.
