Parent / Child Join(Collect) Statement to summarize Child Rows

Amanda Fulbright

Hello!

First time poster here! I have found this community so helpful I can usually find enough examples to write the formulas I need but I'm stuck. I have a sheet that is tracking decisions across multiple groups. I am using a parent row with child rows underneath for each separate group. See below.

I am going to generate an Approval Document and would like to pull in each Committee, Workgroup, and/or Work Team that made a decision with the outcome and date. It would look like this:

[Assigned Committee] OR [Assigned Workgroup] OR [Assigned Work Team] + “ - Decision:“ + [Decision Status] + “ - Completed on: “ + [Complete Date] + CHAR(10)

When there's only one committee under the parent this formula works.

=IF([Is Parent?]@row = 1, JOIN(COLLECT(CHILDREN([Assigned Committee]@row), CHILDREN([Assigned Committee]@row), <>"")) + " - Decision: " + JOIN(COLLECT(CHILDREN([Decision Status]@row), CHILDREN([Assigned Committee]@row), <>"")) + " - Completed On: " + JOIN(COLLECT(CHILDREN([Complete Date]@row), CHILDREN([Assigned Committee]@row), <>"")) + CHAR(10), "No Results")

However when there's more than one I get a mess:

I'm ok doing 3 concatenated fields, one each for committee, workgroup, and work team, to reduce complexity but I need to get a single line for each group with the specific details like in the first image. Is this possible?

Thank you in advance for any tips!

Amanda Fulbright

I just needed to look at this with fresh eyes this morning!

The solution is to create a helper column on the child rows to gather the columns together and then reference that column in my parent row Join.

Child Row Formula for [Child Approval Info]

=IF([Assigned Workgroup]@row <> "", [Assigned Workgroup]@row + " Workgroup - Decision: " + [Decision Status]@row + " - Completed On: " + [Complete Date]@row, IF([Assigned Committee]@row <> "", [Assigned Committee]@row + " - Decision: " + [Decision Status]@row + " - Completed On: " + [Complete Date]@row, IF([Assigned Work Team]@row <> "", [Assigned Work Team]@row + " Work Team - Decision: " + [Decision Status]@row + " - Completed On: " + [Complete Date]@row, "")))

Parent Row Formula for [Approval Info]

=IF([Is Parent?]@row = 1, JOIN(COLLECT(CHILDREN([Child Approval Info]@row), CHILDREN([Child Approval Info]@row), <>""), CHAR(10)), "")

Thanks all!

Paul Newcome

You would need to insert a helper column and use a formula to output the string on each row. Then you would join the child rows of the helper column.

Amanda Fulbright

I just needed to look at this with fresh eyes this morning!

The solution is to create a helper column on the child rows to gather the columns together and then reference that column in my parent row Join.

Child Row Formula for [Child Approval Info]

=IF([Assigned Workgroup]@row <> "", [Assigned Workgroup]@row + " Workgroup - Decision: " + [Decision Status]@row + " - Completed On: " + [Complete Date]@row, IF([Assigned Committee]@row <> "", [Assigned Committee]@row + " - Decision: " + [Decision Status]@row + " - Completed On: " + [Complete Date]@row, IF([Assigned Work Team]@row <> "", [Assigned Work Team]@row + " Work Team - Decision: " + [Decision Status]@row + " - Completed On: " + [Complete Date]@row, "")))

Parent Row Formula for [Approval Info]

=IF([Is Parent?]@row = 1, JOIN(COLLECT(CHILDREN([Child Approval Info]@row), CHILDREN([Child Approval Info]@row), <>""), CHAR(10)), "")

Thanks all!

Amanda Fulbright

@Paul Newcome - I came to the exact same conclusion this morning! Thanks for your guidance!