New call-to-action
Results 1 to 13 of 13

Thread: sorting a character type field

  1. #1
    Member
    Real Name
    t walker
    Join Date
    Mar 2012
    Location
    USA
    Posts
    230

    Default sorting a character type field

    Hello, I'm wondering if there is a way to soft a field that is defined as character, that only have numbers in it in numeric order , like 1, 2, 3 etc , instead of 1, 10, 100, 101 etc. I tried using the val function on the Order field on the Sort Records To Print dialog box, but the records still did not sort properly. Any help would be greatly appreciated.

    Thank you
    Tracy

  2. #2
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default Re: sorting a character type field

    Try using val() and remspecial(), i.e., val(remspecial(your_field_name))

    Raymond Lyons

  3. #3
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: sorting a character type field

    I generally force the sort with

    padl(alltrim(fieldname),n,"0")

    where n is the defined width of the field.
    There can be only one.

  4. #4
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default Re: sorting a character type field

    Quote Originally Posted by Stan Mathews View Post
    I generally force the sort with

    padl(alltrim(fieldname),n,"0")

    where n is the defined width of the field.
    Stan is "generally" far smarter than I am, but my method and his will give very different results if a field ever has something like "5g" in it instead of just "5". Test them and see. Then someone will probably show you how to strip out any "g"'s if something like that is likely, or keep them from getting into the field in the first place. Lots of ways to skin a cat in Alpha Five. For now, I have to go to dinner, which I hope won't include cat meat--or horse.

    Raymond Lyons

  5. #5
    Member
    Real Name
    t walker
    Join Date
    Mar 2012
    Location
    USA
    Posts
    230

    Default Re: sorting a character type field

    Hello,

    I tried added the remspecial function to my formula, and it didn't work
    I also tried the padl(alltrim suggestion and that didn't work either
    The numbers still sorted like the 1, 100, . . . . .2 etc

    I looked for a function to convert a character field to a numeric filed, but could not find one. Does such a function exist?

    Thank you
    Tracy

  6. #6
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: sorting a character type field

    I looked for a function to convert a character field to a numeric filed, but could not find one. Does such a function exist?
    No. There is a function that that will convert a character value to a numeric value that was provided you in the first answer to your post.

    I tried added the remspecial function to my formula, and it didn't work
    I also tried the padl(alltrim suggestion and that didn't work either
    The numbers still sorted like the 1, 100, . . . . .2 etc
    Then you did something wrong in both attempts. Both will work if applied correctly.
    There can be only one.

  7. #7
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default Re: sorting a character type field

    Tracy,

    As Stan said, you have to be doing something wrong. Unzip and take a look at the sample I am attaching. The sorting is done in the report layouts (Report, Select Records in design mode for the layouts).

    Raymond Lyons
    Attached Files Attached Files

  8. #8
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,495

    Default Re: sorting a character type field

    Description

    VAL() converts a Character_String to a numeric value. If the Character_String contains leading non-numeric characters, then a value of 0 is returned.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  9. #9
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default Re: sorting a character type field

    Quote Originally Posted by Ted Giles View Post
    Description

    VAL() converts a Character_String to a numeric value. If the Character_String contains leading non-numeric characters, then a value of 0 is returned.
    Ted,

    While what you say is true (I think!), it does not seem to apply to trailing non-numeric characters, and the 2 ways given to Tracy do result in different orders. Below I give one way of dealing with a single leading non-digit, but there must be other, better ways to deal with sorting of this kind. It's just beyond me at the moment.

    Raymond Lyons
    Attached Images Attached Images

  10. #10
    Member
    Real Name
    t walker
    Join Date
    Mar 2012
    Location
    USA
    Posts
    230

    Default Re: sorting a character type field

    Hi Stan,

    I went back and checked everything that I could think of.
    The OrderNbr field is defined as character, width = 6

    I typed the expression below on the Select Record to PRint dialog in the Order field.
    padl(alltrim(OrderNbr),6,"0")

    The rpt displays the records in this order 1, 10, 100, 101, 102

    I was expecting to see this order 1, 2, 3,4 5, etc

    I don't know what else to check to figure out why this isn't working for me.

    Tracy


    Quote Originally Posted by Stan Mathews View Post
    I generally force the sort with

    padl(alltrim(fieldname),n,"0")

    where n is the defined width of the field.

  11. #11
    Member
    Real Name
    t walker
    Join Date
    Mar 2012
    Location
    USA
    Posts
    230

    Default Re: sorting a character type field

    Hi Raymond,

    After looking at the reports that you created, I quickly realized that the reason it wasn't working for me is because the field I'm sorting on is the group field, not a field in the details section of the report. when I pasted the val(remspecial expression on the group break field, the sort worked as you said it would.

    I went back and looked at my original message and saw that I left out that I was doing this at the group.
    I am so sorry that I caused so much drama over this issue.
    I truly appreciate and am thankful that you and everyone else that posted a reply to this thread did not give up on me.

    Thank you to everyone that posted a reply.
    Tracy



    Quote Originally Posted by Raymond Lyons View Post
    Tracy,

    As Stan said, you have to be doing something wrong. Unzip and take a look at the sample I am attaching. The sorting is done in the report layouts (Report, Select Records in design mode for the layouts).

    Raymond Lyons

  12. #12
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,666

    Default Re: sorting a character type field

    Tracy

    I haven't made a mistake since the last one and I vow not to make another one until the next one...
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  13. #13
    Member
    Real Name
    t walker
    Join Date
    Mar 2012
    Location
    USA
    Posts
    230

    Default Re: sorting a character type field

    LOL Al,

    After I figured out what was wrong, I was able to fix it, and all is well.
    Thank you
    Tracy

    Quote Originally Posted by Al Buchholz View Post
    Tracy

    I haven't made a mistake since the last one and I vow not to make another one until the next one...

Similar Threads

  1. Sorting a mixed character field
    By scs010484 in forum Alpha Five Version 5
    Replies: 2
    Last Post: 08-05-2011, 08:39 AM
  2. Return only numbers in a Character type field
    By George Corder in forum Alpha Five Version 8
    Replies: 6
    Last Post: 10-02-2009, 12:30 PM
  3. Converting field of type Time to Character
    By Rhett Scott in forum Alpha Five Version 9 - Desktop Applications
    Replies: 1
    Last Post: 08-11-2009, 01:01 AM
  4. sorting a character field
    By jbcc in forum Alpha Five Version 5
    Replies: 6
    Last Post: 04-04-2004, 12:25 PM
  5. Sorting Numbers in Character Field
    By JayMoskovitz in forum Alpha Five Version 5
    Replies: 4
    Last Post: 11-17-2003, 01:43 PM

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
  •