How to merge multiple tables into one in Airtable

Introduction

In this article you’re going to learn how to consolidate your Airtable data into fewer tables so that you end up with a neater, more organized base. 

And let me be clear: this is not a “theoretical” article!

You see, here at MemberFix we run many aspects of our business from within an Airtable base that we call our MemberFix Control Center. 

Over time, as we’ve continued to add new tabs and new categories of information to our control center, we started to notice multiple tables with redundant data.

For example, we initially had a table for Customers and a separate table for Team Members.

But in Airtable, you ideally want to place similar categories of information in one table. In this case, both customers and team members can be grouped under the umbrella term “people”. 

(And for that matter, so can Partners, Affiliates, and any other type of “person”.)

As you might imagine, it can get messy to have your data in too many different places.

So we decided to do a little housekeeping by consolidating multiple related tables into single tables wherever it made sense to do so.

From there, we just created Airtable Views to easily look up the various sub-categories of information in a given table.

(For instance, in our People tab, we have a “customers” view, a “team members” view, a “partners” view, and so forth.)

Here’s how you can do the same.

Video Tutorial

Thinking about and setting up your new tables

Let’s start with an example.

If you’ve got a table for “Cats” and a table for “Dogs”, you want to put them in a single table called “Animals”. 

While most real life examples are more complex the principle is the same: you identify similar categories of information that reside in separate tables and you join them in a single table that acts as the parent category, so to speak.

To show you a real example from our business, here are three separate tables that we decided to join into a single table:

^ Team table ^

^ Customers table ^

^ Partners table ^

Notice that all of these tables share similar data types with similar fields; namely: Name, Status, Email, Website URL, etc.

Since they’re essentially all just different types of “people”, there’s no reason for them to be in separate tables.

The only reason that they were in separate tables to begin with was because it seemed more convenient at the time, probably mixed with some laziness and procrastination on our side (hey, we’re human).

In any case, joining these three table into a single table provides several immediate benefits to our team:

• All similar data is in the same place
• No redundant data in the base will make it more lightweight and faster (if you are using the free package this will also free up cells for new data)
• Easier record linking to other tables

…among other benefits.

Consolidating your existing tables

Step 1 – Create the new table to which you’ll be transferring your data from the multiple other tables where it’s currently stored.

To reiterate, the reason we named our new, single table ‘People’, is because all of the entities in the three separate tables we used to have—Partners, Customers, Team Members—are in fact all just different types of people. 

Step 2 – Create a list of all of the fields in the tables from which you will be transferring data to your new, single table. Skip this process for linked records. 

NOTE: When you link a column in the table to another table, Airtable automatically creates a reciprocal linked record in the source table as well! (A great way to remember this is that linked records are a 2-way street; both tables have to link to each other, you can’t have one without the other!)

Step 3 – Add these fields to your new table.

When you add them, you will need to append a suffix after the name of the field to help you identify unique fields for each table that will be included into the consolidated table.

The reason we do this is because we’ll need these suffixes later to build Views in our new table.

In our case we used the following naming convention:

• <column name> (Team) – for unique fields from ‘Team’ table
• <column name> (CX) – for unique fields from ‘Customers’ table
• <column name> (Pr) – for unique fields from ‘Partners’ table

*It might not make a whole lot of sense at this point why we’re creating suffixes but you’ll understand what this actually looks like and WHY we do it in just a moment, so read on!

Step 4 – Copy the data from your old tables into the corresponding columns in the new table.

HINT: you can hide columns and change their order by drag-n-dropping them.

Use this to copy and paste data in blocks, not just column by column. Just make sure that the destination columns order is the same as in the table from which you copied your data.

After all the steps above you will produce something close to the table in the picture below:

• Name – Is a primary field, required by Airtable, there is no suffix in it because it is non-unique for all 3 tables.
• Type (Team) – a single select field, suffix ‘Team’ means that this field will be used to generate Team view for People table
• Rate (Team) – a number field, suffix ‘Team’ means that this field will be used to generate Team view for People table
• Email – an email field, there is no suffix in it because it is non-unique for all 3 tables.
• Website 1 (CX) – an URL field, suffix ‘CX’ means that this field will be used to generate Customers view for People table
• Listing URL (Pr) – an URL field, suffix ‘Pr’ means that this field will be used to generate Partners view for People table

Now let’s check the Status field…

As you may notice it has PRE-fixes similar to the SUF-fixes we have used for our column names. And their purpose is exactly the same as the suffixes, but for this single column only.

From the beginning we had just 3 separate Status fields:

• Status (Team)
• Status (CX)
• Status (Pr)

But as we continued to work on this we decided to combine these 3 fields into a single field as they had very similar status options (Active, Inactive, Prospect, etc.).

These fields also had unique statuses so we had to separate them somehow. Prefixes seemed like a good solution and indeed, worked like a charm!

Step 5 – Now when you have all of the unique data in your new table labeled correctly, you’re going to create a separate View for each set of entities from the initial tables.

To do this, you’ll need a brand new field that you have not had before in any of your old tables. We’ll call it the Entity field.

Create this ‘Entity‘ field and set it to be a Single Select field type. Then assign the following options to it:

• Team 
• Customer
  
• Partner

Step 6 – It is time to bring an order to this chaos!

Create a new Grid View and name it ‘Team View‘, then apply a filter with the following condition:

WHERE Entity is ‘Team’

Step 7 – Now you have all records from your initial table in this new view.

But there are also columns from the other tables clogging up this view, so what can you do about those?

Simple: just hide them whenever they don’t belong in a particular view.

The cool thing in Airtable is that it preserves your column order, filters, sorting, groupings and hidden fields for each individual view without affecting other views.

When you’re done you will see a view close to the one in the screenshot below:

Repeat steps 6 and 7 for each table you are going to transfer over to the new one.

Here are the views we created in our particular scenario:

Update linked records mapping to the new table

Now when you have your new table setup and running you will need to switch all fields that have been linked from other tables to link to the new one instead.

Unfortunately this is not something Airtable can automate or track automatically so you will need to do that yourself.

Step 1 – Check which columns linked to old tables:

Step 2 – Link columns to the new table:

IMPORTANT: You will need to enable ‘Limit records selection to a view‘ option and select the corresponding view that you created before. This is key because now instead of having multiple tables full of info, you have a single table with multiple views full of that same info.

Repeat Step 2 for each column that was previously linked to old tables in each of your new tables.

Step 3 – Now when you have remapped all records to the new table the process is almost finished.

There is some room for polishing your results, though.

If you return to your new table (for us as you remember it is ‘People’) and open a hidden fields list you’ll notice that several new records have been automatically added by Airtable:

These are Linked Records fields that Airtable automatically adds whenever you link a record from one table to another.

So you’ll need to edit their names to comply with the naming convention you are using for your new table.

If you’re following our example, the first one should have the suffix ‘Team‘ because it is linked to the Team View. The second one should have the suffix ‘CX‘ since it’s linked to the Customers View.

Here’s what the renamed and tidied up version looks like:

Repeat Step 3 for each field

Step 4 – Go through each view and hide the fields which do not belong to this view and vice versa, as needed.

Results

Once you finish the process of consolidating your scattered data from multiple tables into fewer, more united tables with distinct views, you can begin to delete your old ones.

I suggest that you keep them for the time being just in case you will need something from them, and to give your team a chance to transition to the new way of doing things smoothly.

If you’ve followed along with this article you should now have:

• More space in your Airtable account as you will delete old tables and some records that now joined into a single column (ex. Status column in our case)
• All the data in one place organized by views—improved visibility and less friction to access certain records
• Total increase of your Airtable base as it now serves less records

Now let’s hear from you!

Do you experience a visibility issues due to a lot of tables in your Airtable database and thinking about merging them into a larger ones?

Tell us in the comments section below!