Hi,
I am trying to create a “summary” table within the same base as the input tables (this is a template that will be copied).
It looks like Syncing the tables is only possible if I do the syncing in a new base. But I am essentially looking for this concept within the same base. How could I get totals/sums from the table Option A, Option B, etc into a Total table?
Thanks in advance for your help!
Hey Zach!
So, this is where Airtable’s foundational functionality comes into play.
Relationships!
What you want to do is create a relationship between your records.
In this case, you want to take Option A and Option B and link them to a junction Total.
Here’s How:
- In Option A, create a new field. Select the option to create a link to another record.
In this case, you’ll want to create a link to the Total table.
Be sure to select Allow Linking to Multiple Records. (I’ll explain this below).
Once you do this, a new field will appear not just in the Option A table, but also in the Total table.
-
Repeat the process in the Option B table.
-
In the Total table, you will now have two new linked fields.
For the sake of examples, here’s a demo of how this works…
-
Create new records in the Total table called Project A, Project B, and Project C.
-
Now, go into your Option A table, and find all the records that you want to relate to Project A.
In this case, we’ll use Material Cost A and Labor Cost A. -
In the new field called Total, type in Project A. A list will appear that will allow you to link the record to the project. Repeat the process in the Option B table.
If you’d like, you can do this for all of the records in Option A and Option B.
The end product will look like this:
Here’s where we solve your problem!
Now that we have all of our records linked together, we can now summarize their data.
So, let’s get the summarized information for Option A:
In the Total table, create a new Rollup field.
You want to tell it to rollup from the Option A table. Let’s rollup the expenses field.
Now, in the bottom input bar, we can create a formula that tells Airtable how to interpret all the data from the expenses field.
There are a ton of options to choose from, and it’s worth exploring them to see how you can manipulate what you get.
In this case, you want to get the total amount, so we’re going to use the SUM() function.
It displays as SUM(Values). (This just means that it’s taking the sum of all the values in the Expenses field.)
Now create the field, and you’ll see the rollup field populate with the sum of all the expenses from that table per project. Here’s what mine looks like:
Now, we can do the same process for Option B. Your finished product should look like this:
So Now What?
So let’s say you want to get the full sum of all of the expenses from both of those rollup fields.
We can use a formula to do this!
First, I’m going to rename the rollups to just Expenses A and Expenses B, since it will help with keeping the formula clean.
- Create a new formula field.
- We want the sum of the two fields. We could do this via the same SUM() function we used in the rollup. But we can also just use the addition operator to do this. So, we’re going to enter this using this simple formula:
{Expenses A} + {Expenses B}
Now, if you create the field now, you’ll get a flat value integer like 2550 or 1050, instead of a currency value.
To change this, just pop into the formula configuration and select the Formatting tab. Here, select the currency format, and the values will convert to a currency value.
Your final product should look like this:
Your base design of having the two Option tables makes your life a little bit harder.
Since you have two tables holding the same type of record (in this case, a record holding an expense or a credit), you run into problems with how that data can behave and interact as you scale it.
I highly recommend that you merge all of the expense records into one table, and use something like a single select field or even your linked records to sort, filter, or group them.
Let me know if you have any questions or theoreticals you wanna push at me.
