Using views to section out related data in Airtable

• 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?

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

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

• 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

• 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.

• 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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

Matt deletes each of the old tables one by one.

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.

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.

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).