Descendants/Children Formulas (w/ Join, Collect, Distinct, etc.)

jellissr

Hello - First, THANK YOU in advance for reading this and helping in anyway.

Having some trouble with joining and collecting children and children's children (descendants minus the immediate parent's child) in parent rows.

Problem 1: Under Contract Request IDs, for example, I only want CR1112 to populate in the parent column one time. I know it's showing up a second time because it's listed both individually and as a group in children rows. So I figured I just pull the children's children (and somehow omit the immediate children) all the way up to my parent row.

Problem 2: Under Contract Types(s), for example, I don't want Master Service Agreement and Statement of Work grouped together, nor do I want Master Service Agreement to appear more than once in the parent cell. I want to keep these as individual as they are shown in the children rows.

Lucas Rayala

Hi @jellissr,

Problem 1: To prevent duplicate entries in the parent column under "Contract Request IDs," you can modify the formula to only include the distinct children and their children's children. This way, the immediate children will be omitted. You can use the following formula in the parent column:

=JOIN(ARRAYUNIQUE(CHILDREN(CHILDREN([Unique Row ID]@row))), ", ")

This formula retrieves the children's children using the CHILDREN function and then removes any duplicates using ARRAYUNIQUE. The JOIN function combines the distinct values into a single string separated by commas.

Problem 2: To keep "Master Service Agreement" and "Statement of Work" separate in the parent column under "Contract Type(s)" and avoid duplicate entries, you can use a similar approach. Modify the formula in the parent column with the following:

=JOIN(ARRAYUNIQUE(CHILDREN(CHILDREN([Unique Row ID]@row, 1, 0))), ", ")

This formula retrieves the children's children (descendants) but excludes the immediate children by specifying an offset of 1 in the CHILDREN function. The ARRAYUNIQUE function removes any duplicate values, and the JOIN function combines the distinct values with commas as separators.

By using these modified formulas, you can collect the children and children's children in the parent rows while excluding immediate children and avoiding duplicate entries.

jellissr

Thanks, Lucas! My parent cells are returning #UNPARSEABLE, so want to make sure I'm inputting 'Unique Row ID' as a reference correctly.

Problem 1: When I use the formula below for pulling children's child information, I'm getting #UNPARSEABLE. Am I using the correct reference? After trying the column header [Contract Request ID(s)] as Unique Row ID, I did try to plug in the specific Row ID shown in the image below, but also to no avail.

=JOIN(ARRAYUNIQUE(CHILDREN(CHILDREN([Contract Request ID(s)]@row))), ", ")

Row 1154 Properties.png

Problem 2: When I use the formula below for pulling children's child information, I'm getting #UNPARSEABLE. Am I using the reference correctly?

=JOIN(ARRAYUNIQUE(CHILDREN(CHILDREN([Contract Type(s)]@row, 1, 0))), ", ")