[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, theperiodcharacter. 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, pressingCtrl-Cto copy the value, selecting{Link to Calc}in the second row, scrolling to the last row in the table, and, while holding down theShiftkey, pressingCtrl-Vto paste the value in the remaining rows; or - by hitting the sequence
.-Return(that is, ‘.’ followed by theReturnkey) until{Link to Calc}in all rows is set to ‘.’.
- by entering ‘
-
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 identicalCREATED_TIME()s.
