How to calculate the total number of descendants of an item?

[Quentin_Morel]

Well, let’s go @Fran_Vidicek, lock your belt . It’s a bit tricky, and may be @Paul_Danyliuk will find a non-dynamical way to solve that. But it works !!!

We will use a while loop, each step will iteratively calculate the child(s) of items, as long as there is at least one child. We will calculate the sum of the child and increase the total number each time. Once the child column is blank, the while loop will stop.

---

Here is what it looks like

I need :

• a Start button = Init + Run
• An init button = that will set the first Child
• A Run button : that will change the child list to next generation, and update the total number
• A loop : that will trigger again Run button as long as there are children
• A condition (checkbox) column : that will check if there are still children

To process that :

Start by setting an initalization column. This will find, for each row, what is the first level of child. We need that before we start our loop, and we store thoses values inside ChildProcess column. But as we want to initialize only once, we need to put it outside of the loop. You can see that we initialize also the number of child.

If I show you this step only, this is the result i got with only “initialization” process.

OK, then the core of the process is in the Run button, the code is here but please check it directly in embed doc for better view

RunActions(
  WithName(
    thisTable
      .Filter(
        [Parent Item].Contains(thisRow.ChildProcess)
      ),
    Child,
    RunActions(
      ModifyRows(
        thisRow,
        thisRow.TotalChild,
        thisRow.TotalChild +
          if(thisRow.ChildProcess.IsBlank(), 0, Child.Count()),
        thisRow.ChildProcess,
        Child
      )
    )
  ),
  thisRow.Loop
)

In a few word, this action will :

• Define what a child is : a child is an item from this table that contains current childprocess row. Step by step, we will check child, great child, etc…
• Modify the current row : we replace child process list with new child, and we add the total number of child from the begining to now.
• We call a Loop action → See next point

Now, we want to check if we still have child. If not we stop, if yes we continue. Then, I created a column Condition which only check if the child column is empty

Now, the loop button will call Run action again only if the condition is to false

This is really the core of the process :

• Initialize
• Update child list and add count to the total
• if there are still childs, continue, otherwhise stop it

Define a Start Button, that will initialize and start the first run

Finally Add a master button that will activate each start button !

And now the magic happens !

I truly realize this require a good level regardinf formula, but do not hesitate if I need to add some explanations

Find embed doc bellow