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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @steven.nienaber105336

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!