Alpha Video Training
Results 1 to 7 of 7

Thread: Changing Field from numeric to character problems

  1. #1
    Member
    Real Name
    Geoff Meredith
    Join Date
    Aug 2006
    Posts
    637

    Default Changing Field from numeric to character problems

    I created a table a long time back with one of the fields being a numeric auto-increment. Since this time I have got into the habit of using character types for my auto incrementing fields. I changed the numeric to character in the edit structure view of the table which had the knock on effect of changing the order of my table entries when viewed in the default browse. Instead of being in the order 1,2,3,4 etc. they are now 1,10,100 etc.

    Is there a way of getting the table order back to normal while keeping the field as a character type?


    edit: I realised the title "Renaming Field" wasn't the best description.
    Last edited by gmeredith17; 02-23-2008 at 11:07 AM. Reason: title misleading

  2. #2
    VAR csda1's Avatar
    Real Name
    Ira J Perlow
    Join Date
    Apr 2000
    Location
    Boston, Massachusetts, USA
    Posts
    3,530

    Default Re: Changing Field from numeric to character problems

    Hi Geoff,

    Quote Originally Posted by gmeredith17 View Post
    I created a table a long time back with one of the fields being a numeric auto-increment. Since this time I have got into the habit of using character types for my auto incrementing fields. I changed the numeric to character in the edit structure view of the table which had the knock on effect of changing the order of my table entries when viewed in the default browse. Instead of being in the order 1,2,3,4 etc. they are now 1,10,100 etc.

    Is there a way of getting the table order back to normal while keeping the field as a character type?
    Well, it's time to switch gears again! It turns out that some SQL databases do not support character auto-increment fields, and some do not support more than 1 auto-increment field per table. So if you plan on accessing via Alpha DAO in the future (which has some strong advantages), you might want to arrange tables to be more convertible to SQL.

    That said, if you want to sort the table with an index based upon the character index, you could use a sort/order expression like VAL(fieldname)
    Regards,

    Ira J. Perlow
    Computer Systems Design


    CSDA A5 Products
    New - Free CSDA DiagInfo - v1.39, 30 Apr 2013
    CSDA Barcode Functions

    CSDA Code Utility
    CSDA Screen Capture



  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: Changing Field from numeric to character problems

    Another way to get the sort you want is to temporarily turn off the auto-increment, update the auto field with an expression which pads the field values with zeros or spaces to the the defined width of the field, then turn the auto increment back on.

    Whether you would want to do this probably depends on how you use the field. If it is ever printed on any of your reports, etc then it would not align as it does presently.

  4. #4
    VAR csda1's Avatar
    Real Name
    Ira J Perlow
    Join Date
    Apr 2000
    Location
    Boston, Massachusetts, USA
    Posts
    3,530

    Default Re: Changing Field from numeric to character problems

    Hi Stan,

    Quote Originally Posted by Stan Mathews View Post
    Another way to get the sort you want is to temporarily turn off the auto-increment, update the auto field with an expression which pads the field values with zeros or spaces to the the defined width of the field, then turn the auto increment back on.
    The risk with doing this is if the auto-increment is a link field in a set. If that is the case, it will have to have the values updated in those tables as well to keep the links valid.
    Regards,

    Ira J. Perlow
    Computer Systems Design


    CSDA A5 Products
    New - Free CSDA DiagInfo - v1.39, 30 Apr 2013
    CSDA Barcode Functions

    CSDA Code Utility
    CSDA Screen Capture



  5. #5
    Member
    Real Name
    Geoff Meredith
    Join Date
    Aug 2006
    Posts
    637

    Default Re: Changing Field from numeric to character problems

    Ira - Thanks for the warning about the sql. Its not a problem with this project but appreciate the heads up for future ones.

    Stan - I was thinking along the same lines but didn't know how to approach it. Took a clue from your post and searched the help for 'pad' and lo and behold the padl() does the trick.

    I have put my code below for reference. I tried two methods one manipulating a variable and then assigning the result to the field and the other just manipulating the field. Does one method have any merit over the other?
    Code:
    dim tbl as P
    dim VCdrawnum as C
    
    tbl = table.open("drawlog")
    tbl.fetch_first()
    while .NOT. tbl.fetch_eof()
        tbl.change_begin()
        VCdrawnum=tbl.drawnum
        tbl.drawnum= padl(alltrim(VCdrawnum), len(VCdrawnum), "0")
        tbl.change_end(.T.)
        tbl.fetch_next()
    end while
    tbl.close()
    Code:
    dim tbl as P
    
    tbl = table.open("drawlog")
    tbl.fetch_first()
    while .NOT. tbl.fetch_eof()
        tbl.change_begin()
        tbl.drawnum= padl(alltrim(tbl.drawnum), len(tbl.drawnum), "0")
        tbl.change_end(.T.)
        tbl.fetch_next()
    end while
    tbl.close()
    Please note that I am new to xbasic and the above code is cobbled together from extracts from the help file. I welcome any suggestions or improvements that could be made to it.
    Last edited by gmeredith17; 02-25-2008 at 10:06 AM. Reason: wrote lpad instead of padl() in the reply to Stan.

  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: Changing Field from numeric to character problems

    No xbasic needed. An update operation would do the same thing.

  7. #7
    Member
    Real Name
    Geoff Meredith
    Join Date
    Aug 2006
    Posts
    637

    Default Re: Changing Field from numeric to character problems

    Thanks again Stan. Just tried creating an operation and it was very intuitive and worked. I'm still glad I did the xbasic routine as I am keen to get to grips with it as and when time permits.

    For reference:

    I used the operation genie. If you have the table open in browse mode and choose Tools -> create operation...

    From the menu that appears select 'update records' and click the 'create using genie' button.

    Click the 'general tab' and choose the 'Assign a calculated value to a field' option.

    Choose the field you want to manipulate and then create the expression using the expression builder. My Field was 'drawnum' and the expression
    Code:
    padl(alltrim(drawnum), len(drawnum), "0")

Similar Threads

  1. A5 renaming objects
    By Leo Cohen in forum Alpha Five Version 6
    Replies: 8
    Last Post: 11-12-2005, 06:22 PM
  2. Renaming a database
    By Stephen F Zaborsky in forum Alpha Five Version 6
    Replies: 2
    Last Post: 12-26-2004, 01:00 PM
  3. Renaming AlphaSports
    By jkukuda in forum Alpha Five Version 6
    Replies: 2
    Last Post: 12-06-2004, 08:31 PM
  4. renaming file
    By John Cunradi in forum Alpha Five Version 4
    Replies: 4
    Last Post: 03-02-2001, 05:08 PM
  5. Renaming Everything Question!
    By Bob Kieffer in forum Alpha Five Version 4
    Replies: 1
    Last Post: 05-01-2000, 11:26 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
  •