String Sort 1

[kutoose]

I have a string field which contains both numbers and characters. When I group the report based on this field (which is string) I don't get correct results.

For example
If the values for that fields are

"9"
"10"
"13"
"45"
"34"
"39"
"67"
"76"
"54"
"35"
"23"
"11"
"23"
"65"
"87"
"1"
"11"
"10"
"2"
"3"
"4"
"ABC"
"WQR"
"MST"

when I sort it in ascending order I get
1
10
10
11
11
13
2
23
23
3
34
35
39
4
45
54
65
67
76
87
9
ABC
MST
WQR

Where as I need the sort as
1
2
3
4
9
10
10
11
11
13
23
23
34
35
39
45
54
65
67
76
87
ABC
MST
WQR

I also have to group the values in the report using these fields in other reports.....

Please help !!!

 

[butkus]

You might try the val() function to return the numeric value of your data and then sort the results (numeric first the alpha)

 

[kutoose]

Hi butkus,
Val() works well as far as sorting the records. But I need to group the records based on the fields. When I try to group by the val function, ABC,MST and WQR are coming together in one group ...( Val(ABC),Val(MST) and Val(WQR) all return 0). I want them all in seperate groups........

 

[goranm]

Hi !

Maybe this can help you.

First find the size of the field (let´s say it is 5 in your case).

Then create a formula on wich you then make your grouping.

ReplicateString("0",5 - length({YourField})) + {YourField}

This will pad 0´s to the left of your value, and the order shall be as you wish.

Then you can go to "Change Group Options" and under "Group Options" you can select your original field so that will be the one that show up on your report.

/Goran

 

[kutoose]

Hi Goran
It wont work with the numeric fields...It will be sorted on string basis....If the field contains only alphabet it will work...

How can I put in a formula like...

If {field_name} is numeric then tonumber({field_name}) else
totext({field_name})

some thing like that......

 

[mbarron]

Create the following formula:

stringvar res;stringvar sort:={your.field};

if isnumeric(sort) then res:=totext(tonumber({sort.nums}),"0000&quot else
res:=sort

Insert this field into your report and sort on it. Then suppress the field.

The 4 zeros will ensure the any numbers up to 9999 will sort. Mike

 

[kutoose]

Seems its working.......Have to do more testing...
Thanks Mike !!!