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
- Customer Resources
- 66.2K Get Help
- 431 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!