Cross-Doc Into Single Table?

Hello there!

Is it possible to put multiple data points from various tables into a single table via Cross Doc pack? I’ll give an example:

Table 1, Doc 1 - To Do at Home:
Project - Pick Apples | Due Date - 6/12/20 | Notes - Only pick ripe ones!

Table 2, Doc 2 - To Buy:
Project - Buy Ladder | Due Date 5/29/20 | Location - Home Depot
Project - Buy Juicer | Due Date 5/29/20 | Location - Target

Table 3, Doc 3 - Errands:
Project - Visit Cidery | Due Date 6/30/20 | Location - Michigan

Table 4, Doc 4 - Master Projects (done via cross doc on to a separate doc):
Project - Buy Ladder | Due Date 5/29/20
Project - Buy Juicer | Due Date 5/29/20
Project - Pick Apples | Due Date - 6/12/20
Project - Visit Cidery | Due Date 6/30/20

Obviously, this is a simplified example. The reason I’m looking for cross doc to one table is that I have multiple docs with various tables that all differ slightly. I’m looking to bring them together for a “Master Project Dashboard”. I have over 100 ongoing projects so putting everything into one doc is not an ideal solution (it would be a gigantic doc). I’ve played around with cross doc a little, but it seems like it’s more suited to bringing in tables to another doc in their entirety. I haven’t yet found a way to bring in elements of multiple tables into a single table.

Thanks!

No. That’s not the [apparent] intended function of Cross Doc.

What you’re describing is Merge Docs and that hasn’t been invented yet although there are some similar mentions scattered in the forum. Ideally, it could be implemented by making the table selector [in Cross Doc] a multi-select option and a lot of changes in the underlying code, of course.

I’m not an expert concerning Integromat (per se) but I believe you can do this by creating the unified table and then use Integromat integration formulas to join the disparate tables (and their common fields) into a unified table.

Another approach - use a scripting language (like Google Apps Script) and the Coda API to read from the disparate tables and write to the unified table. This is essentially what Integromat provides without writing code.

Lastly, there may be a way to use Coda Actions to trigger copying of certain new records from one table to another, thus creating a real-time blending of data from each disparate table to the unified target table.

@Bill_French is right in that Cross-doc doesn’t lend itself well to a “many to one” solution. Its best used in a “one to many” setup. There are some workarounds if you want to sync in tables from those other docs, then run an automation to add the new rows to a combined table. It might become pretty inefficient though and inherently doubles your data.

Having a single doc with all the info where you created separate views for each project, then synced that view out to the project doc would likely be the most efficient setup. But like you mentioned, could be more work than it’s worth depending on what you need from it.

Would it be possible, in my previous example, to merge elements of various tables into one? I’m trying to NOT have a very large table with various blank cells and instead have multiple tables with a few common elements (columns) and some unique to each case.

I guess I’m still trying to figure out database design (or whatever that’s called).

The solution would be to sync tables from other docs into that one core doc you’re talking about. Then you would create a new table in the core doc, and buttons or automations from the others that would copy the rows into it. This would create you one core table to work from, but it would be disconnected from the others.

There isn’t a way to sync from multiple other docs and tables into one table automatically.

I’m sorry, I was just re-reading my comment and realize I wasn’t clear. Would it be possible if ALL tables were in the same doc to somehow merge various columns from separate docs into a separate table?

For my (actual) example, I have clients at various sites, with each site containing various elements. I am attempting to make a large table with all “demographic” information (e.g. date of hire, position, etc.) as well as unique values to each site (e.g. office number only exists at certain sites, and other sites have open office layouts and don’t need this information). That was a vague example, I know, but it’s a bit more complicated than that and the table is extremely large so I’m attempting multiple “micro” views.

Does it make more sense, in terms of database design, do have one large table where certain cells are blank, or to have 2 tables where all cells are filled in? My ultimate goal is to get various macro and micro views of my tasks/projects because I have 100+ projects (of varying sizes) going on throughout the year and losing track isn’t an option!

1 Like

It sounds like all you need is the basic table and then views of that table that show only the info relating to certain values (like demographics) - all in a table and then you make new views in different section dividing it up to see the best of both worlds -

in your words, one large table with some empties is fine - you can filter those out of a view where you want to see only what is most relevant for that intended view.

am I understanding you right @Benn_Bennett?

its a problem when you get to having a monster amount of info - we deal with that and split it between docs only because of the size problem, but its still the right way to set up the information

From experience, these are typical discussion points I hear just before someone says - this is a lot more complex than first imagined.

My recommendations…

  1. Diagram every aspect of the [desired] data and process model. Include details of the data types and the sources and nature of the data.

  2. When documenting the desired data model ensure that you document it based on process and business requirements; do not allow implementation approaches or any Coda database perceptions to influence this effort.

  3. Compare the model requirements to Coda’s ability to implement them; draft a feature gap between the requirements and Coda’s native features; that gap is the list of of things that you will need to build perhaps using the API or no-code integration adhesives such as Zapier or Integromat.

1 Like

@Benn_Bennett Your use case is pretty similar to my pet project in Coda. I once used the “one doc to rule them all” approach which consisted of a mega-table of tasks and milestones, organized by project specific views, or context-specific views, in separate sections and folders of the document. The mega-table didn’t have a lot of empty columns because I stored those in project-specific tables and used lookups/relations to pull in the project-specific data when I needed it. It would have been a great solution except that I ran into performance issues on mobile devices as the table and document grew. One solution was to delete rows that represented completed tasks, but I didn’t want to lose that data.

I’m currently using Zapier to integrate my project management in Coda with my task management in Todoist. Todoist is useful because you can create project-specific views formulaically and “on-the-fly” via URLs. So to use your example, now I might have a “To Do at Home” Coda doc with all the project context (files, key dates using Google Calendar Pack, email, images) and an embedded view of all related Todoist tasks. That embedded view is fully functional, so it feels like a single experience instead of using two separate apps.

And I can also have a Dashboard Coda doc with infinite ability to mix and match Todoist data (by project, by date, by label or any other Todoist search vocabulary), but which doesn’t create performance issues because there’s no longer a mega-table to slow it down.

To @Bill_French’s point, I now rely heavily on a few Zapier recipes that have proven pretty bullet-proof. For instance, one recipe creates a project in Todoist each time I create a project in Coda, then returns the Todoist Project ID to Coda, which enables me to create those formulaic views without any manual cutting and pasting. But I’ve lobbied for a Todoist Pack that would make the Zapier recipes unnecessary. Honestly, I’d prefer to not use a third-party task manager at all and just use Coda, but again, that hasn’t worked for me yet.

JB

Yep - totally agree, but it appears you carefully identified the feature gap and applied a best-available-case to get the automation functional. While I’m not a big fan of code-free integration tools (for many reasons), they are advancing nicely and reliability is certainly improving.

In my view, we shouldn’t have to lobby for any packs; instead, lobby for an open pack architecture and allow [some] integration and feature demands to be satisfied by power-Makers and the after-marketplace. I’m almost certain this is what all Codans want as well. :wink:

2 Likes

Absolutely on the open pack architecture!

1 Like

Lots of items here are part of how a product grows. Launching something early to see how it’s used, what people need from it, how everyone uses it, what’s feasible to support are all questions that we’ve needed to answer. And with everyone participating and providing feedback, we’ve learned a great deal, both in what works and in how to improve moving forward.

Opening up pack creation is an idea that’s been talked about a great deal for a long time. It’s not that Coda hasn’t thought of it, more that it requires a heck of a lot more work and planning. Instead of withholding packs, or any idea for that matter, for a long time until it’s perfect, we can launch much earlier, learn a lot more, and create a better system in the future with what we learn from you all now.

You might have heard the metaphor “use a compass not a map” in terms of sticking to a path and never budging verses finding a direction and adjusting your course as needed along the way. We’re more of a compass company, choosing a North Star and working as a team along with our customers, adjusting our course as needed, to get us all to the destination together.

This is more talking about how the company operates in order to move fast and to find what works for you, the end user. Feedback is great and we’re super happy to have everyone here participating and trying things out. You’re all using Coda in ways we couldn’t anticipate, so this is a journey for everyone involved.

It’s been very helpful seeing how everyone is utilizing Packs!

2 Likes

@John_Beaudoin_Jack wanted to chime in here as you appear to talking about yet another example of a “Source of Truth” solution in Coda, but ultimately one that failed and had to rely on an integration or maybe more accurately, a workaround.

I am trying something similar with a few tables, but I do have a few “mega” tables that are problematic. My issue is that logically what I tend to hear from Coda support and other users is that much of this should reside in discreet tables, but I need to relate a lot of the data, and the more tables I have, the more lookups I need, and that really gets cumbersome in a hurry. This is one of the reasons for a big request of mine:

and a few others that are similar.

And since I think this is a good thread on this issue, and @BenLee you are in here, too, I’d like to add once more that from my point of view, I’d like to see Coda Product Team move towards allowing much of this to be accomplished within Coda itself. To relate to John’s solution, I think it would be easy within one Coda Doc, or Coda “Instance” powered by more advanced Cross-Doc, to have both simple “todo” type tasks as a table, and projects as another table type, and relate them. I don’t think Coda is far from being able to do this right now, and I’d love to see you guys think about further improvements that would continue to allow either 1) ability to build out a full-featured solution via one Doc, or 2) Cross-Doc evolution that would allow a full-featured solution with may docs.

Thanks!

@Benn_Bennett @John_Beaudoin_Jack, @ABp, @Bill_French et al — I’m about to launch a custom pack just for this!

3 Likes

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.