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 listing for more details and documentation.

See this community thread that details some of the motivation behind this pack, as well as a more technical discussion of the design decisions behind this approach.

The currently released Lite version is limited to a single merge table per doc and merge tables are limited to merging two source tables from the current doc. The Pro version is already finished but not yet released. Stay tuned!

Implementation details

Merge Table Ordering

Rows are populated according to the natural sort ordering that the rows appear in the specified table (or table view) in the doc. Note that the order in which tables included in the merge will have their rows populated into the merge table is not defined.

Column Consolidation and Type Resolution

This pack reads the schemas (i.e. the column names and types) of the tables included in the merge to determine the schema of the resulting merge table. It does so in a deterministic way according to the following strategy:

  1. Group together all columns with the same (normalized) name
  2. Depending on whether the column merge is “union” or “intersection”, keep all columns groups or discard the groups where the columns are not common to every table, respectively
  3. Resolve each column group to the most specific type encompassing the types of all columns in the group*, falling back to the Text type

*this step is somewhat opinionated, as the value types available for pack schemas as well as the type formats used by Coda do not expose any specific hierarchy (nor do they exactly map against each other). The hierarchy used is chosen to mimic the expectations for how types resolve across various end-user systems in Coda, such as the Coda Formula Language. For more details about the resolution hierarchy please contact the developer.

Special Properties

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 override any same-named columns in the tables to be merged.

  • Row URL is an absolute link to that row in its source table
  • Table is the name of that row’s source table, which can be used to discriminate the row’s provenance
Current limitations

Although it would be possible to enable configuring individually whether hidden rows and/or hidden columns should be included in the merge, currently the Coda API only allows for an “all-or-nothing” approach (see this community thread). This means that in order to support the more common use case of enabling table views to be used in the merge to filter hidden rows from their “parent” tables before the merge, we cannot support including merging hidden columns.

Including hidden columns in the merge would be desirable to allow for adapting certain table columns to the merge, in a hidden way. For example, with a Projects table with a “Deadline” column, and a Meetings Table with a “Date” column, clearly the intent would be to merge both tables on the common Date type column. However, they cannot be merged unless their names match. The workaround is to create an “adapter” column in one or both tables with a common name that simply copies the respective column from either table. In this example, that could mean simply adding a column named “Date” to the Projects table, whose formula would be [Projects].Deadline (simply a copy.) With this, the tables can be merged as expected onto a common “Date” column. However, this requires that the “Date” column not be hidden in either of the tables.

An alternative would be to instead merge the tables columns with the "union" operation, which would copy over both the “Deadline” and “Date” columns into the merge table in the above example, and deal with merging them manually in the merge table with some additional formula column. This keeps your source tables lean, but adds overhead in computing the common column manually in the merge table.

5 Likes

Demo Doc!

2 Likes

Merge Tables is a part of my Sync Tables Pro pack, just saying :wink:

It was announced and implemented in the first demo and didn’t yet make it for the soft launch. It’s 99% ready — I just wanted to examine it for the ways to make the UX better. So it’s coming sometime this week.

The plan for the feature is to merge any number of tables, allow any number of merge tables in a doc, and also have all the benefits of Sync Tables Pro pack such as row-level security and swappable sources.

P.S. I’m sorry I missed your thread. I would’ve notified you that I was working on it too.

This looks amazing!! Thanks for you hard work on it - can’t wait to try it out.

2 Likes

This is great, looking forward to trying it out. Does it work for tables with formula columns?

1 Like

It does work in the sense that the result of the formula will be properly transferred into the merge table, but unfortunately it is not possible at the moment for a pack to set the formula itself on a column.

Probably the best this pack could accomplish would be to include the formula as a string in the generated column name, so that it can be copied over more easily. Would that be useful in your case?