JOIN / COLLECT CHILDREN TEXT

Ann Hannan

Hi All,

I'm stumped trying to get a join / collect statement to work.

I have Ancestor / Children columns set up, and on the Ancestors = 0 rows, I would like a text string of the column QLICI ID summarized for all Child rows - separated by a dash and return.

Here is what I have currently, but its not working:

=IF(Ancestors@row = 0, "- " + (JOIN(COLLECT([QLICI ID]:[QLICI ID], CHILDREN(), CHAR(10) + "- "))))

The QLICI ID is the primary column.

Appreciate any insights you can offer!

Thanks,

Ann

Ann Hannan

Since posting, I've refined my criteria for this formula. I'd like to join/collect only the immediate child to the Ancestor = 0 row. These rows will have Ancestor = 1 under a row with Ancestors = 0. I'm thinking I may need a helper column with the Ancestor = 0 QLICI ID to match. Then I can pull based on match of QLICI ID to the ancestor = 0 row. Help!?? :)

Ameya Athalye

Hi @Ann Hannan,

From what I've understood, it'd help to create a helper column say "hierarchy" and mark all parents with 0 and all children with 1. You can populate the values in that column by using this formula: =IF(COUNT(CHILDREN()) > 0, 0, 1). Convert this into a column formula to make life easier.

Then use the formula to collect your children IDs.

=JOIN(COLLECT([QLICI ID]:[QLICI ID], [Hierarchy]@row =1, 0), "-")

Hope this helps.

Dave Marsch

Hello @Ann Hannan, I have been trying to do something much like you and the results are not working properly. From my troubleshooting, something with the hierarchy, IF formula, and JOIN formulas is not playing nice and it's not even failing consistently from what I can tell.

Were you successful? I'd love to hear about it.