Problem with sorting 2

[Macho123]

Hi, I have a string field which also has numbers in it ans i am having problem to sort it. below is the data.

0
1
1a
11
2b
abc

There are 1 to 11 numbers in the string and then the string also has some thing like 1a,2b ans also has abc,dcg etc. the output i am lokking for is

0
1
2
...
11
1a
1b
2a
2b
abc
def
gft
.....

Please help me with this.

thanks...

 

[beltmanjr]

I have no time at the moment to try this myself,
and completely depending on where in the report you need this sorting I would be thinking about 2 arrays.

1 which takes all numbers and sorts those
1 which takes all text and sorts those.
Append the text one to the number one afterwards.

Your problem is that you are trying to sort numbers as strings, which wont work in a straight approach, hence the split into 2.

If you cant do this in your report, consider a view or command.

I hope this gets you in the right direction

 

[lbass]

Create two formulas:

//{@val1}:
if isnumeric({table.field}) then
val({table.field}) else
1000000

//{@val2}:
if isnumeric({table.field}[1]) then
val({table.field}) else
1000000

Add these in order to the report->sort order. Then add {table.field} as the third sort field.

-LB

 

[bdreed35]

This is a little convoluded, but it works:

//@Sort1
local stringvar input := {table.field};

if input[1] in "a" to "z"
then "3"
else
if totext(val(input),0,"") = input
then "1"
else "2"

//@Sort2
local stringvar input := {table.field};
local stringvar output;
local numbervar x;

for x := 1 to length(input)
do
(
if input[x] in ["0" to "9"]
then output := output & input[x]
);

val(output)

//@Sort3
local stringvar input := {tablea.field};
local stringvar output;
local numbervar x;

for x := 1 to length(input)
do
(
if input[x] in ["a" to "z"]
then output := output & input[x]
);

if output = ""
then " "
else output

Add these to your Record Sort Expert in the same order.

~Brian

 

[Macho123]

Thanks for the quick responses, i am trying them but to make it clear, it is field on which there is agrouping. hope it works for the group too. else let me know

Thnaks...

 

[bdreed35]

Sticking with lbass's solution since it is more streamlined, I would insert 2 new groups in your report; one on val1 formula, and then the other on val2.
Once you have done that, move your original grouping on the original field below the 2 new groups.
Lastly, suppress Val1 grouping header and footer, and Val2 grouping header and footer.

~Brian

 

[Macho123]

lbass your formula worked great. Thanks very much. But i did not understand the logic behind it.

as you and brain suggested i created two formulas and made groups on them and put my original group on the original field under them and supressed the formula groups. it worked perfectly but i did not understand what is that 1000000 and if you have time could any one of you explain how it works.

Thanks...

 

[lbass]

The 1000000 is just a high number that would always sort last. Without it, the formula would default to 0, and those records that didn't meet the criteria would be sorted first.

-LB