How to Use Rollup in Airtable
If you’ve been using Airtable to create and manage your databases, you’ve probably wondered how to apply formulas to specific cells. Fortunately, rollup fields enable you to assign a specific formula to selected cells and perform calculations that you need.
In this article, we’ll show you how to create a rollup field in a few simple steps. Also, you’ll learn the basics of the Airtable database and other important features.
How to Use Rollup on Airtable?
The Rollup feature does the same thing as the Lookup function, but it can also create a formula. In other words, you can select specific cells in records linked to a different table field and then apply formulas.
However, if you want to pull data from one table and use it in the other, you need to create a link between the two tables. To do this, one table needs to contain a column with data that can be found in the primary key column of the other table. If this is the case, you’re ready to link these two tables together.
- Navigate to the table that contains the field to which you want to link records from another table.
- In the field tab, click on the small arrow button.
- In the field type section, click on “Link to another record.”
- Choose the table with the records that you want to link to this field.
- Click “Save.”
Now that you’ve linked the two tables, you can create a rollup field:
- Click the “+” icon right to your existing fields.
- Enter the name of your field.
- Select “Rollup” as your field type. (Note: You can type in “rollup” in the “Find a field type” search bar to find the rollup field).
- In the “Rollup” tab, choose the table you want to use for summarizing records.
- Select the field you wish to rollup. The data from this field will be transferred into the rollup field you’re currently creating.
- Choose the criteria for the records you want to rollup. The records that meet these criteria will be added to the rollup field.
- Enter the rollup formula you want to apply to the added values (e.g. “AVERAGE”).
- Click “Save.”
Success! You’ve created a rollup field.
How to Use Airtable Database?
To better understand how Airtable databases (or bases) work, you should first know how to create a base from scratch.
- Go to your workspace and click on “Add base.”
- Select “Start from scratch.”
- In the extended menu, choose the name, color, and icon for your base.
- Click anywhere on the screen to close the extended menu and finish creating the base.
Note: Airtable gives you the option to enable automatic Slack notifications every time you make a change to your base.
Alternatively, you can use one of the Airtable templates and then edit it according to your needs. In the “Universe” tab you can even find user-generated templates. This can give you new ideas on how to organize a base for your specific purposes.
The third way of creating a base is by using a CSV file. If you’ve used Trello or Asana, you can export your database as CSV files and then import it in Airtable. The CSV file will automatically be transformed into an operable base.
Once you have your base set up, you need to know about the three main features of an Airtable base: tables, records, and fields.
Each table consists of records and fields. Records represent separate items in your table, while fields store information about records.
At the top of your dashboard, you will see tabs that represent your tables. They have a function similar to spreadsheets in MS Excel, but you can set them up to exchange data with other tables.
When you create a new database, you’ll have only one table, so you need to add more tables. You can add a new table by clicking on the “+” icon next to an existing table.
If you want to rename, duplicate, delete, or edit information about a table, click on the small arrow button and choose the desired option from the extended menu.
- Records (Rows)
Records represent items that you want to group. A record can contain your employee’s name, a task, an idea, or anything else that you want.
To insert a new record, you can implement one of the following three ways:
- Click on the record with the “+” icon.
- Right-click on one of the records and select “Insert record above” or “Insert record below” to add a new record in a specific place in your table.
- Select a record and then press ‘’Shift + Enter’’ to create a new record below the selected one.
- Fields (Columns)
With fields, you can create different criteria for organizing information about your records. When you create a new database from scratch, you’ll only have three fields. Should you want to add a new field to your table, you can press the “+” icon next to the rightmost field.
For each field you create, you can set a field type. Selecting a field type determines the nature of data that the field will show. You’ll always have this option when adding a new field.
However, you can also change the field type of an existing field as well:
- Click on the small arrow button in the field tab.
- Select a field type from the range of field type options (e.g. Attachment, Checkbox, Multiple Select, etc.).
If you want to get more advanced options, you can select the “Formula” field type which is similar to Excel.
The important thing to know is that the first field in your table (left to the vertical line) is your primary field. This field is essential when you are using the Rollup feature. Also, the primary field can never be hidden or deleted.
How Do I Create an Airtable API?
Airtable enables you to view your database in your web browser by using an API. To create an Airtable API, you’ll first have to obtain your Airtable API key:
1. In the top-right corner of the screen, click on the account icon.
2. Select “Account.”
3. Under the API heading, click on “Generate API key.”
Note: You can regenerate or delete your API key by clicking on one of the options next to your API key.
Now that you’ve got your API key, follow these steps:
1. Go to Airtable API.
2. Select the database for which you want to create an API.
How to Rollup the Product Cost Field?
If you want to rollup a field that contains product costs, you will have to change the field type.
1. Click on the small arrow in the product cost field tab.
2. Choose the “Rollup” field type.
3. In the “Rollup” tab, choose the field that contains the products (e.g. “Products”).
4. Then, select “Price” in the second drop-down menu.
5. Choose the function you want to apply to product costs.
6. Click “Save.”
How Do You Count in Airtable?
We assume that you don’t want to count manually. Unfortunately, you can’t use a formula in Airtable to count selected details. Instead, you can create a new field that will count the number of selected variables to show you the results.
1. In your table, click on the “+” icon next to a field to create a new field.
2. Enter the name of your field.
3. Select “Count” as your field type.
4. Choose the field that links to the records you want to count.
5. Click “Save.”
Alternatively, you can use a rollup field and apply the “COUNTALL” formula.
1. Click the “+” icon right to your existing fields.
2. Enter the name of your field.
3. Select “Rollup” as your field type.
4. Choose the table and the field from where you want to extract and count specific information.
5. Select “COUNTALL” as your aggregation formula.
6. Click “Save.”
How Do You Use Rollup Summary?
To create a Rollup Summary, you’ll first need to link two tables. Then, create a rollup field as described at the beginning of this article. When finishing creating the rollup field, choose the SUM rollup formula.
Using Rollup in Airtable
Finding a way to calculate specific cells may not come intuitively. Thus, we hope that the Rollup feature helped you create formulas for your selected options.
You’ve learned how to link two tables and create a rollup table that uses information about records relevant to the other table. More importantly, you know how to create your own database from scratch and use tables, fields, and records to organize your data to meet your business needs. On top of this, you can create an Airtable API, which lets you view your Airtable data on a web page.
Did you use the Rollup feature as well? Do you know any other solution that may substitute the rollup feature? Let us know in the comments section below.