How to sort by alpha, then numeric value by a formula!

[Magj]

I have the following data in my report

ST0000663-10
ST0000663-11
ST0000663-1
ST0000663-2
ST0000663-3
SU0000663-10
SU0000663-11
SU0000663-1

How do I create a formula to sort the above data by the first alpha characters, then the number after the dash like below:

ST0000663-1
ST0000663-2
ST0000663-3
ST0000663-10
ST0000663-11
SU0000663-1
SU0000663-10
SU0000663-11

Thanks

 

[lbass]

Create a formula:

stringvar array x := split({table.string},"-");
if ubound(x) > 1 then
x[1] + totext(val(x[2]),"00")

Use this formula as your sort field, but use your original field for display in the report.

-LB

 

[hilfy]

You could try something like this:

Local NumberVar atDash;
Local StringVar start;
Local StringVar end;
atDash := InStr({table.field}, '-');
start := left({table.field}, atDash);
end := right(('00' + mid({table.field}, atDash + 1)), 2);
start + end

This assumes that there is a maximum of two numbers after the dash. If there can be more, the formula for "end" needs to be modified by adding zeros to the string and adding to the last number. For example, if there can be three numbers, it would change to "right(('000' + mid({table.field}, atDash + 1)), 3)".

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Magj]

Thank you so much! it's work. : )