Merge Table Pack

Release notes

Roadmap

Explore

Merge Table Pack

Merge and sync tables from multiple docs into a combined view.

Louca Dufault

Last edited 279 days ago by Louca Dufault

This pack enables you to merge tables from across docs into one combined sync table.

⁠

Install Lite version

⁠

Install Pro version

⁠

Similar to the

Cross-doc Pack⁠

, but with the added benefit of being able to pull in rows from multiple tables at once into a single sync table that merges the values under the common columns!

In this doc and elsewhere, the sync table used to merge tables together is referred to as the Merge table.

Jump ahead to quickly find out

if this pack is right for your use case⁠

How to use this pack

Installation

You can add the Lite or Pro Pack to your doc from its listing (opened by clicking on one of the

Install buttons above⁠

), then clicking to “Add to doc” (or “Try it” / “Buy” for the Pro pack).

If you’re already in the doc, you can simply type the Coda command /merge then select the Merge Table (Lite) or Merge Table (Pro) Pack and click to Add to doc (or Try it / Buy for the Pro Pack). You can also open the Insert menu on your doc, then open Packs, then search for “merge” and follow the same steps.

Setup

Add the pack to your doc (see

Installation⁠

)

Drag the “MergeTable” sync table into your doc from the pack menu, or the “New MergeTable” sync table if using the Pro Pack

Configure the Sync with “Choose what to sync”

Configure which tables to merge by entering the links for the desired tables in the tableLink fields

Configure how the columns will be merged by selecting one of the options for the columnMerge field, or keep the default

Configure any other optional parameters, if desired

Start syncing the table, to begin pulling in the rows from all the tables

Set up the sync table to continuously update at a set interval, or keep syncing with manual updates

Add more Merge tables (if using the Pro Pack) by repeating steps 2-5.

See

Using Packs Tables to sync your data into Coda⁠

for help with sync tables.

See

Documentation⁠

for more info on the Merge table configuration.

Getting a table’s link

You will need to copy the table links to configure the Merge table to merge the rows from these tables. The table links can be retrieved by opening the three dot menu next to the table in the doc, then choosing "Copy link".

You should get a link that looks like: https://coda.io/d/_11rndmchars/Page-name_suAbc#_lu123, that when pasted into a doc appears as the name of the table.

Tips

If the tables you are merging are themselves sync tables, in order to get the latest data you’ll need to refresh each of the tables that are included to be merged before refreshing the Merge table (making sure to wait around 10 seconds). Refreshing the Merge table will not in turn refresh the tables it merges!

If columns you want to merge have different names (that won’t be considered equivalent, see

Column Name Normalization⁠

) in the various tables included to be merged, you can either rename those column(s), or add adapter column(s) to the tables that simply copy over values from those inconsistently-named columns using a Formula, and name the new columns such that they are the same across all tables. You can even hide the adapter columns (as long as you enable the Hidden Columns sync table configuration option)! An example of this is in the

Intersection merge⁠

demo.

If the merging requires some other custom merging logic, you can always use the union merging to pull in all the columns, then add formulas to perform your own column-wise merging logic. An example of this is in the

Union merge⁠

demo.

You can tell which table a row was merged from with the Table Name property that is returned for all rows in the Merge table. Simply hover over any row “chip” (the little box thing) in the Merged Row column in the Merge table, and then find the Table Name property. You can also directly add it as a column to the Merge table by clicking to “Add column”! Pro tip: if you add it as a column, the Table Name can be used in the Merge table’s sorts, filters, groups etc., and Formulas in the table or elsewhere can use this column to choose how rows are treated based on which table they come from.

General sync table tips

You can create any number of views for a given Merge table

Refreshing any of the Merge tables (the base sync table or any of its views) refreshes all the others

Troubleshooting

See

Troubleshooting⁠

How it works

This pack provides a sync table, referred to as the Merge table, that you configure to pull rows from any number of other tables from any of your docs. It will detect columns that share the same name across the source tables, and merge them together under a single column in the resulting Merge table. Each column in the Merge table corresponds to a set of equivalent columns in some or all (depending on the configured columnMerge, see Column Merge section) of the tables. This process follows rules to accommodate discrepancies in the naming of the columns (think upper case vs. lower case), and to resolve different column types.

Say you configure the Merge table to merge Table 1, which has a column named “Date” of type Date, and Table 2, which has a column named “date” (notice the lower case spelling) of type Date and time, the resulting Merge table will have a single “Date” column of type Date and time, which is how we would naturally expect the merging of those two tables to occur. This process follows rules to merge any tables together in a predictable way, which are explained in

How it works (in depth)⁠

. One is that the “wider” of the two types, Date and time in the example above, that accommodates both column types without any loss of information, will be chosen for the resulting column. See the

Column Type Resolution⁠

section. The other is the process that determines whether two column names are equivalent, which decides which the columns will be merged in the resulting Merge table, does so by performing

normalization⁠

on the column names. See the

Column Name Normalization⁠

section. The last rule is that in cases where two or more columns in the same table would be considered equivalent, only one is chosen from that table to be considered in the merge. See the

At Most One Column⁠

section.

Special columns

There are a few special columns that are always included in the resulting Merge table, representing metadata to keep track of where the rows were taken from. These columns will overwrite any equivalently-named columns in the tables included to be merged.

“Row URL” is an absolute link to that row in its source table

“Row Name” is the name of that row as specified by its value for the display column in its source table (even if that display column was not picked up by the merge)

“Row Link” is a Mardown-formatted link to that row in its source table, with the “Row Name” as the link preview, and the “Row URL” as the link URL.

“Table Name” is the name of the table where that row was pulled from, which can be used to discriminate the row’s type/provenance

The “Row Link” column can be added as its own column in the table, to support use cases where having the default sync table row

chip⁠

as the display column is not desirable because of the different UI this presents (which includes the pack logo).

Is this pack right for you?

The motivation for this pack comes from the common use case of having neatly structured data in various tables, that share some columns in common. These similar tables would benefit from being viewed together, but are different enough to warrant keeping distinct, or are outright impossible to combine.

These are the main use cases this pack enables:

I have different tables that serve the own purposes but share some similar columns, and I want to combine them all into a single table, so that I can view those similar columns for the rows of all those tables together.

and

I have many different copies of the same (or very similar) table distributed across different pages or even different docs, and I want to combine them all into a single table, for presenting / bookkeeping / external integration / analysis.

Much of this is discussed in this community topic, which sparked the creation of this pack:

⁠

Polymorphism would be nice: a way to join tables with common columns under a parent or generalized table The desired functionality is to somehow have an umbrella table that would join together (in the SQL sense, i.e. to merge the rows of multiple tables on some common columns) multiple otherwise unrelated tables. The intention being to: Aggregate all rows from multiple tables into a view that presents the row values for the common set of columns. Considering this view of the rows as a more abstract or generalized table, to reference this table within formulas and lookups. This has been previousl... community.coda.io⁠

⁠

Different tables with some common columns

An example (more or less the same as in this doc’s

Demo⁠

) could be having tables for Tasks and Projects. Both might have names, dates, people (assignees or owners), priorities, and any number of columns in common. There are valid reasons for keeping the data in two separate tables. For one, conceptually it doesn’t make sense for two unrelated concepts to share the same table, merely because they have some aspects in common. It also makes managing any logic on the table, such as formulas, defaults, conditional formatting, etc. quite unruly. On the other hand, having them both in the same table would enable viewing all the tasks and projects together, for example to see all task and project dates in a single calendar, or view the tasks and projects for a person.

It might sometimes make sense to unify the tables, maybe because the concepts are similar enough, the data complexity is low, or there is no logic applied in the table. Aside from unifying the tables, the only other natively supported approaches are:

build a formula that reads the rows from each table, extract what is relevant (or common), and generate new rows in another table that mimic the original ones

⁠

normalize⁠

the data by factoring out the commonality into a separate table referenced by each of the similar tables

The first approach requires writing a fairly complex (and highly custom) Formula that reads all the rows from each table, extracts the relevant (common) columns, then creates new rows in a separate table (which you would also need to create), with back-links to the original rows in their tables. It can be done, but it’s non-trivial (and requires abusing the column reference types to get to a tenable result).

The second approach requires completely redesigning your tables as well as re-creating the rows (same goes for combining the tables, especially tedious if there are already many rows).

In fact this pack pretty much implements the first approach, although without needing to handcraft some complicated Formula to implement it.

Similar tables that must remain separate

There are also a number of use cases where data in the tables inherently has distinct sources, and cannot be redefined to a single table. For example, one might have docs for many clients, each with their own Invoices table (for security reasons). Visualizing all the invoices in a single doc would require using the

Cross-doc Pack⁠

to pull in each of the Invoices tables individually. The approaches discussed above to combining the rows from all the Cross-doc tables suffer from the same drawbacks.

This pack provides a straightforward way to collate all the tables across the separate docs into a single table. The only manual step to include a new table to merge is copying its link!

Features

Some of these features are only available in the Pro Pack, see

Lite vs. Pro Version⁠

for the full feature availability.

Interoperability with ✨ Cross doc ✨ and other Sync Tables

This Pack supports merging together Sync tables from the Cross doc Pack. It will also automatically strip out the “Row” identity column from the resulting merge table (unless it is no longer the display column) as it is usually redundant. Note that if your goal is to merge tables that are found in another doc, this Pack already supports doing so without the need for using Cross doc sync tables as an intermediate.

The same applies for merging together other Merge tables, where the “Merged Row” identity column is omitted if it would not result in losing the row’s display name. Note that if your goal is to merge together more than two tables, or merge such that all the columns (even those not common) are picked up, this pack already supports doing so without the need for using multiple Merge tables, with the tableLinks sync table parameter, and the columnMerge sync table parameter set to “union”, respectively.

Lite vs. Pro Version

This pack is distributed under two different versions with their own Pack listings: the

Merge Table (Lite)⁠

Pack and

Merge Table (Pro)⁠

Pack. The Lite Pack is offered for free, and includes most of the functionality, with some limitations. The Pro Pack includes all of the functionality and is offered as a paid subscription.

Lite

Pro

Number of tables that can be merged together

unlimited

Number of Merge tables per doc

unlimited

Merge tables from the same doc

✅

Merge tables from any other doc

❌

✅

Merge Cross doc tables

❌

✅

There are no rows in this table

⁠

If switching from the Lite to Pro version, you will have to recreate and setup the sync table. Try the Pro Pack for up to 14 days in a doc for free!

Documentation

See

Documentation⁠

Documentation for the pack can be also be found in the

pack listing⁠

and in the sidebar menu when configuring the pack’s sync table.

Limitations

Unsupported column types

Columns of the following types will always be omitted from the merge:

Reaction

Button

This is because a) the Coda API used to retrieve the row data does not provide enough information for values of these types to recreate them, and b) Coda Packs do not support returning information in these formats in sync tables. See

Return data with meaningful types - Coda Pack SDK⁠

This may change in the future, although unlikely.

Lossy column types

Columns of the following types will be included in the merge, but will not include the column metadata:

Select list

Formula (i.e. columns that use a formula to produce their values)

Columns of these types will be treated as no more than the value in the cell of the rows for that column (the selected Select list option, or the value produced by the Formula), but the column type-specific information will be discarded, such that the resulting column in the Merge table will appear simply as the selected/produced values in their resulting type (as resolved). As such, there will never be any columns in the resulting Merge table with Formulas, or of the Select list type.

This is because Coda Packs do not support returning information in these formats in sync tables. See

Return data with meaningful types - Coda Pack SDK⁠

. This may change in the future, although unlikely.

A special case is Canvas type columns, which will not lose any metadata per se, but will appear in the resulting Merge table as Text type columns (with the canvas body recreated as a best-effort, YMMV). You can opt to change the type of the resulting column back to Canvas.

References (fka “Lookups”)

Pack sync tables do not support references for non-Pack tables. This means that columns in the resulting Merge table cannot reference other tables in your doc. Any lookup columns in the tables included to be merged that are picked up in the Merge table will appear as broken lookups by default (same as in Cross doc tables), or can be set to appear as links to the looked up rows in their source table for ease of navigation.

Community Discussion

Most of the discussion surrounding this pack and its development can be found on the Coda Maker forum under the topic here:

⁠

Launched: Merge Table Pack The Merge Table pack enables merging and syncing multiple tables across docs into one combined sync table. Similar to the Cross-doc pack, but with the added benefit of being able to pull in rows from multiple tables at once to merge their values under common columns! Useful for combining multiple tables that share common columns, for example merging Tasks, Meetings, and Projects tables into a single Calendar layout table showing the names, type, and relevant dates for each. See the pack listin... community.coda.io⁠

⁠

Feel free to join in on the conversation, or share your use cases elsewhere in the community. Additionally, if one of your doc makes use of the pack and can be showcased publicly, please make sure to publish it so that it can be highlighted in the pack listing for others to see!

Support

If you have any questions, concerns, or problems while using the pack, feel free to comment with your situation on the

Coda Maker forum topic⁠

Please don’t hesitate to report any issues you have encountered while using the pack!

Contact

Reach out

louca.developer@gmail.com⁠

for all other inquiries.

Note: this email is not regularly monitored. Please opt for the aforementioned support channels if possible.

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.