Parent / Child Join(Collect) Statement to summarize Child Rows
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!
Help Article Resources
Check out the Formula Handbook template!