Unique Serial Numbers

[T-Square]

Over the years, I have bemoaned Filemaker's apparent lack of a truly unique serial number field. Imagine my profound surprise when I found that FM has a hidden unique serial number field.

Filemaker uses numbers, just like the rest of us, and they're hidden under the Get(RecordID) function. Setting up a key field using this field is as simple as auto-entering the Get(RecordID) calculation in a numeric field. It is NOT possible to create duplicate entries using this number; FM manages it quite simply. There's no need to worry about re-sequencing Auo-Entered Serial numbers and overlapping IDs.

Now, I know that importing new records into a relational structure using this field definition is not a straight-ahead routine, since the hidden RecordID is not mutable. The imported records get new IDs.

Alas!

Today I spent writing the scripts that import my data from one file to the next, porting old IDs into dummy OldID fields, which I then locate in related tables and replace. It's not perfect (in fact, it's kinda slow) but it gets the job done.

Thinking about the need for Absolutely Eternally Unique IDs, it seems to me that ANY attempt to import multi-relational data will run into import issues. Even different installations of industrial strength databases are likely to have overlapping ID numbers, so the developer must have routines to handle these eventualities.

I am moving back to using number key fields based on the RecordID, given that the database files using other techniques have tended to grow at a rapid clip.

[Vaughan]

The internal record ID has been around for a long time... the Status( CurrentRecordID ) function was introduced in FMP 4.

"It is NOT possible to create duplicate entries using this number; FM manages it quite simply. There's no need to worry about re-sequencing Auo-Entered Serial numbers and overlapping IDs."

Sorry, but it is possible to create duplicates.

If you export records from one database and re-import into a clone of the same file made at an earlier time, new records may be assigned record IDs that had already been used in the older database. This is because FMP keeps a count of the number of records created *over the life of the database* and the record ID is based on this count.

For this reason it is NOT recommended that the Record ID be used as a key field for relationships.

From TechInfo Article 2784:

"The ID numbers FileMaker Pro generates are sequential but not contiguous due to the way the numbers are generated internally. It is not intended to be used as an incremental serial number, counter, or other similar function."

[T-Square]

Vaughan--

Yeah, I see the Status function in 4 (I went back to my install of 4 to see for sure).

Maybe I haven't got all the problems worked out, but here's what I come up with:

1) I capture the key field value of the importing records,

2) Pipe it to another field (say OldID)

3) Generate a new internal ID, based on the current iteration of FM,

4) Post process the related tables, finding the old ID in related records and replacing it with the new

As I said, it's slow, but it works.

Also as I noted before, any duplicated instance of a particular database has the potential for this kind of collision. If you have two AUTO-INCREMENT fields in duplicate MySQL databases, they're going to generate the same ID sequences, and any related tables you build from there will have the duplicate foreign key problem. The only longterm solutions are: 1) run a centralized database, or 2) work out remapping your record IDs and related tables.

I think the TechInfo Article you note is important insofar as the terminology they use: they warn against using it for incremental serial numbers or as record counters. As a unique pointer to this specific record, I don't see how it could be beat.

[Vaughan]

Maybe I'm missing something here, but I see no advantage to using the RacordID as you suggest, than to use the standard auto-entered serial number.

In fact the auto-entered serial number has the distinct advantage of being easy to reset, something that the internal recordId does not offer.

If you're after some kind of fool-proof serial numbering system, I don't think it exists; not to cover all eventualities.

Your 4-step work-around posted above is more complex that that needed for a normal serial number...

1) import records

2) see what the last serial number is

3) set the field's serial number to the next value

[T-Square]

Vaughan--

This is a belated followup to your last post here.

My main problem with the auto-entered serial number is that, even if you define it as a Unique field, FM allows duplicate numbers to be used. The way that this usually trips me and my clients up is that they report a bug, I fix the bug and email them an update. The update gets installed--and because my dataset is smaller, the Next Serial number to use is *lower* than theirs, and they start adding new records that get duplicate IDs. Then all hell breaks loose. You can't delete the new record, because the relationships to other files (which are set to perform a cascading delete) end up deleting all the linked records for the *original* entry. Ugh!

The original workaround I used was to implement Captain Kurt's Unique ID function, but the resulting 20-character string looks ugly in dropdown lists AND causes a file to balloon in size.

So, that was why I looked into using the RecordID instead.