Linking every record of one table to a single record of another


#1

[Note: As this process is a crucial part of a number of other Airtable routines — for instance, finding the latest/first/largest/smallest something of a group of somethings, or [seemingly] any method allowing the value of a field in one record to be used in a calculation performed from a different record — I find myself entering it increasingly frequently. To save my fingers and others’ patience, I’ve decided to create a post dedicated solely to the process I can later reference as needed.


The following will create a link from every existing record in a given table (here called [Main]) to a single record in a second table (here called [Calc]). The creation of such a link between tables is a necessary first step for a number of Airtable routines.

  1. In [Main], define a new single-line text field called {Link to Calc}.

  2. Set the value of {Link to Calc} for every record in the table to equal ‘.’ — that is, the period character. This can be done

    1. by entering ‘.’ in the first row and dragging the fill handle (the small white box at the lower-right corner of the cell to be copied to the table’s last row;
    2. by entering ‘.’ in the table’s first row, selecting that cell, pressing Ctrl-C to copy the value, selecting {Link to Calc} in the second row, scrolling to the last row in the table, and, while holding down the Shift key, pressing Ctrl-V to paste the value in the remaining rows; or
    3. by hitting the sequence .-Return (that is, ‘.’ followed by the Return key) until {Link to Calc} in all rows is set to ‘.’.
  3. Scroll to the top of the table; right-click on {Link to Calc} and select ‘Customize field type.’

  4. Change the field’s configuration from ‘single-line text’ to ‘Link to another record.’ When prompted, select ‘+ Create a new table’ and specify [Calc] as the table name.

  5. Airtable whirs for a moment; then, the background color of each ‘.’ changes slightly. This indicates each ‘.’ has been converted from a character in a single-line text field to a link from a record in [Main] to a record in the newly created [Calc] table with the {Name} of ‘.’.

At this point, lookup and rollup fields can be defined in [Calc] that act against all records in [Main]. For instance, if each [Main] record contains a {Date} field as a timestamp, rolling up {Date} using the aggregation function MAX(values) will reveal the date of the latest record.[1]

Note: This process only serves to link existing [Main] records to [Calc]. Going forward, any new record added to [Main] also needs an explicit link to [Calc]. If performed manually, this adds but two mouse-clicks — one on the plus sign in {Link to Calc} to drill through to the list of [Calc] records and a second to select the record named '.'. Alternatively, creation of the link can be automated through the use of a third-party middleware service such as Zapier. In the case of Zapier, the routine to create such a link would be considered a two-step Zap and can thus be executed even by a free account.


  1. Obviously, a formula field with the formula CREATED_TIME() could be used for such a timestamp. However, it should be noted the first three records of any table are created at the same time as the table and, as such, bear identical CREATED_TIME()s.

Calculate Percentage using Total From Column
Specify field in another table in formula?
View last record only
Compare results in two tables
Is there a way to create a moving average?
Use a summary value in a formula
Use a summary value in a formula
Apply formulas across summary bars
How to get the highest value out of a column
Sorting my records based on the active years
How do I automatically add up all values in a column?
How to get max value of a set of records (in Airtable or via API)
Sum of all amounts in one column presented in another table
Make the result of Roll Up appear in every row rather than in a single fiedl
Restart number sequence with each group?