Auto increment jumps to 900000???

[Bill Broyles]

Have I ever messed up somehow?? I have a "sales" dbf table with an autoincrement field with a default of

 ALLTRIM(STR(RECNO("sales")))

Now when I go to add a new record, the default has jumped to 900000 instead of 80??

Where does the autoincrement get/store it's number? How can I fix this on my customers computer?

TIA
Bill

[Tom Cone Jr]

Re: Auto increment jumps to 900000???

Bill, your auto-increment field is CHARACTER data. Your default value expression seeded the table with a starting value of "1". This is bad. Why?

From the helps:

Note : New auto-increment field values always have the same number of characters as the previous value. The length of the auto-increment field does not affect the length of the values that are generated.

Your starting value (the first id field value in the first record in your table) had only one character. This creates a dillemma for the field rule when you get to the 10th record, since two digits would then be required. I think the conflict between the default value field rule and the auto-increment field rule is causing the problem.

To use a character type field for auto-increment your first value (the first field value in the first record) should be padded on the left with leading zeros, sufficient to pad out the entire width of the field. Here , with a width in the table structure of 6, I believe, you should have seeded the first record with a value of

"000001"

To fix this you would first back up the customer's data a couple of times.

Then you'd remove the auto-increment field rule.
Then run an update operation to supply corrected field values in each record. Beware of breaking links to related tables if any exist. They need to be updated as well, of course.
Then validate the changes carefully.
Finally, put the auto-increment field rule back in place.

-- tom

[robert]

Re: Auto increment jumps to 900000???

tom got here while I was typing-

I don't know if this has anything to do with it but

Note from the Alpha help-
Using the record number to create a unique record ID is not a good idea. The value returned by RECNO() is only a physical position in a table. It can change if you delete a record and then pack the table.

RECNO() is used in your data entry default in the data entry field rules for Id.

I could reproduce your problem in V8.

When I duplicated the table using utilities\duplicate and included indexes, records, field rules etc., it quit doing it. However, now it doesn't put anything in
Id when you tell it to create a new record.

When I changed the Id field from auto increment to the calculated field you had in the field rules, it behaved appropriately.

[Bill Broyles]

Re: Auto increment jumps to 900000???

Tom thank you for the help. You said

Then you'd remove the auto-increment field rule.
Then run an update operation to supply corrected field values in each record. Beware of breaking links to related tables if any exist. They need to be updated as well, of course.
Then validate the changes carefully.
Finally, put the auto-increment field rule back in place.

Is there a way for me to use a script to set the auto-increment field rule off? My customer only has the A5-Runtime, and I don't see any way to edit the field rules. Can they be set with a script?

TIA
Bill

[Stan Mathews]

Re: Auto increment jumps to 900000???

Turn off...

tbl = table.open("yourtablename")
rul = tbl.rules_get()
rul.change_begin()
rul.fieldnametoturnoffautoincrement.data_type = "User"
rul.fieldnametoturnoffautoincrement.Default.Default = ""
rul.fieldnametoturnoffautoincrement.Default.Type = "None"
rul.change_end(.t.)
tbl.close()

Turn back on...

tbl = table.open("yourtablename")
rul = tbl.rules_get()
rul.change_begin()
rul.fieldnametoturnonautoincrement.data_type = "AutoInc"
rul.fieldnametoturnonautoincrement.Default.Type = "Simple"
rul.fieldnametoturnonautoincrement.Default.Default = quote("00001") '<-change this to whatever your real default might be
rul.change_end(.t.)
tbl.close()

[Bill Broyles]

Re: Auto increment jumps to 900000???

Thanks Stan, I think I can do what I want with this help...

This message board is so much help for my pea brain...