Sort Order

[Juli Carey]

I'm trying to sort my database by membership number order but can't get it to run sequentially ie 1,2,3,etc

All I can achieve is 1,10,100,101 etc.

I'm running A5V4.03

A big thanx to anyone who can help.

Juli

[bobsk]

RE: Sort Order

Hi Juli,
I couldn't duplicate the problem but have have found it easier to work with ID type fields as characters. Also beginning with something like 00001, 00002, etc.
Also try this. Remove autoincrement from field rules and manually enter the next record. Then go back and put autoincrement on again.
Bob

[Leon Brachman]

RE: Sort Order

Juli -

You are describing a very common problem in sorting. Actually, the sort is probably doing exactly what it's instructed to do. I'm guessing that you have set up the sorted field as a character field. In that case the first character to the left always comes in sorted order before the second character, etc. - regardless of the active length of the field in various records.

Thus, everything starting with 1 will always come before anything starting with 2, etc.

There are 2 fixes for this - both pretty simple.

1) Pad shorter numbers with leading 0's so that this field in all records is always of the same length.
For example, 0001, 0002, 0003,0010,0011,0012,0013,
0020,0021, etc.
Try it - that will always work.

2) if you don't like padding (easy to do with "PADL" which is an available function), then change the field type from character to numeric. The sort should handle the numbers just fine as you have them now as long aw the sort knows that it's handling numbers, not characters.

Good luck.

Leon Brachman
[email protected]

[BRochford]

RE: Sort Order

One thing you could do is use this expression in your sort order:
(Assume membership_no is C 10)

padl(alltrim(membership_no),10,"0")This returns "0000000001" and will give you the sequence you want. You probably don't need the alltrim, but it doesn't hurt.

The order you are presently getting is correct because Char. fields are always Left Justified.

As an alternative, you could make your field a numeric field.
-Barry

[Juli Carey]

RE: Sort Order

Thank you, thank you.

It's so easy when you know how, but it was driving me crazy, but am now a happy bunny.

Juli

[Juli Carey]

RE: Sort Order

Cheers for that. It really is easy when you know how !

Thanks, Juli

[Juli Carey]

RE: Sort Order

What can I say but thanks - and it works.

Juli

[martinwcole]

RE: Sort Order

Bob, why is it recommended that ID fields not be numeric using Alpha.

With the last language I used, also written in "C", the reverse was recommended. So, when I got started in Alpha I made my primary linking field ("Customer Number") numeric. By the time I saw someone on the board say never use numeric unless you are adding and subtracting, I was already way too far down the road to turn back. I have never had any problems with it.

Also, I have never understood why some users use complex syntax in querries, when it isn't necessary, unless its a hold over from an earlier version of Alpha. For example, here is a query filter that works just fine, that mixes numeric, date, and character fields:

query.filter="between(customer_number,1,20320523) .and. wrepairexpires {} .and. between(val(wrepairlength),1,3)"