Need to change from text to numeric

[tomkonec]

Hello

I would like to sort a report by a a kind of numeric sorting via a field that is alpha in the database.

Currently the field, if sorted using generic sorting shows:

1.1
1.10
1.11
1.12
1.2
1.21
1.22
1.3
1.4
1.5
1.6
1.7
1.8
1.9
2.0

however I would like it to be sorted as follows:

1.1
1.2
1.3
1.4
1.5
1.6
1.7
1.8
1.9
1.10
1.11
1.12
1.21
1.22
2.0

I have tried the "ToNumeric" field however it makes everything two decimal places which does not work

Please could you advise

 

[dgillz]

Why would 1.10 be AFTER 1.9? This is not true for for normal sorting, either numeric sorting of alphanumeric sorting.



Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[CoSpringsGuy]

this might work
create two formulas
formula1
tonumber(split({yourfield},".")[1])

formula2
tonumber(split({yourfield},".")[2])

sort by Formula 1
then by formula 2

then put your field in the details section

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[tomkonec]

This formula is getting nearer

---------------
formula1
tonumber(split({yourfield},".")[1])

formula2
tonumber(split({yourfield},".")[2])
---------------

However what I would really like to do is to be able to sort by the numbers with always having two digits after the dot, i.e

1.1 change to 1.01
1.2 change to 1.02
1.10 keep as 1.10
1.12 keep as 1.12

Is there a way to do this either by using the formula above or a different formula?

 

[CoSpringsGuy]

well noone else is piping up so try this:
sort by formula 1 and then by formula 2 that we created above
create this formula

formula 3
cstr(tonumber(split({yourfield},".")[1]))
&
"."
&
cstrtonumber(split({yourfield},".")[2]),"0#")

and place it in your details section. ITs kind of ugly but most of my methods are!

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[CoSpringsGuy]

missed a ( sorry


formula 3
cstr(tonumber(split({yourfield},".")[1]))
&
"."
&
cstr(tonumber(split({yourfield},".")[2]),"0#")

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[tomkonec]

That is superb. Thanks! And just for future reference for anyone looking at this post I made one tweak to get it perfect..

formula 3
cstr(tonumber(split({yourfield},".")[1])"#")
&
"."
&
cstr(tonumber(split({yourfield},".")[2]),"0#")

 

[CoSpringsGuy]

im not sure how the # will improve it but Im glad we got to a solution for you!

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection