Using views to section out related data in Airtable
  • 26 Mar 2024
  • 13 Minutes to read
  • Dark
    Light
  • PDF

Using views to section out related data in Airtable

  • Dark
    Light
  • PDF

Article Summary

Plan availability

All plan types

Platform(s)

Web/Browser, Mac app, Windows app, and mobile apps

Related reading

Learn how Airtable views can help you to store data in a single table, while still allowing for data to be portioned correctly depending on the needs of the user viewing and interacting with that data in Airtable.

Why use views instead of tables to section out data?

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 between using views or tables

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.

Step 1: 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

Step 2: 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

Step 3: 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

Step 4: 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

Step 5: 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] Step 6: 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

Step 7: 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: Tracking 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 and walkthrough steps

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

Step 1: 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

Step 2: 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

Step 3: 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

Step 4: 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

Step 5: 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

Step 6: 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

Transferring data from a table in one base to another base

With a dynamic tool like Airtable, we usually have a few ways to go about doing things so you can pick the way that works best for you. When it comes to transferring information from one base to another, we have a few workflows to choose from.

Workflow option 1:

An option to display information from one base in another base is to use the Airtable syncfeature. Syncing is a one-way process allowing you to link from a source table to a destination table. Be mindful, as a one-way process, this means that in the destination base you cannot:

  • Add or delete records in the synced table

  • Edit information in the fields that are synced

There are three basic steps to complete in order to set up a sync:

  1. Create a syncable view share link in the source base

  2. Choose which method you would like to use to create the sync in the destination base

  3. Use the setup window to configure and create the synced table

If you need to edit fields on the destination table the information is being displayed from, two-way sync is probably the best option for your workflow.

Workflow option 2:

Another option you could use to display information from one base in another, is to join your tables that hold similar categories of data together. Before getting started, we recommend giving this article a read. It may make more sense to import each of your separate tables into the same table, if they all contain the same type of data (all events, or people, or tasks, etc.), with each batch of records added from respective tables categorized through a single select field.

This could be done by exporting each table as a CSV and using the import extension to pull them one-by-one into the destination table. Otherwise, if the field configurations match up between source and destination table, copy and paste may come in useful.

There is no push-button way to relocate a table to another base, due to the fact that a table can be relationally very bound up with other tables existing within a base; this is indeed one of the most powerful features of Airtable. You can download the table as a CSV and re-upload it as a new table in the desired base, but you'd want to configure the columns in your new table to match the original columns ahead of time by adding fields and using their customization menus. Setting field type and formatting is enough -- e.g., if you have a single select field, any values that appear in its cells from a data paste will automatically be turned into field options. You could also use copy and paste to pull data into the new table, after similarly setting up your fields to accept the new data as the right type.

All formula fields will need to be copied over from your old base, because only the output values will transfer over as single line text fields. Other calculated fields will also end up also as text fields, since they've lost their source (a record from another table back in the first base).


Was this article helpful?