Alpha Video Training
Results 1 to 8 of 8

Thread: Sort Order

  1. #1
    Juli Carey
    Guest

    Default Sort Order

    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

  2. #2
    Member
    Real Name
    Bob Kenworthy
    Join Date
    Apr 2000
    Posts
    324

    Default 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

  3. #3
    Leon Brachman
    Guest

    Default 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
    leonhb@flash.net

  4. #4
    Member
    Real Name
    Barry Rochford
    Join Date
    Apr 2000
    Posts
    452

    Default 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

  5. #5
    Juli Carey
    Guest

    Default 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

  6. #6
    Juli Carey
    Guest

    Default RE: Sort Order

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

    Thanks, Juli

  7. #7
    Juli Carey
    Guest

    Default RE: Sort Order

    What can I say but thanks - and it works.

    Juli

  8. #8
    VAR
    Real Name
    Martin W. Cole
    Join Date
    Apr 2000
    Location
    Terrell, Texas (near Dallas)
    Posts
    5,957

    Default 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)"

Similar Threads

  1. sort by Biblical order
    By Philip Ramstad in forum Alpha Four Versions 7 and 8
    Replies: 7
    Last Post: 06-05-2005, 01:13 PM
  2. Sort order of Sub reports
    By kgilroy in forum Alpha Five Version 5
    Replies: 8
    Last Post: 08-22-2004, 05:17 AM
  3. Browse Sort Order
    By Jeffrey Wolfe in forum Alpha Five Version 5
    Replies: 4
    Last Post: 08-21-2004, 09:32 PM
  4. Complicated Sort Order
    By forskare in forum Alpha Five Version 5
    Replies: 4
    Last Post: 07-23-2002, 07:19 PM
  5. Letter Sort Order
    By John Oesterle in forum Alpha Five Version 4
    Replies: 0
    Last Post: 05-18-2000, 08:21 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •