Prior to the uuid function, you would use a serial number on creation, no? Is not that just a fancy way of placing an auto-enter field value into the record? From where I sit the auto-enter calculation is the same thing. From my understanding, a uuid by definition is universally unique. I would and do feel safe using it as a table key, just don’t allow for replacing existing values. I know you won’t, but lurkers may need the clarification. It seems to be the encouraged behavior from FMI as well, at least one of the youtube videos I watched today did.
I've been using UUIDs as primary keys in tables for a while. So have some other folks. However, I used a strictly numeric UUID rather than the hexadecimal (text) representation of an RFC 4122 UUID because my testing found that number fields performed better in finds and resulted in a smaller file. Since the Get ( UUID ) value would have to be stored in a text field, I'm eager to re-test and compare the results.
Thanks for the reply.
I don't believe the auto-enter calculation is the same as the calculation field. At least in my tests, they are not.
4 different records were generated with the new record command. The calculation field on the left creates a different uuid than the autoentercalc text field on the right.
In the FM12 Help file on the function it states:
Both are set to the same stored option, yet they produce a different UUID for the record.
I understand that the UNSTORED calculation will re-evaluate each time the field is visited, but I am not clear why these two fields produce different results and which one would be the best choice to use for a unique record ID.
uuidvsaeuuid.png 26.6 K
Get ( UUID ) returns a new value every time it is called, even if you call it multiple times for the same record. For the application you're describing, the text field with an auto-enter calculation is what you want, I think.
You misunderstood me, I am advocating the text field set to auto-enter calculated results, w/ don’t enter if value already exists.
NOT, the calculation field.
Love the value that a UUID can bring, but I'm wondering what you and others are doing with regard to a smaller ID that is more usable by end users.
For example, my clients have forever referred to Project records by referring to the ID number (i.e. "Project 12345"). They would have quite an issue if I told them they needed to refer to a project using "14A68421-080F-409F-958C-8518A6A1D730".
I look forward to hearing your thoughts.
Surefoot Database Consulting
UUIDs can also make very long 1D barcodes. For human readable values, I usually make separate "id" (UUID) and "invoiceNumber" (serial increment) fields, for example. The UUID is used under the hood as the primary key, and the invoiceNumber is what's exposed in the user interface. In intermittently connected distributed systems (remote FileMaker Go users that periodically sync with a server), the UUID will exist on creation, then it has to be uploaded to the server for a server-side script to loop through new records and add an invoice number that will be consistent system-wide.
I can see one advantage to using a (stored) calculation over the auto-entered text field: in the former case, duplicating the record will yield a unique UUID value; in the latter, it will not.
You can't set a calc field, which prevents you from creating related records that way. It also makes it more difficult to import records.
I always use an auto-entered field which has, so far, only one downside: dealing with Duplicate record and importing.
If you have "Do not replace..." checked then duplicating the record duplicated the ID. If you have it unchecked, then when you import that key, you can't update auto-enter fields (which replaces the PK field you're importing). I set it to Replace, then import the key with not updating, then import again, matching on the key and updating.
When we un-check the "Do not replace existing value of field" box the auto-entered value will be get a new UUID when duplicating a record. Otherwise one can always script a Set Record ID command as a part of a Duplication script.
I am eager to see the results of your new tests. The last were quite impressive. I had not noticed this change when playing with the pre-release version, however the number based UUID tests still give reason to keep things as I have set them up with your UUID custom functions as opposed to Get (UUID)
Thanks all for your replies and information.
Tim, I did miss what you were saying in your first post. Thanks for explaining it further with the second one.
David, thanks for the additional information on importing. That will be important to keep in mind.
Also, Jeremy: I appreciate your post on two id's: one more visually appealing to the user.
I am not quite getting what you are saying here. What I am understanding is that you un-check the box "Do not replace..." so that when you duplicate a record it can set a new UUID on the duplicate record.
What are you saying about importing? So, if I import records the UUID key is a stored value in a text or number field and the values as they are in the Old data will import. What I cannot do is update the key during an import when the box "Do not replace.." is unchecked on the Auto Enter field?
When do you want to replace the value? Maybe I am just not fully awake this morning? If you can clarify this I would appreciate it.
>When do you want to replace the value?
I do a lot of imports where the primary keys are generated outside of the database, usually a worker in the field accessing a local copy. One of the great features of UUIDs (I've been using Ray Cologon's, uid_Create) is that I can create records without worying about duplicate IDs. So, if I want to import parent and child records, I need that UUID to be the same. Also, doing a conversion from non-normalized data requires a bunch of relationships and calc fields that I don't want to clutter my development database with so I build an Import database, that requires a lot of keys to be imported too.
So what I'm saying is, you can set your auto enter calc to Do not replace (checked) or Replace (unchecked). If it's "Do not replace" then duping a record keeps the ID. You can script it, but it makes me nervous to have the primary key in normal use relying on a script. If it's Replace, then duping makes a new ID. That's all and good. Except that choice has implications when importing.
When importing you run into trouble. In the import dialog, you have a couple options. The first is Import Actions ( Add New Records, Update Matching), the other is the Import Option dialog that pops up after you ok your field list. You can Perform Auto-Enter options or not.
If you're set up this way:
Auto Enter Calc: Replace
Import Action: Add New Records
Import Option: Perform Auto-Enter
Result: New IDs on Import
Auto Enter Calc: Replace
Import Action: Add New Records
Import Option: Don't Perform Auto-Enter
Result: Correct IDs on Import, but no auto-enter for anything else!