Distribute nested values over rows

Simplify your Coda formulas using a button

In the community an interesting solution was provided by Micah Lucero for what seems to be a complicated problem. You have per cell multiple values and you want to distribute them over new rows in a target table, while keeping the link with a unique identifier like the row Id or the creation moment. By the way, both are numbers.

I have seen the need for this logic in product domains, like the sales via Shopify. Or in a Bill Of Material logic when an item is sold, you want all related items taken out of your stock. In the community the question was about work tickets. They all share the idea that multiple parts or items are living together in a single cell.

The provided solution by Micah Lucero is concise and elegant and worthwhile your consideration in case you come across a comparable challenge. What he offered is based on the insights of Coda you find here.

We evaluate this code snippet in 3 parts.

code snippet

Part 1

The first part of the code checks if the row id living in the source table lives also in the target table. The reason to start with a filter on the source table is to check if we have rows to add to the target table. That is only the case when the row id’s are not yet in the target table. The related mental phrase is something like: filter the source table to check if it contains id’s not living in the target table. In case Id’s not living there, show me the list of id’s.

[DB Source].Filter(ID.contains([DB Target].ID).Not()).ForEach(CurrentValue.WithName(parent,parent))

When the table DB Target is empty the result is a list of 3 id’s and since they live in the display column, they show with the @ in front, if they are all already in the target table you see an empty list. In java script an Array : [ ]

the outcome of step 1 and step 2

Part 2

Since the outcome of part one is a list of objects, we chain this object we named ‘parent’ with category, which is a list of items living in their own table. This list of items we evaluate again, it is the second time we apply forEach().

parent.Category.ForEach(


)

Part 3

We add the rows and we fill out two columns: ID & Category.

Both get values we created via the double forEach() we applied, the parent.ID and the currentvalue

[DB Target].AddRow(
        [DB Target].ID,parent.ID,
        [DB Target].Category,CurrentValue)

So far the excercise has been instructive and insightful. It made me think about a situation you have table that gets updated via an automation or a pack (a sync table).

Below how that change could look like, before and after.

what if we have add categories to the source table

An additional question

How can we only distribute the new values. In row one it is Chicken, in row two and in row three there are no changes. In row for we have Cow & Horse as extra. You want to know what already lives in the target table before adding something. This additional requests requires a filter.

It was the great Coda connaisseur Joostmineur who solved this puzzle. You can find it in the blog.

Nested loops · Prefill a table

My name is Christiaan and blog about Coda. Since the summer of 2023 often about how to Coda with AI to support organisations dealing with texts and templates. The latest Coda AI update was on Dec 7, 2023.

Updates to Coda AI credits, analytics, and features

Why I focus on Coda AI you can read here: ⤵️

Why I focus on Coda AI?

I hope you enjoyed this article. If you have questions feel free to reach out. Though this article is for free, my work (including advice) won’t be, but there is always room for a chat to see what can be done. You find my (for free) contributions to the Coda Community and on Twitter.

Coda comes with a set of building blocks ー like pages for infinite depth, tables that talk to each other, and buttons that take action inside or outside your doc ーso anyone can make a doc as powerful as an app (source).

Not to forget: the Coda Community provides great insights for free once you add a sample doc.