CR XI: record sorting 1

[dh42891]

I have a report that groups and sorts by a stream name attribute- a string, obviously. Most records are sorted correctly, but a handful having names like Joe Creek Trib 2, Joe Creek Trib 12, etc are not sorted correctly. It reads "Trib 2" as being alphabetically after "Trib 12" (presumably because it's reading character by character and "1" is before "2" even if the "1" is part of a number bigger than "2."

Sorry, not a good description... but if you understood, any ideas on how to get CR to sort correctly? I have a workaround that requries me to sort the database in another application and calculate an indexing key of sorts, but that seems a little ridiculous... thanks,

dylan

 

[lbass]

If the numbers are always at the end of the name, you would try creating a formula like:

stringvar array x := split({table.name}," ");
numbervar y := ubound(x);

join(x[1 to y-1], " ")+" "+ totext(val(x[y]),"0000")

Add zeros to correspond to the maximum number of digits in the number. Use this for sorting or grouping, but display the actual field.

-LB

 

[glyrocks]

Hmm, that gives me "Joe 0000" I guess it's splitting at the first blank and appending 0000.

dylan

 

[lbass]

I don't think you implemented the suggestion accurately, as I tested this.

-LB

 

[glyrocks]

Hmm, I guess I'm not sure how to correctly implement that code then.

dylan

 

[glyrocks]

Okay, I did some investigating and plugged your suggestion into the right place this time. I'm getting a "constant expression required here" error where I plug my table name in. Thanks,

dylan

 

[lbass]

All you need to do is copy the formula exactly, and then substitute your actual field for {table.name}. When you use a field in a report, it is identified by the table and the field, and in my example I used "name" for the field that results in the "stream name" since you didn't identify it.

-LB

 

[glyrocks]

{mapXSLn_DTL.STREAMNAME} is the field name, exactly. This should be going in the 'use formula as group sort order' correct?

What I've got there:

stringvar array x := split({mapXSLn_DTL.STREAMNAME}," ");
numbervar y := ubound(x);

join(x[1 to y-1], " ")+" "+ totext(val(x[y]),"0000")

Thanks for the help,

dylan

 

[lbass]

No, insert your group on this formula. Then remove the group name and place the field itself in the group header.

-LB

 

[glyrocks]

Ah, thanks! I believe that will do it!

dylan