If you create a column called “# of Descendants”, set it to select rows with Parent.contains(thisRow) and then sum their # of descendants +1 each, you’ll get an automatic recursion down to the bottom.

Num Of Descendants Column Formula: ThisTable.Filter([Parent Item].Contains(thisRow)).[Num of Descendants].Sum() + ThisTable.Filter([Parent Item].Contains(thisRow)).[Num of Descendants].Count()

I have to bow, congrats for this efficiency @Billy_Jackson !
I got stuck in my reasoning when I said myself “I have to count until there is no child any more”, and that led me to the while loop. Sometimes brain not enough open-minded !

@Fran_Vidicek, I set Billy’s MUCH BETTER solution in the embed doc also

Of course, given the simplicty of it, do use it !!

I already tried something like this, had the same idea but it only went 2 levels deep and now I got the same result, but I might have done something wrong.

Give me a minute, I will try again.

Oh, ok I see now, thanks so much @Billy_Jackson !!! That’s perfect!

I don’t think the formula works, I believe you accidentally referenced to a wrong column.
Because if I delete the Number of Descendants (Desired Result) column, Billy’s Solution is an error.

I think there is an extra column at play here. The key to this working is that the formula is summing ITS OWN COLUMN. So in this case, it should be summing “Billy’s Solution” rather than a separate filtered column

Yes it’s from the “three chart” doc. I went there for solution or ideas at first because it deals with similar structure . I didn’t find the solution but it proved itself useful for visualizing the issue .

However now I have another problem/question if anyone is interested in helping solve.
It's similar to precious problem but goes a level further:

Imagine we have boxes. Each box can have boxes and toys inside of it. The toys can be inside other boxes or in the box itself.
How would we count total number of toys inside a box?

I’m assuming this time It won’t be possible to create a “non-dynamic” solution and we will have to approach it in a way that is more similar to @Quentin_Morel 's solution here.

I think you can actually still take a similar approach.

We’re looking for Total Number of Toys inside box = Toys Directly in Box + Sum of toys directly in boxes of all descendants.

As we’ve seen, it’s pretty straightforward to calculate either the list of descendants (as per Paul’s solution) or the sum of items within them.

Here, you’re looking to create a column Total Toys Within = Toys.filter([Parent Box].contains(thisRow)).Count() // Total Toys directly in box + Boxes.filter([Parent Box].contains(thisRow)).[Total Toys Within].Sum() // Sum of all toys in descendant boxes

The key principle here for both of these examples has been to create recursion without writing for/while loops by writing a formula that references other iterations of itself.

So RowA.RecursionFormulaColumn = functionOf(rowN.RecursionFormulaColumn)
This rowN is - itself - a function of other iterations of that column, so on and so on, as determined by the parent/child structure.

I don’t know how Coda’s formulas are implemented, but my best guess is that they are almost certainly performing a version of Quentin’s solution, but on the back end of things.

Coda allows for recursive behavior. You can reference the same column within that column’s formula but only as long as you’re reading the values through a lookup somewhere.

See in my previous message how “Cumulative” is calculated with the use of “Cumulative” of the child nodes.

Here’s a clip from one of my lectures where I’m demonstrating the recursive behavior with digest cycles (aka recalculation passes). I was solving this problem btw. Watching at 1.5x advised.

P.S. I think you can totally apply the same thinking to solve it even if you have multiple tables there at play. This demo in the video actually had two tables interplaying so that @Connor_McCormick1 would accurately describe the composition. I always say it’s primary that you understand the principle, not just the formula someone gave you in the community