How to use nested ForEach to flatten out job tickets with multiple categories

[Uri_Budnik]

i think i am in the right neighborhood on how to do this, but i am stuck

i have a table with work tickets. the columns are ID and Category

a work ticket can have multiple categories. my goal is to flatten this out into another table such that each work ticket ID has only one Category. So, if work ticket 123 has 2 Categories in the source table, it will be 2 separate rows in the target table, both with the same ID in the target table. then we can do some reporting on the target table

this is a sample of the source table:

and this is a sample of the destination table:

i am partially there, but i cannot figure out the compete answer

i can filter by just the tickets i want to flatten and i can pull out a single object per ticket but still with multiple categories. i can count how many categories are there, but i cannot figure out how to nest a ForEach to then add a row to my destination table for each of those categories

here is a sample of what the above looks like:

i think the solution involves nested ForEach and probably WithName, Sequence and Nth.

here i made a list showing how many categories each job ticket has so that i can use Nth to loop over that. but i can’t figure out how to combine all this and get it to work

There are similar questions here (i search for ‘nested ForEach’) and even an example from a News from Coda article from 2020-12.

nevertheless, i am stuck at the moment

any help from an expert user is much appreciated

[Rickard_Abraham]

Didn’t read it thoroughly but perhaps it’s ListCombine() you’re looking for? It recursively flattens nested lists

[Uri_Budnik]

thanks but ListCombine does not help because i need to repeat the Ticket ID for each Category and ListCombine is just a simple union of sets

[Micah_Lucero]

ForEach(
  Source.Filter(ID.In(Destination.ID).Not()),
  WithName(
    CurrentValue,
    parent,
    ForEach(
      parent.Category,
      AddRow(
        Destination,
        Destination.ID,
        parent.ID,
        Destination.Category,
        CurrentValue
      )
    )
  )
)

[Uri_Budnik]

Micah,

thank you SO MUCH

this is exactly what i was trying to do.

your solution is very clean and very elegant. all i did was replace the table and column names.

many thanks and happy new year to you

[Uri_Budnik]

and thanks for putting together an example coda doc with the solution. that was super

[Uri_Budnik]

Follow up question @Micah_Lucero,

There is one more level of iteration. Job tickets can have one or more Categories (as stated in the original question) AND when the category is Order Dashboard they can have none, one or more SUB-categories

This is a sample table

My question is, in your example, how does CurrentValue determine what column to insert? Isn’t it a list with the whole row in it?

i tried adding one more iteration level with ForEach for Subcategory, but i am getting an error. I think it is because i am not able to successfully refer to the Subcategory inside the ForEach loop and i am trying to insert the wrong value into that column. I am not completely clear on what CurrentValue contains

There is not much documentation on CurrentValue that i can find

Thanking you in advance,

-Uri

[Micah_Lucero]

I’m not sure how well I can explain this, but essentially…

Anytime you use a formula ‘foreach’, you’re presented with the currentValue. This is because ‘foreach’ goes through every different list item and then processes whatever logic you have within the ‘foreach’ expression.

So, anytime you use the ‘foreach’ formula, it’s going to return the currentValue.

The only issue is, for one ‘foreach’, you can only have one current value. Since we’re nesting multiple current values, we run into a bit of an issue because we can only have one current value. To fix this, we have to use the ‘withName’ formula so that we can store the original parent foreach value and reference it later on in the child foreach. Essentially, ‘withName’ allows us to store variables.

In your case, from what I understand, you’re trying to create a new entry for each category. And if there are subcategories, you also want to create a new entry for those subcategories as well, with the same ID that it’s originally on.

In this case, we don’t really need to add more logic or any more for each formulas. Rather, we need to flatten out the categories and the subcategories as one. So, we can use a simple ‘listCombine’ to merge the subcategories with their main categories into one list, and then just insert that into our formula.

[Uri_Budnik]

@Micah_Lucero

In your case, from what I understand, you’re trying to create a new entry for each category. And if there are subcategories, you also want to create a new entry for those subcategories as well, with the same ID that it’s originally on.

yes, but slightly different than what you say above. i don’t want to mix Category with the Subcategories

thank you for updating the Coda doc, but, the script combines the Category and Subcategories values as it flattens them and puts them all in the Destination table under the Category column

what i need is for the destination to also have a Subcategories column. I guess i should have put a screenshot of that too

the highlighted part of the screenshot

the result is not

ID Category
4 Horse
4 Neigh
4 Moo

but

ID Category Subcategory
4 Horse Neigh
4 Horse Moo

i hope i am making it a bit clearer

[Micah_Lucero]

How would you want the result to look for this case?

[Uri_Budnik]

depends on what is possible @Micah_Lucero

we really care the most about the Category Order Dashboard because that has the most Subcategories and we do most of the tickets there.

but, other Categories also have Subcategories.

i added the original Category and Subcategory tables to the document you created (and shared in a prior message) to make it easier to explain. you can see the relationships there. any Subcategory can only belong to a single Category.

so, in the example you screenshotted, the 2 Subcategories can only belong to Order Dashboard.

now, to answer your question more directly, i was thinking that as the ForEach loop is flattening each row, when the current Category is Order Dashboard then also do a nested loop to flatten out the Subcategories within (if any). In other words, several rows of Order Dashboard as the Category each with its own Subcategory. That is what i was trying to clarify in the last message.

Therefore, in the example from your screenshot, both Subcategories (Documents Panel and Expenses) only need to be flattened with Order Dashboard as the Category. No confusion with the other two categories in the ticket.

---

we can assume that when any of the Category in a ticket are Order Dashboard all Subcategories in the ticket belong to Order Dashboard

---

Its a rare corner case to have two Categories each with its some Subcategories so we ignore it. We really only care about Subcategories for tickets that are in the Order Dashboard Category

what i am after is to be able to create two reports; (1) what Categories did we work on and (2) when the Category is Order Dashboard, what Subcategories did we work on.

The last time i did this, i exported the data out of Coda to flatten it out, i think it would be much cooler to keep it all inside of Coda

This is the end result i am after:

and since Order Dashboard is so big and has so many Subcategories, we like to break that down too

notice that 11% of Order Dashboard tickets don’t have a Subcategory, this is valid. Sometimes the Category field is Order Dashboard and the Subcategory field is blank

The only other Category that has multiple Subcategories is Settings and we do not do a lot of work there

our goal is in bold above, but now that i am explaining this, i am wondering if it is not simpler to flatten out all the Subcategories. at the top of this reply i said that “it depends on what is possible”.

Is it possible that when a ticket has three Categories: Order Dashboard, Labels and Settings; and two Subcategories: Documents Panel and Expenses (like in your screenshot); that Coda can use the relationship from the tables to know that Documents Panel and Expenses are connected to Order Dashboard and flatten them out properly?

now, flattening out all the Subcategories is not important. we only care about the Subcategories for Order Dashboard. i am only mentioning this second idea in case it is easier to flatten all the Subcategories than just the Subcategories for Order Dashboard

i hope this explanation was not confusing