Help me with ListCombine()

[Fran_Vidicek]

If you want to play with the document this is the one.

I can’t for the love of god figure out how this formula works:

This is what the table looks like:

And this is the Gantt Chart view of the same table:

So the first formula ListCombine(), merges all of the values inside [Immediate Children] + [All Downstream Tasks] + [Immediate Children] columns, but why would it repeat a same column twice…
Or does it combine values from [All Downstream Tasks] and [immediate children] columns, but then that makes no sense because there is no data inside [All Downstream Tasks] column yet.

I absolutely don’t understand what is going on in this first formula.
Thanks in advance!

[Nick_HE]

I agree it seems a bit circular

Is it clear to you though how the dot reference is working there?

thisRow.Immediate Children.All downstream tasks means:

• Look at the “Immediate Children” cell for this row
• All the items in that Immediate Children cell are other rows in this table
• For each of those other rows, grab what’s listed in their All downstream tasks cell
• Each of the things listed there is also a row in this table. And those rows are what this expression is ultimately spitting out. In other words: we’re looking at our main row, and grabbing all rows that are the main row’s children’s children (and children’s children’s children, etc)

And then the second part of ListCombine, thisRow.ImmediateChildren, grabs the immediate children. Cause we haven’t actually grabbed them yet (we’ve only grabbed the children’s children).

For what it’s worth, I think this would be more readable if the order was swapped:

ListCombine(
  thisRow.Immediate Children,
  thisRow.Immediate Children.All downstream tasks
)

i.e., grab this row’s children, and then also grab the children’s downstream tasks (i.e. their children and grandchildren)

TL;DR

• Think of it as combining a list of rows from this table, cause ultimately all the things found in these cells are references to other rows in this table
• The dot operator is referencing properties of other rows

[Fran_Vidicek]

That is a great way to explain it, helped a lot.

–

The main thing I didn’t understand was the way dot operator first does what is behind the dot and then what’s after, therefore the cells in the All downstream tasks column were not empty (the way I thought before), instead they had a list of Immediate Children from that row.

My understanding now:
Computer pulls a list of rows from Immediate Children column for each row into the All downstream tasks column for each row.

And then the computer pulls a list of all rows that are in the All downstream tasks column for each of those immediate children rows.

Now that adds another new batch of downstream tasks (a list of rows or “children of children”) to the All downstream tasks column of each row.

And the cycle continues:
Computer looks at new rows and pulls all downstream tasks of these rows…
Computer looks at new rows and pulls all downstream tasks of these rows…
Computer looks at new rows and pulls all downstream tasks of these rows…
… Until there is no more.

It’s really hard to keep everything that’s going on inside my mind, so I hope I explained it well, also big props to whoever came up with these formulas.

---

What about the Filter Formula?

.Filter(
    CurrentValue.IsNotBlank() and CurrentValue != thisRow
  )

Now this is another, second part of the formula that can be deleted and nothing changes, so I wonder why use it, what’s the purpose?

I’m not sure what CurrentValue means… is it the list of rows inside All downstream tasks cell for each row?

If so, why even use it. Why not just pull “Blank” if CurrentValue.IsBlank().

---

and regarding CurrentValue != thisRow, how is it even possible that a list of rows has a row that is same as thisRow?

Other than than, thanks so much for the clarification of how it works, something definitely clicked today for me .

[Nick_HE]

Yep so first we start out with the ListCombine, which grabs a list of all the children and a list of all the (great-)grandchildren… and puts them all into a single list.

Then we filter that list, which means we go through each item and ask if it fits the criteria we want, and then we either keep it or throw it out.

Inside a filter formula, CurrentValue means the current item from the list we’re looking at. In this case, each row that was in our list of children, grandchildren, etc.

We want to make sure:

• The row is not blank (maybe this would happen when creating a fresh new row or something? this is probably in there to prevent some janky but rare edge cases from causing problems). If it’s blank, we throw it out (but keep all the other ones, cause we still care about them)
• The row is not this row. How could this row get into its own descendants list? I haven’t looked into that doc in detail but maybe it’s possible to set up circular dependancies (where A relies on B which relies on A… or even A->B->C->A). This could actually cause the formula to break (or crash the doc?), for the chicken-and-egg reasons that confused you about the formula initially. This is another guard rail to make sure things don’t go sideways even if the user gets weird

You’ve definitely picked a complex doc to investigate, but this is how you find awesome coda tricks! The filtering and row referencing here is powerful stuff, and I think it’s worth creating a dummy doc and playing around with some simplified versions of these techniques to really concretize the concepts. E.g.

• Make table A, with a Lookup column to table B (this can just be a regular lookup column that you manually select a few records in). Then in table A, try some formulas that reference the subproperties (columns) of the table B records, using the dot
• Make table A, with a Lookup column to itself, and try referencing subproperties using the dot
• In a second column, try filtering the Lookup column based on subproperties of each record