The simplest answer is not to use serial IDs, but rather to use UUIDs as key fields. You can generate them by way of the Get ( UUID ) function.
You can further automate the process by using synchronization, either by rolling your own process or using one of the commercial tools available, such as GoZync, MirrorSync, or EasySync.
Thanks a lot for this tip: This will prevent mixing 2 new records (in the parent table).
But there will be another problem:
If a new record is generated in the clinic, and another generated at home, we will end up with 2 records having the same serial Nb after synchronizing.
And this will end up with mixing daughter tables results (that are relying on serial numbers for filtering and showing data among different records).
Is there any solution?
And does the commercial tools deal with this problem?
That's why you use UUIDs and NOT serial numbers. In any table. Serial numbers are created in serial order (1, 2, 3, etc.). So you can and usually will get duplicates when you're working in multiple copies on multiple devices. But UUIDs are unique. So no matter what device they're created on, you don't get duplicates.
You'll have to go back and convert all your key fields from serial numbers to UUIDs. Assign it in the parent records first, then use the same UUID on all the appropriate child records. (It's usually best to create a second field to hold the new ID temporarily on the parent record, assign that UUID to all the child records, then go back and change the real key field to the new UUID on the parent record.) The Replace Field Contents command is useful here.
Once you convert your existing data, change the key field in the parent record from an auto-enter serial number to an auto-enter calculation using Get ( UUID ). From that point forward, all new records will get a new, unique identifier, whether it's on the main database or on one of the mobile devices.
This seems perfect.
I will try it.
But what I will try to do is simply change the serial from : auto-enter serial to : auto-enter calculation using Get (UUID).
So, all the previous records will rely on the previous serial, and the new ones will reply on the Get (UUID) [since the user is starting to use the solution at home from now on].
This should work, shouldn't it?
But there is no possibility to get same UUID twice?
What is the theoretical probability of this (especially that some records are created on 2 "different" solutions?
Assuming you've cleaned up all previous instances of duplicate keys, then yes, that would be okay. (I personally would prefer not to do it that way, simply for consistency's sake, but if you can work with it, there's nothing technically wrong with it.)
In theory, it's technically possible to have a duplicate UUID, but the whole point of a UUID is that it be "unique", so the likelihood is extremely small. The scenarios in which it could happen are generally based on automated functions that create large numbers of records in a short period of time, which don't apply to normal user interaction.
I will do this method because I find the other one more complicated and there is a possibility of mistake.
I want to thank you very much for your help.
You were extremely helpful.
And be sure you have made a full back of your entire solution before updating key fields in your live copy, as any error during the updating of Keys could make it extremely difficult to ever rematch the parent-child records if the key changes are not done in the correct order.
- The parent records need a new field, populated with the new Primary Key while the old Keys are still in use.
- Check that all parent records have a value populated in the new pKey field.
- The child records need a new field created for the new Foreign Key, and to have that field value captured via the old relationship.
- Check that all child records now contain a new fKey value.
- Then the old relationships can be updated to use the new Keys.
If you break the existing relationships before the Key values are updated in both tables, your backup copies will probably have to be copied again to get things working.
Yes, thank you, Stephen. I forgot to mention the backup part (DOH!).
Re "what I will try to do is simply change the serial from : auto-enter serial to : auto-enter calculation using Get (UUID)"
Another potential issue is that UUIDs are text, which will necessitate a change to schema if your existing serials are number values (i.e. a Number field, not simply numerals in a Text field). If your existing serial field is already Text, then you can just switch the entry method and carry on. If it is Number, you will have to change the field type to Text, and then also change any matching foreign key fields. That should work smoothly enough, but you will need to check carefully to ensure it doesn't trip you up.
Thank you Stephen for your help
Yes indeed, I paid attention for this issue.
Furthermore, serial numbers I usually use are text, because I try to have unique serial numbers even between different tables, so I put some text (pointing to the corresponding table) in the serial number.
Thank you very much for your help