How to replace tables with connected views

If you find that your doc has multiple tables with similar data, it's usually better to organize them as views of each other. By replacing separate tables with connected views, you will save time and effort by automatically syncing any changes made to the data to all views. Plus, you'll gain more flexibility in analyzing the data, as its much easier to query a single table than it is multiple. In this tutorial, we'll show you an efficient way to retrofit a doc and combine independent tables into views.

Step 1: Compile a list of tables you want to connect.

Tables that would make good connected views are the ones that share a common noun. Learn more about deciding if a table should be a view here.
Consider using the Doc Explorer Pack to quickly pull a list of all tables in your doc. Add a checkbox column to mark which tables you plan to turn into connected views. Once you’re confident you’ve checked off all the tables to combine, add another filter to show only the rows you’ve checked off.

Step 2: Create a new base table

This will be the new table where all the rows from all the separate tables will be added. Your new views will be views of this table. It’s best practice to create a “backend” for your doc, where you store this main table. No one will actually see/use this table, it will only be used to create views from. Add all columns that appear on ANY of the tables you’re looking to combine. It’s ok if only one table has a column and all the others do not, add it anyways. We’ll talk about hiding unused columns in a later step.

Step 3: Create a tagging/filtering plan

Each of your views is going to be a filtered version of your main table. This means you need to figure out what parameters you’re going to filter for to pull the right rows for each view. A simple option would be to explicitly state which views a row should appear in via a select list column. The select list options would be the name of every view you plan to make. For each row, set the views you want the row to appear in. In each view, filter for rows that have that view listed. This is by no means the only way to approach filters. Check out this tutorial for a deep dive into how to create a tagging/filtering system.

Step 4 - Create the views

Go through your list of tables, and make a view of your new base table for each one, adding a title to each. If you want your title to be the same as the original table, consider appending the original table’s title with “-original”, as tables/views in Coda must all have unique names. For each view, apply the appropriate filter based off your plan from step 3. Hide any columns that are not needed.

Step 5 - Add your rows

Going view by view, copy the rows from your original table to the new view. If the columns are in the same order, you can bulk select all rows/columns and paste everything at the same time.

Step 6 - Check for duplicate rows

Return to your main table and check for any duplicate rows. Duplicate rows are created when you had a row listed in two or more tables. Check out this tutorial on how to quickly find duplicate rows. If you find duplicates, select one row to keep, and adjust the column values to ensure it appears on all of the correct views, per your filtering system. Then, delete the other rows.

Step 7 - Delete the original tables

Compare each view to it’s original table. Once you are satisfied that all the rows are there, delete the original

Replacing tables in a high traffic doc

Replacing tables with views in a frequently used doc can be a challenge logistically. Here are some tips to make the transition smoother

Do steps 1-4 discreetly

Create a hidden page in the doc where you create the new base table and all of the new views. Because the page is hidden, these new tables/views won’t impact or confuse the users who are using the original tables.

Freeze operations for the final steps

When you begin adding rows to the new views, you need to avoid users adding/updating data in the original tables that you’ve already copied over as this can result in data loss. Plan and communicate a cutover date for when users stop adding new data to old tables. Check out this guide for a deep dive into making the transition smooth and minimizing disruptions in workflow. During the cutover period, users will cease making changes to table content. You then can move the new views from hidden page to their live pages and complete the row transfers, steps 5-7. When all original tables have been deleted, relaunch to your team.

Was this helpful?

YesNo