This is a long-time bug in FileMaker Pro, dating back many years, which still hasn't been fixed in FileMaker Pro 14.
We depend heavily on FileMaker Pro's "auto-enter serial number" feature (located within any field's options) in order to generate primary keys for our records. Because we are using serial numbers as our primary keys, we must have these primary keys be unique values.
However, it is extremely easy to confuse FileMaker so that it generates hundreds or thousands of DUPLICATE serial numbers. This can really destroy the integrity of a database system, because FileMaker should not be generating duplicate serial numbers for any records.
All you need to do to trick FileMaker is to take these easy steps:
1. Create a simple database. Put in a few text fields, a few number fields, even a few text fields that lookup from other tables. One of the number fields should be called "serial number" and it should have its options set to auto-enter serial number upon creation.
2. Host your database on FileMaker Server for OS X.
3. Then, get 3 users to access your database from 3 different OS X computers.
3. On OS X computer #1, that user should run a simple script that simply creates new records in a loop. Every new record will automatically generate a new serial number in the serial number field, because the serial number field is set to "auto-enter serial number upon creation". Every serial number will be unique. This is actually how FileMaker is supposed to operate. No problems so far. Everything is working just fine so far. So far, so good. Everything is perfect.
4. But then, on OS X computer #2, WHILE THE FIRST COMPUTER IS STILL CREATING NEW RECORDS, this computer should run a script that IMPORTS RECORDS into your table with AUTO-ENTER TURNED OFF. Don't import any data into the serial number field... just leave that serial number field blank upon import. After the import is complete, the script should then REPLACE FIELD CONTENTS on the serial number field. What should it replace field contents with? Choose "Replace with serial numbers: Entry options" and check the checkbox that says "Update serial number in Entry Options".
5. On OS X computer #3, while the first 2 computers are in the middle of running their scripts, the 3rd computer should run the exact same script that computer #2 is running. In other words, computer #3 should import records with AUTO-ENTER TURNED OFF. It shouldn't import any data into the serial number field. And after the import is complete, the script should then REPLACE FIELD CONTENTS on the serial number field. Choose "Replace with serial numbers: Entry options" and check the checkbox that says "Update serial number in Entry Options".
6. Be sure that all 3 computers are running their scripts at the exact same time.
When you're done with all of this, you will end up with a database that has a ton of duplicated serial numbers. This is the bug, because the serial number field should ONLY have unique data in it, because all the scripts are depending on FileMaker's "internal engine" to generate unique serial numbers.
So what is causing this bug to happen? It is a combination of:
(a) importing records with auto-enter turned off
(b) FileMaker's "Replace Field Contents" script step.
If auto-enter was turned ON during import, this problem wouldn't happen at all. But we are unable to turn auto-enter ON during our import, because we have a ton of lookups that we DON'T want to trigger during our import. So we are forced to turn auto-enter OFF during our import.
Once you turn auto-enter OFF during your import, that's where the problem begins. After you TURN OFF auto-enter during your import, the "Replace Field Contents" script step is what will fail you. The "Replace Field Contents" script step doesn't take into account ANY OTHER SERIAL NUMBERS THAT ARE CURRENTLY BEING CREATED by other computers on the network at the exact same time that it is running its function. The "Replace Field Contents" script step is just living in its own world -- humming along and creating new serial numbers, without actually paying attention to serial numbers that are currently being generated on other computers.
Yes, I know that we now have some new workarounds for generating primary keys instead of using serial numbers, primarily the Get(UUID) function. But we have some legacy solutions which we cannot change at this point, and they are dependent on serial numbers using FileMaker's internal engine to generate serial numbers. And we have to leave auto-enter turned off or else all of our lookups will trigger when we don't want them to trigger.
Turning auto-enter ON during import actually fixes this problem, but we can't turn it on during our import.
Would be really great if FileMaker Inc. could please fix this bug. Of course, AN EVEN BETTER long-term solution would be for FileMaker Inc. to give us MORE GRANULAR CONTROL over the "auto-enter" functionality upon import. Instead of only allowing us to turn on or off auto-enter for the ALL THE FIELDS IN THE ENTIRE RECORD upon import, we should be able to turn auto-enter on or off on a field-by-field basis during the import.