2. I suggest linking old and new versions of your data in their respective records to the same related table to get the number generated as an auto-entered serial number. This will keep it from changing when you duplicate a record as both old and new copies would link to the same related number. The script can then update a second field with the next revision letter in the series.
YourTable::__pkInternalIDSerialNumber = Numbers::_fkInternalID
__pkINternalIDSErialNumber is an auto-entered serial number that is NOT the number you are discussing here. An auto-entered serial number in the Numbers table will server for that. You can then combine a "revisionLetter" field in YourTable with the serial number for Numbers to get the version numbering that you want.
thanks phil for your reply, just want to ask some questions about your recommended approach, i can't quite get my head round the formula for the script
do i create 2 ID fields (one with auto generated numbers, another without) and then one field for the versioning number?, the version number field will equate to the duplicated ID field?
Your description of this problem is typical for a system for managing technical documents or product specifications where you need to keep past versions of the document. I'm going to pretend that these are ISO documents just to get a name I can use to help distinguish the purpose of each field:
DocumentID : This field identifies a specific document. If, say you've revised a given document 3 times, all three records will have the same data in this field.
DocumentVersion : This field identifies the version of the document. In some systems it's a number, in others it's a letter. If you pull up all the records for a given document, the value in this field identifies the revision sequence. The oldest document might have an "A", "0" or "1" in this field, the oldest after two revisions might show a "B" ; "2" or "3" respectively. This is the one field that always changes (increments) each time a document is revised.
__pkInternalID : (I've simplified the name from my last post) This is an internal ID number that your average users need never see. It uniquely identifies each and every record in your documents table. Each time you duplicate the record, this field will automatically get the next number in the serial number sequence. In our example of a document with 2 revisided copies, this field might show 2000, 2050 and 2085 for the three versions of this document. __pk by the way is a common naming convention for identifying the primary key of a given table. The two underscore characters at the beginning of the field name make sure that is listed first anytime a list of field names sorted in alphabetical order appear when working with your database and this makes it a bit easier to find and work with this field.
You may or may not need to set up the related table. The relationship I originally described won't really work so I have to apologize and take a new approach at it. The only advantage to putting the field in the related table is that you can set up a unique values validation rule for the DocumentID or you can define it as an auto-entered serial number if you need to generate your own document ID's. (Your records may have DocumentID's already assigned to them from a previous system.)
The relationship would look like this (Note the changes):
Documents::DocumentID = DocumentIDTable::DocumentID
The other purpose for this documentIDTable would be to store any additional data that will be common to all versions such as the date the document was created.
Your script for duplicating a document record would be something like this:
Set Field [Documents::DocumentVersion ; Documents::DocumentVersion + 1] // this is for a numeric revision sequence, letters take a different expression.