BlogFileMaker

Creating a UUID in FileMaker 17: Introducing UUIDNumber

By May 15, 2018 9 Comments
In version 12, FileMaker introduced a function for generating a Universally Unique IDentifier, or UUID. It follows an internet standard for generating a string value designed to be unique across space and time. These are also known as a Globally Unique Identifiers.

Why Do I Need UUID?

UUIDs have a broad range of uses. Because they are intended to be unique, you can use one as a Primary Key in database design. We all use Primary Keys in our tables to create relationships. (You DO use Primary Keys, right?) Using a UUID as a Primary Key in and of itself is not bad design, however there are considerations to be made.

You might be used to using a serial number for a Primary Key, which is valid. However, using a UUID takes advantage of easier syncing and importing from multiple users. Your keys will, theoretically, never duplicate, and you can avoid collisions.

All UUIDs Are Not Created Equal

There are several different methods used to generate a UUID. The FileMaker function Get (UUID) will generate a text string containing numbers and letters. It therefore must be saved as a Text type of field and indexed as text.

Get (UUIDNumber) is also designed to generate a universally unique value but without including any letters. This makes it suitable to use as a Primary Key defined as a Number field. This new function generates a 30-byte value, opposed to a 72-byte value for the text UUID.

“But,” you say, “I can put letters in a number field and they work.” Yes, you can, but using them as keys to relate to other values effectively strips the letters, leaving only numbers in the index. You can see this by using the GetAsNumber function on any string.

If you do that, you will likely experience a namespace collision, since the value may not be unique when only its numbers are considered. Instead, use the new Get (UUIDNumber) function, and define the field as a Number if you need a Primary Key field.

Field Indexing

If you use a UUID for Primary Keys as well as the Get (UUID) function, your keys are all text. This can get expensive in terms of maintaining a larger index and can have implications for scaling. Number fields, on the other hand, are handled differently than Text fields, including how they are indexed. This makes a big difference as your table grows.

Of course, you can use other methods of generating a number UUID, including Custom Functions and Plugins. The benefit of having a built-in function is that you can rely on it being available when you need it. This makes your code more portable, if, for example, you need to copy/paste fields from a table in one file to another. It is also best practice to adhere to native functionality where possible. This ensures your solutions are scalable and supported as much as possible.

Plan for Usage

I look forward to utilizing Get (UUIDNumber) for solutions that are built specifically for FileMaker 17 and beyond. If your deployment includes prior versions of FileMaker, you cannot use Get (UUIDNumber). If you use any of the 17 only functionality, you require only FileMaker 17 clients as the low bar for entry. Do so by selecting the menu item File > File Options… and set Minimum version allowed to open this file to 17.0.

Moving Forward with FileMaker 17

If you have any questions about how to use the UUIDNumber or any other new features included in FileMaker 17, please contact our team. We’re happy to help your team determine the best way to leverage them within your FileMaker solution.[space=”1″]

References

Mike Duncan

Mike Duncan

Mike is an AWS Certified Solutions Architect as well as a certified FileMaker Developer. In addition to his work, Mike also enjoys pursuing his art, freelance writing, traveling, and spending time with his family.

9 Comments

  • Avatar Nihm Brisby says:

    If a developer feels confident with all the potential pitfalls of porting a solution from a Get (UUID) approach to Get (UUIDNumber), would you recommend they do so? I have a large solution that not only searches UUIDs, but uses “List of” to send lists of UUID’s to the server in PSOS parameters (to recreate the user’s found sets). Do you think there will there be a noticeably impact on performance of number vs text?

    • Avatar Mike Duncan says:

      I would be cautious with replacing a key field that is already in use. Beyond that, it may be worth it, and could be hard to test the performance difference. Partly because it depends largely on the solution, and will be felt more as time goes by and record count and file size increase.

    • Avatar Mark Kimbrell says:

      Note that Get (UUID) returns a 36-character string, and Get (UUIDNumber) returns a 57-character number. Under the hood, FileMaker stores both Text and Number fields as strings. Thus, records using Get (UUIDNumber) will take up more space*. When you are looking at a “List of” IDs as a script parameter, you are dealing with one big string of data. Thus, passing a list of Get( UUIDNumber) to the server would be a larger data payload than a list of Get( UUID).

      *Huge caveat: this does not seem to be extend to indexes. Number indexes take up much less space than text indexes. Given that you will almost always index your IDs, the total storage efficiency of a field & index pair tips in favor of Get(UUIDNumber) at a fairly low record count. You can create test files to observe these behaviors.

  • Avatar awildorn says:

    Just realized that using UUIDNumber stored as a Number Field Type, will cause a massive headache when you try to export these UUID’s in excel. FileMaker Exports the the field as displayed in scientific notation. I’m sure there is a way to fix this, but it just adds another thing to keep in mind, when exporting… and there is already so much…Simplicity is better. I’d love to use UUID as a number for indexing benefits, but it’s seems less valuable now that this issue persists as default.

    • Avatar Mike Duncan says:

      This is actually an issue in Excel. The data will get exported correctly, but when you open in Excel, it uses “general” format by default which will change the data. Same thing happens with zip codes when exporting and opening in Excel. If you look at the exported xml, part of the xslx format, you will see the correct values.

  • Avatar Glen Cardenas says:

    Actually, I’m running into this issue with MirrorSync sending keys back and forth to the iPad. The iPad generates a 24 digit number key and when synced back to FM server, the key is intact. However, when this key is sent back to the iPad it turns into scientific notation of 16 digits and an exponent. If the above problem was the fault of Excel, what do you think is causing my issue?

    • Avatar Mike Duncan says:

      You might check the layout to see if the field is formatting the number in any way. Click into the field to try to see what the actual contents are, or use the data viewer. Otherwise, could it be an issue with Mirrorsync?

  • Avatar Bryn Behrenshausen says:

    Mike do you know if the UUIDNumber is unique to the table or across the whole file?

    i.e. if I have tables:

    Organizations
    Individuals
    Tasks

    if i use UUIDNumber for the PKey of all, and have a foreign key field in Tasks that puts the UUIDNum of the Organization or Individual in it, would I potentially have Organizations with the same UUIDNumber as an Individual? Trying to think how I can keep from having multiple foreign key fields in a table that relates back to multiple tables if I can avoid it.

Leave a Reply