auto serial problems

[cherphas]

Ok, i've search everywhere for this...
I've set a up a field as a "Primary key", with auto-number. When I delete a record, the number in the field doesn't adjust itself. For example:
I have records 1,2,3 and 4, the value of primary key in record 4 is "4". If I delete record 4, I have records 1,2 and 3. If i click New Record, I have records 1,2, 3 and 5, it should be 1,2,3 and 4. How can i fix this?

 

[ImStevenB]

Unfortuantely, you can't. The idea of using the auto entered serial number is so that each record has a unique id/number attached to it. You wouldn't want this to change if this is your primary key field - it should be a unique number.

You could create a script that would assign the serial number based on "gaps" that appear in your numbering - but eventually that would slow down the file when it had to check for openings.

Another option would be to assign Status(CurrentRecordNumber)as a calculation for the ID number field. This way if a record does get deleted, the numbers will adjust. This defeat the purpose of a unique ID though.

HTH Im Steven B

http://www.myarcadia.net

 

[JeanW]

Im Steven is right.

Once I used a 'workaround'.
Build a calculation field based upon the date of creation of the record.
Take all the records, sort on creation date and with a loop assign a number to each record.
This script was performed by opening the file.

But again it was a workaround that fit in the application.

JW

 

[Calyx]

If auto-numbering is basic (like 1,2,3,4), remove auto-number from your primary key. In your new record script, add a set field step that will set the primary key number to the current record count number.

The script can look something like this.
Go to record/request/page (last)
New Record Request
Set field ["fieldname", "Status(CurrentRecordCount)"]

Of course, this will only update the next record if the last record was deleted. If the last record deleted had 4 as its primary key, then when you create a new record, this will assign 4 to the primary key. If you have 4 records and you delete primary key 3, when you create a new record, this will assign 4 to the new record (causing duplicates in your primary key.

If you have a more complex numbering (like A004 or something like that), then you can set the primary key to the max number of that field + 1