Autoincrement via table rules

[madtowng]

I put together a test table, complete with field rules.
Let me know if I'm on the right track.

[Tom Cone Jr]

One way to think about this is to imagine that the sort sequence for character field values compares characters in each field from left to right. The field values are initially sorted by the characters in the first postion; then field values are sorted using characters in slots 1 and 2; then field values are sorted using characters in slots 1, 2, and 3 are sorted A to Z, and so on. The numeric equivalent of a character string is irrelevant.

Compare
Record Number order | A to Z (char data) | A to Z (numeric data)
100 | 100 | 100
200 | 105 | 105
105 | 1100| 200
989 | 200 | 206
1100 | 206 | 989
206 | 989 | 1100

Check the Customer_id field values in the Customer table in AlphaSports to see the effect of padding character field values with leading zeros when you want to use the numeric equivalent of the character field values.

[Ronald Anusiewicz]

Goal: We want to change the auto increment seed number of the PK of a dbf table. The PK must be a character field. That said I’m going to make some assumptions.

1. Backup up your workspace.
2. The seed number is to be “10000”. I recommend “10001”. That way the PK will not be zero based.
3. I’m assuming this table is stand alone and not linked to another table or if so, not linked using this PK.
4. I would first go into the field rules and change the field from auto-increment to user entered.
5. Next, go to the operations tab and create, do a update records for that table using the genie.
6. Under the character tab, choose assign serial Values.
7. Fill out the genie dialog and run the operation.
8. Then go back to the field rules and set the PK field back to auto-increment.
9. The next new record will be auto-incremented.

Note: if necessary, this whole process can be automated using xbasic. I’ve done this many times myself.

[madtowng]

Great minds think alike (for the most part).
Obviously be default, I still need to get used to auto_increment working properly on character fields.

[Al Buchholz]

Another option is to use one of the user defined increment functions that have been posted on the messageboard and not use the Alpha auto-increment option.

Then the data doesn't have to change, but might need to for other reasons.

Since Alpha's auto-increment relies on an index for determining the next value, you are not concerned about index corruption causing wrong values to be used.

And added benefit is that you can develop a more sophisticated id but I rarely recommend that.

[Terrymorgan]

Try turning off auto increment, add a record with the number before the next one you require, then turn back on auto increment

[Al Buchholz]

Try turning off auto increment, add a record with the number before the next one you require, then turn back on auto increment

The Alpha routine doesn't look at the last record. It looks at an index of descending values and increments from the largest value.

I think Alpha 4 (DOS) looked at the last record to increment from.

[madtowng]

Seems that most everyone is forgetting that the recno() variable alpha assigns to every dbf table is an auto-incrementing variable. This is the reason my solution for this
question has always involved recno(). The only time a new problem will arise is when someone decides to pack the table, which of course would reset the field for however
many records were deleted.

[Al Buchholz]

Seems that most everyone is forgetting that the recno() variable alpha assigns to every dbf table is an auto-incrementing variable. This is the reason my solution for this
question has always involved recno(). The only time a new problem will arise is when someone decides to pack the table, which of course would reset the field for however
many records were deleted.

recno() is not only affected by a pack, but also if the order of the records in the table changed by other processes - like a copy to another table name with a new order.

Has a limited life span of accuracy so I avoid it unless it's being used in a very short time frame from finding a value and then pointing to another table with that value.

Ie print the current record...

[DaveM]

Having used ai for many years, had much better results using numeric and thwt was also a connector field just mking sure other tables had an identical fiel that got filled from master table
Use your own methods that are different if you like.