Indented Sorting on a number/ level column field ?

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • In Progress
Hello, I am trying to figure out a way to sort reports based on a level column. So if you look at the attached screen shot. I would like some one to be able to add a record with a level column value of 1.1.3 and have it appear on the 5th row. If i sort high to low on the level column field ( which is a text field ) i do not get the result i want. It messes up the order.The level Column_Copy field, is a numeric field.. and that does not seem to take values like 1.1.3.  Can you help? 
Photo of Sumedha Weerasuriya

Sumedha Weerasuriya

  • 376 Points 250 badge 2x thumb

Posted 3 months ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,904 Points 20k badge 2x thumb
You need to create a Numeric Formula field named [Ordinal] and sort on this field but display on the text field which I will call [ID].

Here is the formula for the [Ordinal] under these conditions:
var Number R = 10;
var Number DA = ToNumber("0" & Part([ID], 1, "."));
var Number DB = ToNumber("0" & Part([ID], 2, "."));
var Number DC = ToNumber("0" & Part([ID], 3, "."));
var Number DD = ToNumber("0" & Part([ID], 4, "."));
var Number Ordinal = $R*($R*($R*$DA+$DB)+$DC)+$D);

Two important points about this formula:

(1) This formula is for the format *.*.*.* (four numbers and three periods) which specifies up to four levels of hierarchy. If you have say five digits you need to add additional an variable for DE and incorporate that into the final formula for [Ordinal] following the established pattern.

(2) At the top of this formula is the Radix [R] which has to be set to one more than the largest number that appears anywhere within the [ID] string. So if you had a section equal to say 18, R would need to be set to 19.

(3) Of course this can also be done with script.

Pastie Database