Sorting an Outline String

[jwrjwr54]

Looking for a way to sort (in numerical order) a string field that shows a sequence of events. For example:
0
1
10
10.1
10.2
10.2.1
10.2.2
11
15
2
3
Crystal XI sorts the string by the first number of the field, and not in numerical order (ie, the second step (2) comes after the 15th step). The field can have 3 decimals places.

 

[BarbaraFuhrmann]

Hi,

in similar case I usualy build a formula that completes the string in a way that the sort result will be as required.

Example: {@Event_completed}
if length({TABLE.EVENT}) = 1
then
'0'+{TABLE.EVENT}
else
{TABLE.EVENT}

HTH
Barbara

 

[jwrjwr54]

Thanks Barbara. It works until you get to 4.1. For example:
00
01
02
03
04
05
10
10.1
35
4.1
4.2
Any ideas? Thanks

 

[lbass]

Try a formula like this:

select len({@strno})
case 1 : picture({@strno},"x0.0.0.0")
case 2 : picture({@strno},"xx.0.0.0")
case 4 : picture(replace({@strno},".",""), "xx.x.0.0")
case 6 : picture(replace({@strno},".",""), "xx.x.x.0")
case 8 : picture(replace({@strno},".",""), "xx.x.x.x")

Use this as your sort field, but display your original field.

-LB

 

[synapsevampire]

Just cheat it...

Make 3 or more fields and drop them all in as the sortation one after the other:

Val(Left({table.TOC},InStr({table.TOC},".")-1)))

Val(Mid({table.TOC},InStr({table.TOC},".")+1,InStr(InStr({table.TOC},".")+1,{table.TOC},".")-InStr({table.TOC},".")-1))

Val(Mid({table.TOC},InStr(InStr({table.TOC},".")+1,{table.TOC},".")+1,InStr(InStr(InStr({table.TOC},".")+1,{table.TOC},".")+1,{table.TOC},".")-InStr(InStr({table.TOC},".")+1,{table.TOC},".")-1))

Should be about right...

-k