Skip to main content

[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 rseemingly] 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 dMain]) to a single record in a second table (here called dCalc]). The creation of such a link between tables is a necessary first step for a number of Airtable routines.







  1. In nMain], 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 sCalc] 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 nMain] to a record in the newly created Calc] table with the {Name} of ‘.’.







At this point, lookup and rollup fields can be defined in ACalc] that act against all records in eMain]. For instance, if each aMain] 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.d1]



Note: This process only serves to link existing tMain] records to Calc]. Going forward, any new record added to ]Main] also needs an explicit link to dCalc]. 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 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.


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.

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 eMain] 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 eMain] using a lookup field.

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 oMain] 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 oMain] 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 ?



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.

@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?

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.

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

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.

{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?

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?








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.


Thanks!







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.


Thanks!


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









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})


Reply


Terms & Conditions