Forum Discussion

des_san's avatar
des_san
Advocate I
2 years ago
Solved

Create a hierarchy from a single field

Hi everyone, I need help creating a hierarchy from a single field, Project ID, that can be used in reports. Here is an example of what the data in that field looks like:   PROJECT PROJECT.01 PROJ...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    PATH() is correct.  Replace all periods with pipes.  Then use PATH.

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,

    Thanks for the solution lbendlin  provided, it is excellent, and i want to offer some more information for user to refer to.

    hello des_san , based on your description, you can refer to the following solution.

    Sample data 

    You can create the following calculated columns

    Path = SUBSTITUTE([Column1],".","|")
    Level 1 = PATHITEM([Path],1)
    Level 2 = PATHITEM([Path],2)
    Level 3 = PATHITEM([Path],3)
    Level 4 = PATHITEM([Path],4)

    Output

     

    If the solutions lbendlin  and i provided can help you solve the problem , please consider to mark them as a solution.

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

des_san's avatar
des_san
Advocate I
2 years ago

Thanks lbendlin and Anonymous .
I was reluctant to use PATH at first because it requires me to create a custom column for each level of the Project ID. Is there a more dynamic/flexible solution that can adjust automatically to the Project ID levels? 

Follow up question:
I created a hierarchy field with the Path Level fields. Is it possible to hide levels that are blank (or skip them)? In the example above, the first project has 3 levels and the second project has 4 levels.  As a result, the first project is showing a redundant blank 4th level. How would you approach this?

  • des_san's avatar
    des_san
    Advocate I
    2 years ago

    I will post the follow-up question as a new question.