I have a hierarchy of items. Each level of t...

[Andrew_Eills]

I have a hierarchy of items.

Each level of the hierarchy has a reference number,

which is a Text type column, so for instance 2.5.4.7 or 10.2.4.3

My problem is using these numbers to sort in a table view.

The sort will put a ‘10’ before a ‘2’ or a ‘9’, etc, so

1.5.20.4 10.8.8.4 2.4.21.3

instead of

1.5.20.4 2.4.21.3 10.8.8.4

I couldn’t find anything on this in the documentation or forum.

Does anyone know a way to achieve a ‘numeric’ sort on ‘text’ numbers?

Thx

[Bellave_Jayaram]

You could create a number type VC with formula NUMBER(CONCATENATE(SPLIT([ColName],"."))) and sort using that.

[Aleksi_Alkio]

One way is NUMBER(INDEX(SPLIT([ColName],"."),1)) and then sort with that number. If you need to sort with the 2nd value as well, you can create another VC with the formula NUMBER(INDEX(SPLIT([ColName],"."),2)) and so on.

[Andrew_Eills]

Thanks, guys.

I’ll have a look at both these ideas.

[Grant_Stead]

Specialized hierarchical views are what’s really required. Please vote on it here:

Hierarchy Views Feature Requests

Expanding Tree - root/node style Chart Style - Think org Structure And as a group and sort option for drilling, tables/decks… All we would need to do is indicate which column in the table contains the Parent Ref… Maybe a customization on what level to start at, and hide/show “blank” levels… Hierarchy is a easily achieved by having a REF column within that table the ties back to the same table… So the user can indicate the parent… EX: an employee table could have a REF column back to the emp…

[Andrew_Eills]

I agree that built-in hierarchical functionality would be great, as my needs were pretty basic. In the end, I achieved the hierarchical functionality with table groupings, which seem to treat a textual ‘10’ as greater than a textual ‘9’, which kind of solved my problem, but restricted me to displaying data and choices in a certain way. I also used INDEX(SPLIT(…) to pull the numbers (still in textual format) from the hierarchical references (eg, ‘4.Building Systems, 1.HVAC System, 3.Environmental Control’), so I could have purely textual numeric references (eg, ‘4.1.3’).

[Grant_Stead]

I too do Primavera P6 scheduling.
my contact info is on my website if you ever need anything. steadglobal.com

[Andrew_Eills]

Thx, Grant. I don’t actually use Primavera, but I appreciate the offer.

[Grant_Stead]

I guess a lot of estimating / controls software probably uses the same numerical references huh…