Parent / Child Join(Collect) Statement to summarize Child Rows
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!
Best Answer
-
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!
Answers
-
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.
-
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 - I came to the exact same conclusion this morning! Thanks for your guidance!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!