Ancestors / Parents and concatenation of all level to make a string / map of the heirarchy

Christian Wells

Hello smartSheet world,

Can we concatenate all parents in a Column/field values together?

Example, If we have 5 levels;

123

ABC

abc

987

i

As descriptive text in each field (e.g. a Name of a WBS Level in a Project.)

Using the above to make the below adding a " " in between each (Could be any character)

"123 ABC abc 987 i" Thus give a complete heirarchy map in a field next to the ultimate child record.

I ask as this could be useful in having this for Reporting, individual list of Activities for staff. the display of the project or list where the identification of activities that may read the same yet be located underneath different area's of a hierarchy need to be identified differently.

I have considered using the Parent and/or Ancestors but get in my planning I get stuck on the cell reference for the next hierarchy level up.

I know this is a Tricky one, but may be someone has done this out there.. or can figure it out.

Cheers

CW

Andrée Starå

Hi @Christian Wells

I hope you're well and safe!

Try something like this.

=JOIN(ANCESTORS([Task Name]@row), " / ")

Would that work/help? 

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @Christian Wells

I hope you're well and safe!

Try something like this.

=JOIN(ANCESTORS([Task Name]@row), " / ")

Would that work/help? 

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Christian Wells

Thanks Andree,

Unfortunately I have and it seems all hierarchy references (Parent/Children/Ancestors/Descendants) are "Unparsable" using Join..

Great thought though.

Thanks

Christian

Andrée Starå

@Christian Wells

Happy to help!

It should work. I use it all the time.

Where are you located?

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Christian Wells

The Great 'much less infected' land of Australia and we utilise that syntax, as you compiled the formula... Thanks

I have found this Article I am looking into currently..

https://community.smartsheet.com/discussion/comment/273908#Comment_273908

But I'll give all your suggestions a try,

Thank you very much.

Christian Wells

Thanks Andree,

Got it working, there was a syntax issue.

Andrée Starå

@Christian Wells

Excellent!

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!