Combining multiple tables into one table using views
  • 07 Jul 2022
  • 10 Minutes to read
  • Dark
    Light

Combining multiple tables into one table using views

  • Dark
    Light

In general, you can think of a table as a list containing all the people, ideas, or items of a specific type. For example, a table could be a list of blog posts or recording artists on your record label.

Sometimes, beginners to Airtable might create multiple tables for the same type of item. It might be to represent different statuses (separate tables for Blog post ideas, Posts in progress, and Completed posts) or subtypes (separate tables for Rap artists and Country artists).

While this can make finding what you need easier in the short term, it can also present problems in the long term. If you represent status changes by moving a record from one table to another, it can be easy to make mistakes while copying and pasting; multiple tables for the same types of items can also make it difficult to summarize findings from all of your items at a high level. Read on to find out if your own base has too many tables.

Recognizing the pattern

Check the following to see if your base might benefit from some redesign. If the following sounds like you, you should consider taking your records from multiple tables, putting them together into one table, and then using views to see just the information you need for the task at hand.

Is this you?

  • Do you often find yourself copying and pasting tables, or copying and pasting records from one table to another?
  • Do you notice that some tables are very similar in structure?
  • Are you often trying to get an overview by combining multiple tables that track similar things?
  • Are you creating tables for different people just to keep them separate?

Is this your base?

  • You have a table for each stage in a process...

36000972949420180731-dd-mult-vs-1-table-1.png

  • ... instead of one table for all stages and multiple views within that table.

36000978627320180731-dd-mult-vs-1-table-2.png

Other patterns to look for in your base

  • A table for every month
  • A table for every social media platform you post to
  • A table for every step in a process
  • A table for every category of a product or service you have
  • A table of tasks for each person on your team
  • A table for each type of event you manage

How to choose

While both approaches have their advantages, we recommend designing your base around having multiple views in one table.

Multiple views, one table (RECOMMENDED)

  • Get high-level summaries and gain insights across all views using tools like rollups, the summary bar, and pivot tables.
  • Use kanban views to move items through stages or steps.
  • Form responses can feed into one table, then get assigned to the right place by simply changing a field (rather than copying and pasting responses from one table to another).
  • Create master calendars containing all the information related to a certain class of item and create calendar views filtered to a specific value.
  • Avoid time-consuming and complicated processes of moving records from one table to another table.

Multiple tables

  • Table names visible along top can make finding what you need more easy and obvious.
  • Each table will have fewer views associated with it, making view navigation within each table easier.

How to combine multiple tables into one table with multiple views

Follow these steps to consolidate the information that's been spread out across different tables into one table. We'll also walk through how to create views in this table that will serve the same functions as the old tables.

Add a field to hold the name of the table for every table that will be merged.

Your first step in combining multiple tables is to create a field in every table that will capture what is different about each table. For example, if your tables were created for each month, make a Month field. If you had a table for each step in a process, create a Step field. We do this so the records for that table will still retain that extra detail.

Make the new fields in every table a Single Line Text field for now. We can change this to a single select field or a linked record field type later.

In the Product Launch example, we create a new field called Feature Status because "In Progress," "Complete - Not Staged" and "Ready" are the names of the tables.

36000978621320180731-dd-mult-vs-1-table-3.png

Populate the new field in all old tables with the name of the table.

Each table will have the same value in the new field. For a set of tables that differed only by month then the January table would have “January” as the value of all of the records in that table.

In the Product Launch example, we can use the values of “In Progress”, “Complete”, and “Ready” in each of the tables.

36000978619320180731-dd-mult-vs-1-table-4.png

Duplicate one of the tables to be merged to be the new table.

If all of the tables to be merged have the same fields in the same order with the same field types, then you can make your new combined table from a copy of any of the tables. However, if some tables contain additional information not found in the others then you would choose the table with the most fields and add the additional fields found in other tables. Make sure these additional fields are the same field types across all tables to be merged but you can leave them with empty values.

In the Product Launch example, the In Progress and Complete tables do not have a “Launched?” field nor a “Ready for Launch?” field. The Ready to Launch table has a more complete set of fields so we use that as the table to copy. We name this new table Features.

36000972951420180731-dd-mult-vs-1-table-1.gif

Create new views in the new table to recreate the old tables.

These new views are used to copy the old records to the new table. If you have three original tables, you will want to create at least one view for each of the original tables with the same fields in the same order. If you don't have a view from the original table that has all the fields unhidden, make one.

In the Product Launch example, we create three views named “In Progress Grid”, a “Completed - Not Staged Grid” and “Ready to Launch Grid” in the Feature table.

36000978617320180731-dd-mult-vs-1-table-6.png

When you are creating these views, make sure they have the same fields in the same order as the matching tables. Hide fields if necessary. For example, the In Progress table and the Complete Table do not have the Launched and Ready for Launch fields so those are hidden as shown below. But the rest of the fields in the Complete view are in the same order as what appears in the Complete-Not Staged table.

36000972967420180731-dd-mult-vs-1-table-7.png

Copy the records from the old tables to the new table.

You can select all the records from the old table using the checkbox in the upper left and paste it into the matching view you just made in the new table. You can use keyboard shortcuts to copy ( Ctrl + C) and paste (Ctrl + V ).

In the Product Launch base, the “In Progress Grid” in the Complete - Not Staged table can be copied into the “Completed” view in the new Feature table. The In Progress table can be copied the same way.

36000972993420180731-dd-mult-vs-1-table-2.gif

[Optional] Summarize by using a linked record field and a count field.

Want to count items across all the new views you just made? Change the field type of the field you created in the first step to a linked record. When prompted, create a new table instead of linking to an existing table. Converting to a linked record field type will make a record for every distinct value from that field in the new table. You can then add rollup or count fields to create a summary based on the original tables.

In the Product Launch table, the Feature Status was the new field that was added to all the original tables. When this field is changed to a linked record field, three new records are created (“In Progress”, “Ready”, “Complete”) with the proper link to each record. By adding a count field, this new Feature Status table can show detailed summaries for each status.

36000972983420180731-dd-mult-vs-1-table-3.gif

Delete all the old tables.

When you are satisfied that all records have been moved to the new table and views have been made in the new table, you can delete the original tables.

In the Product Launch table, delete the In Progress, Ready and Complete tables.

360009740574ScreenRecording2018-07-10at1047AM1.gif

Scenario: Matt and monthly snack purchases

Problem

Matt is in charge of snacks every month at his office. He records what is purchased and how much he spent. He also uses the snack list to record what he should purchase next and just leaves the price and date blank.

36000978607320180731-dd-mult-vs-1-table-11.png

His December snack list is special because employees like to bring in treats from home. To make sure the treats are not brought in all on one day, he has an additional field (Contributor) for that month showing who is bringing what homemade treat on what day.

36000978605320180731-dd-mult-vs-1-table-12.png

Every month, Matt copies the previous month to set up the current month. He deletes the copied data and then he is ready to go. He likes this because then the list is nice and short without any older purchases.

36000972955420180731-dd-mult-vs-1-table-13.png

However, recently Matt has realized he wants to see the total amount spent on the year. With his current tables set up by month, he can't take advantage of features such as grouped grid views, the pivot table extension, and the chart extension to get the overall picture because those require all the records to be in one table. Using rollups might provide some information but a rollup field would need to be created for every monthly table. If this system is used for years, that would be a lot of rollup fields!

Solution

By combining all the purchases in one table, Matt can use built-in features to quickly view the total purchased by month and year.

Steps

Add a new field to every table to be merged.

Matt adds a Snack Budget Month field to January Snacks, Feb Snacks, and all the other snack tables. When all the tables are later combined, we will be changing the field type all at once, so just use the default Single line text here to save yourself some time.

36000972957420180731-dd-mult-vs-1-table-14.png

Populate the new field in all old tables.

The new fields in each of the old tables are populated according to the table in which they're located: for the records in the Jan Snacks table, the Snack Budget Period field is populated with "Jan"; for the Feb Snacks table, the Snack Budget Period field is populated with "Feb," and so on. Matt types the value in the first cell of the Snack Budget Month and then uses the drag handle to copy the value to all the other fields.

360009740254ScreenRecording2018-07-04at0715PM25.gif

Copy one of the tables to be merged to be the new table.

The Dec Snacks table has the most complete set of fields because it has the additional Treat Contributor field and Treat Photo field. Matt copies the Dec Snacks table and renames it Snack Purchases.

36000972953420180731-dd-mult-vs-1-table-16.png

Create new views in the new table to recreate the old tables.

To make the next step of copying data easier, Matt wants to reproduce the separate tables as new views. In the new Snack Purchases table, Matt creates a view called Snack By Month which has the same fields in the same order as the Jan - Nov Snack tables. In Snack By Month, he hides the Contributor field and also groups by month. By using grouping, Matt won't have to recreate a table for each month.

Matt also creates a Holiday Snack view which has the same fields in the same order as the Dec Snack table. In this view, all the fields are visible.

36000978599320180731-dd-mult-vs-1-table-17.png

Copy the records from the old tables to the new table.

Matt now can copy the records from the old tables to the new table. He uses the Snack by Month view to copy and paste over the rest of the records from January through April. When he is done copying, he also groups by the Snack Budget Month.

36000972977420180731-dd-mult-vs-1-table-4.gif

Delete all the old tables.

Matt deletes each of the old tables one by one.

36000979637320180731-dd-mult-vs-1-table-19.png

Matt is only left with the one table for an easier-to-manage base. He will no longer have to create a new table every month.

36000979639320180731-dd-mult-vs-1-table-20.png


Was this article helpful?