Numeric sort only? 1

[cjbrown815]

Hi,
What is the best way to get Crystal to sort numerically? I run into this all the time. Currently the records are sorting
XYZ 0-0
XYZ 1-1
XYZ 1-10
XYZ 1-100
XYZ 1-2
XYZ 1-3
XYZ 1-4

I need them to be in proper numeric order.

thanks

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[lbass]

Try using a formula like this:

stringvar array x := split({table.field},"-");
x[1]+"-"+totext(val(x[2]),"000") //use as many zeros as the maximum number

You can sort by this formula and then display your actual field in the report.

-LB

 

[cjbrown815]

Thanks LB, its very close. The 100-100 is still not in line

1-1
100-100
1-2
1-3
1-4

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[lbass]

Well, this is the first I've heard of that initial 100. Please be comprehensive in your examples from the beginning. Is "XYZ " part of the field or a separate field? And is there definitely a space after the last letter (Z)?

-LB

 

[cjbrown815]

Sorry, the actual data is in all one field and looks like this:

RMAGE CE 0-0
RMAGE CE 1-1
RMAGE CE 100-100
RMAGE CE 1-10
RMAGE CE 1-11
RMAGE CE 1-2
RMAGE CE 1-3
RMAGE CE 1-4
RMAGE CE 2-1
RMAGE CE 2-2
RMAGE CE 2-3

Then it repeats with RMAGE EOD 0-0. and so on

thanks

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[lbass]

stringvar array y := split({table.field}," ");
stringvar z := ubound;
stringvar array x := split(z,"-");
y[1]+" "+y[2]+" "+
totext(val(x[1]),"000")+"-"+
totext(val(x[2]),"000")

-LB

 

[cjbrown815]

Its telling me I need a string at := ubound

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[cjbrown815]

The field I'm using is a VarChar (30)

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[lbass]

Sorry, should have been:

stringvar z := y[ubound];

-LB

 

[cjbrown815]

That worked, thanks LB you're the best

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931