How to make auto calculations between two tables in AirTable

[Paulina_Baray]

I have two tables that have information that is needed to create formulas to auto calculate gaps. For example, I have one table set up grouped by Month and Coach and has columns that are in currency format. They have revenue amounts and other count numbers. I have another table that is created to calculate the gap between the numbers in the first table and the newly entered numbers from Coach inputs in a form within that second table. So somehow I need to get the information that is sorted by Month and Coach into the second table to create formulas to calculate with the new information that will come from the form.

Thank you in advance!

[Justin_Barrett]

Tables can only share data when there is a field linking them; i.e. you add a field in one table that points to specific records in another table. With that link in place, other fields from those linked records can be shared.

Ironically, the situation you described where you need to “calculate gaps” feels like it has (for me, anyway) some gaps that are making it difficult to understand the structure, so I’m not sure how to suggest connecting your tables. Would it be possible for you to post some screenshots, and provide additional details on what fields need to be shared between the tables?

[Paulina_Baray]

Hi Justin!

I can provide some additional details. So I have a table titled Vision 2020 that contains vision revenues, vision client cap, etc. Those fields are all either currency or numbers. This table is grouped by month since each month has different values in those fields. Within each month, each coach (person) has a line with the numbers that apply to them. Then, I have another table titled Pipeline where I have created a form that asks for current information. My goal is to create formulas in this table to calculate the difference between the Vision numbers and the current numbers that will be provided via the form in Pipeline table. I’m just unsure how to link everything correctly so that the appropriate month is used in the formulas.

[Justin_Barrett]

Thanks for the added info.

Paulina_Baray:

This table is grouped by month since each month has different values in those fields.

Am I correct in assuming that the month specifier is in a single-select field? If so, you might be able to get what you want by changing it to a link to another table. I’m going to call this [Months] for now, but use whatever name you want. To easily convert from your current single-select to the new link, just change the field type. Airtable will ask if you want to make a new table, and you can give it a name at that time. When the new table is made, it should already have records based on the months you had previously assigned. You may need to fill some gaps depending on how many months were used before, but that should be pretty easy.

Because you probably want to keep historical info and not reuse “May” from 2020 for your 2021 tracking, you may want to consider including the year in the primary field. Your records in the [Months] table would then have names like this:

January 2020
February 2020
March 2020
...

Anyway, you now have a table that others can tie into. In your form on the [Pipeline] table, it would ask the user to pick the month (from that linked table) for their submission.

Because the records in the [Months] table are now linked to both the [Vision 2020] and [Pipeline] tables, you can pass any month-specific data between them via lookup and rollup fields, which you can then use in your formulas.

Does that get you started in the right direction?