
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 06, 2018 07:59 AM
[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, theperiod
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, pressingCtrl-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 theShift
key, pressingCtrl-V
to paste the value in the remaining rows; or - by hitting the sequence
.
-Return
(that is, ‘.
’ followed by theReturn
key) 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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 03, 2019 01:43 AM
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 03, 2019 05:21 PM
In your case, in addition to the all-to-one link, you need a few more steps:
- In your main table, define a field ‘Male’ with the formula
{Gender}='Male'
. - In your main table, define a field ‘Female’ with the formula
{Gender}='Female'
. - In your calculations table, define a rollup field
{Males}
that follows the link to[Main]
and rolls up{Male}
withSUM(values)
. - 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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 04, 2019 12:43 AM
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 ?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 17, 2020 09:15 AM
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 03, 2020 11:44 AM
@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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 26, 2021 04:37 AM
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 22, 2022 08:46 AM
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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 22, 2022 09:07 AM
{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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 22, 2022 09:31 AM
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?
