Changing Field from numeric to character problems

[gmeredith17]

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.

[csda1]

Re: Changing Field from numeric to character problems

Hi Geoff,

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)

[Stan Mathews]

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.

[csda1]

Re: Changing Field from numeric to character problems

Hi Stan,

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.

[gmeredith17]

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?

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()

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.

[Stan Mathews]

Re: Changing Field from numeric to character problems

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

[gmeredith17]

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

padl(alltrim(drawnum), len(drawnum), "0")