Recursive Row Copy Across Tables - Is There an Easier Way?

Alright new-guy warning, I’ve been up to my ears in Coda for a few days now and I LOVE it, there’s just a certain style to how they handle data and formulas which jives with my brain.

That being said, what I’m up to is trying to build what’s commonly referred to as a ‘takeout menu’ list of pre-built content which we can stick together more quickly to respond to potential customers.

Challenge is, I tend to over complicate things and therefore want some nicely groomed tables to ascribe to different project phases, tasks, deliverables, etc…

So when it comes time to put together an ‘order’ or a proposal from this content, I need to copy some of these rows which are conceptually hierarchically related and rebuild all of those relationships within the copied rows across those tables. Here’s a diagram:

I tried to handle this a number of different ways:

  • Building out template pages which would be copied and each have their own table set, ran into conflicts between ‘duplicate relationships’ and ‘make a copy’ options when copying pages.
  • Trying to manually just write the formula by chaining .FormulaMap( ) and .RunActions( ) 9 times, became unwieldy and fragile.

As a developer I just needed to traverse a graph and recursively copy children from any node I wanted to duplicate, and based on these two posts:

I think I was able to extend @Paul_Danyliuk 's method of chaining ‘descendants’ up the hierarchy so you have a single field you can loop through.

A general overview of how this works:

  1. There’s a Tree table which the columns formula query all of the records from all tables I need to do this deep copy trick with. So something like ListCombine(Phases, Tasks, Options)
  2. Pretty much every table has a Key and an ID column, the key is used to signify rows which are part of a particular deep-copy run, and the ID helps me identify ‘Task 1’ after I copy it over to rebuild relationships.
  3. I use @Paul_Danyliuk 's recursive pattern on this tree table so each row has a cell which has all of the entries below it on the tree which I can iterate through and copy
  4. I have a helper table I use where each row is for a particular copy where I can select a new proposal and any items I want to copy, a button on that table actually does the copy actions and deletes the row.
  5. After I’ve copied the tree structure over, we look at the ‘nodes’ on the tree that were made and create the real-table copies of those new nodes.

Tree Table

When I get the ability to post more than one embed I’ll add shots of the helper table and the formula.

I’m a true masochist, somebody come tell me how I could have done this forty times easier :joy:

Okay, finally found the moment to understand this :slight_smile: Thanks for the shoutout btw.

If I understood correctly, the task is to clone the tree of rows from the same table (i.e. a structure of tasks/subtasks of arbitrary nesting from the same table).

I wouldn’t create a column to calculate all descendants or anything alike. Two reasons: it wouldn’t help me (not the easiest solution), and it would be atrocious for performance when you start adding rows.

First, I’d create an iterator table to put the logic there. It’s just easier to code in a more traditional algorithmic way when you use an iterator table.

Second, I’d recursively copy it top-down, storing the current source path and the destination path so that I could connect the copy to the copied parent, and walk up the hierarchy on the way back.

I could implement this for the showcase… but I just realized that actually it’s not a single table — you’re duplicating stuff from different tables? If that’s the case, it would definitely involve some conditional logic to resolve which table and which column should be used on every particular “rewiring” occurrence. Architecturally I’d probably do something similar to what you did — but I wouldn’t clutter the data tables but would create a separate table, dump all the rows there, clone the rows, and then reconnect the clones using that table as the datasource of connections.

1 Like

Thanks for the reply! Yeah the multi table bit got tricky, and it pretty quickly turned into a performance issue with that many lookups back and forth with the tree table and all the others.

What I ended up doing is just copying data to a new unified table for epic, task, etc descriptions which seems a bit cleaner and is a workable solution for now.

Last night I did catch on to some of the folks who are using three separate buttons to actually implement recursion and get around the circular reference check, so after I get this ‘mvp’ across the finish line I may circle back and see if there’s a cleaner way to accomplish what I originally wanted to do.

The one maybe interesting thing I think I did was using a named formula to listcombine all of the rows from all of the tables I needed so I could .nth through it, but that’s probably not an idea the community would want to proliferate :joy:

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