How can I reflect only the highest level ancestor?
I have the =JOIN(ANCESTORS(Task@row), " / ") formula in use, but I want it to stop at the top level ancestor. In this case, I only want it to reflect Synergy:
But of course, my JOIN function is bringing in each of the child rows thereafter. What function can I use?
Answers
-
With the help of a 'level' or ancestor count column, you can write an IF statement that differentiates the formulas based on the ancestor level.
If you don't have a 'level' helper column (you can call it whatever you like and it can be hidden and pushed out of the way if desired), here's a formula
Level = COUNT(ANCESTORS()) *I usually insert the name of my primary column in the Ancestors function to make sure everything is counted. Note my top Level will be equal to zero.
=IF(Level@row=0, [Your Primary Column]@row, JOIN(ANCESTORS(Task@row), " / "))
Be sure to insert the name of your actual Primary column name - I wasn't sure if this was [Task].
Would this work for you?
Kelly
-
Hi Kelly! Thanks for looking in to this for me! We're getting closer - the final step is to remove / Reports, and / Autoimport in the case of this screen shot: i.e., not joining together the ancestors, but just keeping the parent row, that being just Synergy. Here's the formula I have that yields what is displayed: =IF(Level@row = 1, Task@row, JOIN(ANCESTORS(Task@row), " / "))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!