Skip to main content
Question

Indented Sorting on a number/ level column field ?

  • December 11, 2018
  • 0 replies
  • 39 views
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? 
    This topic has been closed for replies.

    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); 
    $Ordinal


    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 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
    https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=706
    Terms & ConditionsAccessibility statement