Help

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

6420 6
cancel
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
Raj_Bharij
4 - Data Explorer
4 - Data Explorer

Hi, I have managed to complete the above sequence and that works fine. I am however having trouble with the rollup. I have a column that specified gender and I want to be able to do a count of (percentage would be even better) for the number/percentage of females/males in the table. I have tried various formulas like: Countall(“Male”) or Count(CountAll(“Male”) but not working.

Sorry if this is not the correct place to post.

In your case, in addition to the all-to-one link, you need a few more steps:

  1. In your main table, define a field ‘Male’ with the formula {Gender}='Male'.
  2. In your main table, define a field ‘Female’ with the formula {Gender}='Female'.
  3. In your calculations table, define a rollup field {Males} that follows the link to [Main] and rolls up {Male} with SUM(values).
  4. I bet you can guess this one. :winking_face:

Now you can use {Males} and {Females} in your calculations table to calculate a percentage (or whatever). If needed, you can then pass the calculated value back to [Main] using a lookup field.

Hi, thanks for the reply and the steps. Tried it out and it works however I have a lot of similar data that I want to be able to report on the overhead of creating all these fields for each option seems a lot. I assume then that the basic functionality doesn’t exist in ‘Pivot Table’ blocks ?

Eric_Petersen
7 - App Architect
7 - App Architect

4th Method /Option for Step 2.
4. Create a Formula Field = “.” Left Click on the Formula Field you created to highlight the column. Ctl+C to copy the values of this column. Left Click on the {Link to Calc} column and Ctl+V to paste the values and create the links.

Pamela_Hannon
6 - Interface Innovator
6 - Interface Innovator

@W_Vann_Hall Hi, this was so helpful! I’ve followed the steps above but am wondering now how I can calculate % based on this total. On my ‘Main’ table I have a questions that is either Yes or No, I want to calculate the Yes out of the total - can you help?

Sergey_Gladyshe
5 - Automation Enthusiast
5 - Automation Enthusiast

Thanks a lot for the instruction. I replicated and it works perfectly.
I also found another more easier way to populate the ‘.’ text into the new records, without Zapier or any other external systems.
I just add an automation on a table, when a new record is added the automation updates my fileds {Calc} with the value ‘.’. Works even with free airtable account.

I am very interested in seeing this in action. When I go to create an Automation I do not have the ability to update my Calc Field because “it is computed” How do I get around this?
Thanks
Jon

{Calc} is not computed filed but it is Link to another table field.

P.S. In that other table, there is one line, first column should be ‘,’. Then it all works.

Yeap I pushed my way through that. So now I have references to all my records and using a formula I am able to create an array out of the names of those Applications
CONCATENATE(ARRAYJOIN(Applicants),“”)
So it returns “Jon, Steve, Tommy” etc… However, what I am looking for is not their Names from those records but another column. In my case it is called “CCS Record Number” I need these in array so I can compare to a server generated ID to make sure there isn’t a duplicate. Using my formula to create the Applicant names, how do I pull the non primary field?