Sorting a String Field

[Lanie]

Hi everyone,
I've got a sting field that contains numbers 1,2,3,4,5,6,7,8,9,10,11,12,13,14 etc.

I need to sort this field so that it appears in the exact order as above.

Can someone tell me what I need to do to make this happen?

It wants to sort 1,10,11,12,13,14,2,20,21,22,23 etc., even when I set the Record Sort Order to Ascending.

What am I doing wrong?

Thanks for all you help, in advance. Lanie
laniet@ij.net
etroidl@conaxfl.com

 

[bdreed35]

When evaluating Strings, 1,10,100, etc. will all come before 2,3,4,etc. It works on the same prinicple as the text; a,ab,abc all come before b,c,d,etc. You can easily resolve this by creating a function that converts the field to a number, and then you sort on that formula.

Create A formula.
the contents should be this: ToNumber ({table.YourStringField})
Now you can sort on this field and get the appropriate order.

-Brian

 

[Lanie]

Brian,
Thank you for your assistance, I am back in work now. However it did not work.

I did the following in the Field Explorer:

Created the formula: @SortLineItems

Put in the following formula:
ToNumber ({CUSTOMER_ORDER_LINE\.LINE_NO});

WhilePrintingRecords;

Select {CUSTOMER_ORDER_LINE\.LINE_NO}
CASE "1":
"01"
CASE "2":
"02"
CASE "3":
"03"
CASE "4":
"04"
CASE "5":
etc, etc,

Saved it - No Errors Found. Then brought it into my DetailsA section of the report. It did not sort of course, so I attempted to use the Record Sort Order function.

Its at this point that I have trouble as it will not appear in the list for me to select a sort on.

By the way, I need to change the case, because that is what we want to see, 01, 02, etc. however if there is a better way than using CASE, I'd like to know that too. Hope this isn't asking too much.

Anywho . . . I think I must be missing something here...can you help?

Thanks for your help. Lanie
laniet@ij.net
etroidl@conaxfl.com

 

[Naith]

You're having trouble selecting it as a sortable field because you've included the function "WhilePrintingRecords" which forces the field to be assessed after sorting would need to be worked out.

You shouldn't really need anything else in your formula other than:

ToNumber ({CUSTOMER_ORDER_LINE\.LINE_NO});

Naith

 

[bdreed35]

Lanie:

Try this:

ToText(ToNumber({CUSTOMER_ORDER_LINE\.LINE_NO}),'00')

This will convert your field to a number, then back to a text field with a leading zero, if needed. THis will only hold two places to the left of the decimal. You can group or sort on this formula. Make sure that you add the formula to the report for displaying, otherwise you won't see the formatting change.

Also, as Naith stated, don't use whileprintingrecords or the case statement. You don't need them.

-Brian

 

[Lanie]

Naith,
Thank you so much, it worked great. Have a wonderful day! Lanie
laniet@ij.net
etroidl@conaxfl.com

 

[Naith]

Glad it worked out for you.

Brian, the success of the sorting relies on the field being a number. If you convert it back to a text field, you're going to run into problems with fields like 111 coming before 22.

Naith

 

[bdreed35]

Naith:

I agree. If there ended up being three characters, it would certainly throw off the sorting.

But from her post "By the way, I need to change the case, because that is what we want to see, 01, 02, etc.", I interpreted that there would only be two characters. If this is the case, the solution I came up with would allow the sorting to work properly and the leading zero be placed in front of any value from 1 to 9.

The way I see it, if there needs to be leading zeros, we would need more information as to what the max length would be.

-Brian

 

[Lanie]

Hi Brian and Naith,

The max length will be 4 characters, and as of yet our order lines hover around 103 - 115 max.

But in the interest of the future, perhaps the formula should accomodate the issue with fields like 111 coming before 22.

What do think? Lanie
laniet@ij.net
etroidl@conaxfl.com

 

[Naith]

Your solution already does compensate for the 111 and 22 example, Lanie, as per Brian's original post which correctly changed your sort to a numerical sort.

You'd only run into the afore mentioned problems when you treat number values as a string field.

Naith

 

[bdreed35]

Lanie:

I think if you answer these questions we can clear this thread up.

1. Does your field hold the values 1,2,3,4,...10,11,12,..etc or 01,02,03,04,...10,11,12,...etc.?

2. Do you need to have leading zeros in your field when it is on the report?

-Brian

 

[lyanch]

I think the easiest way to look at this is that there are two things happening.. and if you handle each separately then it should be less confusing.

The first issue is sorting. Createing the numeric formula handles this easily.
Sorting formula (from numerous)

ToNumber ({CUSTOMER_ORDER_LINE\.LINE_NO})

The second issue is display. You can do this several ways.

A formula (from Brian):

ToText(ToNumber({CUSTOMER_ORDER_LINE\.LINE_NO}),'00')

Is probably the easiest. You can add more 0's to the formula as you want/need them. For instance

ToText(ToNumber({CUSTOMER_ORDER_LINE\.LINE_NO}),'0000')

Would return: 0001,0002,....0100....2000

which would cover your four places right off the bat.

You could also do this as a SQL statement:

right( replicate("0", 4) + convert(varchar, CUSTOMER_ORDER_LINE\.LINE_NO), 4)

which would give the same as using the second ToText above (with '0000')

If you think of it as two problems, with different solutions it makes it much easier.

Lisa

 

[bdreed35]

Lisa:

Well put.

~Brian

 

[Lanie]

Thank you everyone for your help and assistance. I nice to know that there are a lot of very knowledgeable and skilled people here that are willing to help a person out.

Have a great day! Lanie
laniet@ij.net
etroidl@conaxfl.com