1 2 Previous Next 23 Replies Latest reply on Jun 26, 2012 3:07 PM by mr____

    Thoughts on UUID


      The new function in FM12 Get(UUID) seems like a real boon, especially for those deployments that will be both desktop and idevice.


      I am at the start of a small system, building it from scratch in FM12.


      My thought is to use the UUID as the key field for a record, since it is my understanding that it would be absolutely, totally unique.


      I am looking for opinions about using a calculation field for this primary key vs using an autoentered calc for the key.


      The reason I ask is that I often use a technique for creating child records that *pushes* the creation by matching a global field in the parent [null value] with the key field in the child. This does not seem to work if the key field in child is a calculation; it does if the key field is an auto-entered calc.


      Your opinions are appreciated.



        • 1. Re: Thoughts on UUID



          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.





          • 2. Re: Thoughts on UUID

            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.

            • 3. Re: Thoughts on UUID



              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:


              Returns a unique 16-byte (128-bit) string. For example, you can use this function to generate a unique ID of a record.

              For unstored calculations, returns a new string each time Get(UUID) is evaluated.


              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.



              • 4. Re: Thoughts on UUID

                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.

                • 5. Re: Thoughts on UUID

                  Hi Michele,


                  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.





                  • 6. Re: Thoughts on UUID



                    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.


                    Jim Medema

                    Surefoot Database Consulting

                    • 7. Re: Thoughts on UUID

                      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.

                      • 8. Re: Thoughts on UUID

                        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.

                        • 9. Re: Thoughts on UUID

                          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.

                          • 10. Re: Thoughts on UUID

                            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.

                            • 11. Re: Thoughts on UUID

                              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)

                              • 12. Re: Thoughts on 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.

                                • 13. Re: Thoughts on UUID



                                  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.

                                  • 14. Re: Thoughts on UUID

                                    >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!

                                    1 2 Previous Next