Forum Discussion

des_san's avatar
des_san
Icon for Advocate I rankAdvocate 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

PROJECT.01.01

PROJABC

PROJABC.001

PROJABC.001.01

PROJABC.001.01.M01

PRJ1234

PRJ1234.ADM

PRJ1234.ADM.123

...

 

How should I go about this?

I saw a other posts that reference PATH() but that doesn't look like the right solution.

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.

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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
    Icon for Advocate I rankAdvocate 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
      Icon for Advocate I rankAdvocate I
      2 years ago

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