[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.
-
In [Main], define a new single-line text field called {Link to Calc}.
-
Set the value of {Link to Calc} for every record in the table to equal ‘.’ — that is, the period character. This can be done
- 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;
- 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
- by hitting the sequence
.-Return (that is, ‘.’ followed by the Return key) until {Link to Calc} in all rows is set to ‘.’.
-
Scroll to the top of the table; right-click on {Link to Calc} and select ‘Customize field type.’
-
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.
-
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.
- 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.
