Join & Collect Children: 1 Occurrence

Anthony Barthelemy

Smartsheet Community:

How can I modify this formula where it only Collect 1 Child Occurrence?

Existing Formula: =JOIN(COLLECT(CHILDREN(), CHILDREN(), <>""), ", ")

Children: Chrome, Chrome, Firefox, Safari, Safari

Thanks

Paul Newcome

That is because you have the delimiter set as part of the DISTINCT function. If we shift one of the closing parenthesis from the end, it should resolve the issue.

=JOIN(DISTINCT(COLLECT(CHILDREN(), CHILDREN(), <>"")), ", ")

Alon Shachar

You need to use Distinct formula:

 =JOIN(DISTINCT(COLLECT(CHILDREN(), CHILDREN(), <>""), ", "))

Anthony Barthelemy

Formula Works, but No Commas between the browsers.

Results: SafariChromeFirefoxEdge,

Anthony Barthelemy

@Paul Newcome You have any thoughts on how I can Accomplish this? I tried different variation of the formulas but unsuccessful. The Problem is that I have no Commas between the Different Browsers.

Current Formula:  =JOIN(DISTINCT(COLLECT(CHILDREN(), CHILDREN(), <>""), ", "))

Results: SafariChromeFirefoxEdge

Thanks Paul & Alon

Paul Newcome

That is because you have the delimiter set as part of the DISTINCT function. If we shift one of the closing parenthesis from the end, it should resolve the issue.

=JOIN(DISTINCT(COLLECT(CHILDREN(), CHILDREN(), <>"")), ", ")

Anthony Barthelemy

Excellent, Thanks Paul

Paul Newcome

Happy to help. 👍️

JSpears

@Paul Newcome - I have a similar question. I have the following columns and children (the Blue row is the parent).

In the parent Workflow column I have: =JOIN(CHILDREN(), " ; ")

How can I change the formula so that if the Status for a child row = N/A, that it will not be included in the Join results?

I have tried =JOIN(DISTINCT(COLLECT(CHILDREN(), CHILDREN(), Status@row <> "N/A")), " ; ") but I still get all of the children listed.

Thank you!

Jennifer

Paul Newcome

@JSpears It looks like your syntax may be just a little off...

=JOIN(DISTINCT(COLLECT(CHILDREN(), CHILDREN(Status@row), @cell <> "N/A")), " ; ")