Indented Sorting on a number/ level column field ?
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 o...
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.
