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

4101 2
Showing results for 
Search instead for 
Did you mean: 

[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.
12 Replies 12

Screenshot 2022-11-22 at 12-41-49 CCS Card Production Summary - Airtable
Screenshot 2022-11-22 at 12-41-41 CCS Card Production Applicants - Airtable
I made a smaller lightweight view of what I am trying to do. What I need is the “Applicant ID” to be pushed up into an Array, like I have done with All IDs, but that is grabbing the Primary name.

Hey. Once you linked the tables you can pull all other columns via Lookup function.


If you want just unique applicant id’s, just add one more formula column with ARRAYUNIQUE({Applicants ID})