It sounds like you need to add a primary key field after the fact and will have to do a script comparing the two tables to make the link and you'll invariable have a number to cleanup. You'll have to do some comparisons between the two tables and I invariable run a looping script to compare and if it only finds one match, assign the ID, if it finds more than one (or none) it doesn't assign it and then loop a different set of criteria. Its a pain, but needs done.
btw, there are number of ways to do a key such as serial number or UUID. The is my preferred unique ID field. If I'm building a key after the fact, I would change F3 to be the creation timestamp if you have it. As a number field, this tends to be faster than a text field for searching and sorting. It converts the UUID field from Hex to Decimal and it adds the timestamp to the front of the ID to keep things in sort order of record creation. Just a suggestion.
Let ( [
F1 = Get ( UUID ) ;
F3 = Get ( CurrentHostTimestamp ) ;
F4 = GetAsNumber ( F3 ) ;
F6 = F4 & Substitute ( F1 ;
[ "0" ; "00" ] ;
[ "1" ; "01" ] ;
[ "2" ; "02" ] ;
[ "3" ; "03" ] ;
[ "4" ; "04" ] ;
[ "5" ; "05" ] ;
[ "6" ; "06" ] ;
[ "7" ; "07" ] ;
[ "8" ; "08" ] ;
[ "9" ; "09" ] ;
[ "A" ; "10" ] ;
[ "B" ; "11" ] ;
[ "C" ; "12" ] ;
[ "D" ; "13" ] ;
[ "E" ; "14" ] ;
[ "F" ; "15" ] ;
[ "-" ; "" ]
You might want to try setting up a field called record_id and setting its value via script using Get(RecordID). Then compare that value on subsequent duplicates of the same record and see if the value is the same. Have never tried this, but am assuming that when the database was duplicated each year, it truly was a duplicate and FileMaker didn't assign new RecordID's to each record. Wouldn't think it would, and if it didn't, this can tie records together as RecordID is FileMaker's internal unique identifier like a RID (Row ID in db2 for example).
Ah, Get(RecordID)... that's what I was looking for! This worked, although rather than a script I used a Replace Field Contents command, specifying a calculated value equal to Get(RecordID).
The RecordID was maintained from duplicate to duplicate, although there was a point in 2009 where the RecordID value prior was not the same as the RecordID value after... It could be that someone imported into a new database rather than duplicating or it may have coincided with a file format change (to .fp7)... not sure, but pointing that out as a possibility in case anyone else encounters this problem.
Get ( RecordID ) is a unique value for a record at that time but should never be used for "ID". Instead use auto-enter serial or UUID. If you export the records and re-import the Get ( RecordID ) will be different for the 'same' record.
Just a side note: instead of inserting rhe record id by sceipt, you can simply define it as a calculation. just a bit easier and faster.
I agree with Beverly that Get ( RecordID ) should not be used as a unique identifier for records except in very limited special purpose circumstances.
It's very simple to assign a serial number or UUID (Get ( UUID ) ) to a new field in existing records by using Replace Field Contents with either the serial number or calculation option.
we all agree, but did you read the question?
I did miss that detail, but the issue described by Beverly may still result in mismatched records if there has been any need to import data from one file to another as that can result in changed record IDs in on copy of the data and not another.
In my case, I was consolidating the multiple database files by importing into a single table in a new database, which would change the original RecordID. To preserve the original RecordID, I used Replace Field Contents specifying a calculated value prior to importing into the new database.