Sort - How do I sort Alpha characters then numbers 3

[koonie]

I have a report column that I need to sort in ascending order, first by alpha then numbers. I would like to create a formula to do the sort in Crystal Reports 9 for Visual Studio 2003 or 2005.

Data that I would like to sort:

13-2912004
06-2912004
2331-2912004
931-2912004
ANUSM636AV8
ZZ-0.016KGSQCM
BM400-2
TE6FUA

I need to sort in this order:

ANUSM636AV8
BM400-2
TE6FUA
ZZ-0.016KGSQCM
06-2912004
13-2912004
2331-2912004
931-2912004

Note that this field is variable in length, from 1 to 15 characters.

Any ideas?
Thanks in advance for any suggestions.

--Koonie

 

[lbass]

I think you could create a formula:

if isnumeric(left({table.string},1)) then 2 else 1

Use this formula as your first sort field and then add {table.string} as your second sort field.

-LB

 

[koonie]

Thanks for the suggestion.
The sort pattern works great for the first character.
Any idea how I can apply the logic to continue to sort the entire 15 characters?

Before sort:

A01AD
AA10D
CC01T34
001AGB
0A1TGC

Preferred sort pattern:

AA10D
A01AD
CC01T34
0A1TGC
001AGB

Thanks again.

 

[lbass]

Try this:

stringvar y := {table.string};
numbervar i;
numbervar j := len;
stringvar z := "";

for i := 1 to j do(
if isnumeric(y) then
z := z + "9"+y else
z := z + "1"+y
);
z

Use this as your sort field, but display {table.string} on your report.

-LB

 

[Turkbear]

Hi LB.


Those 2 formulas deserve another star..That need has been driving me nuts..( not a long drive, these days..)






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[koonie]

Wow, thanks for the second formula. It works like a charm.
Your help is much appreciated.

--Jim K.