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
- UUID – RFC4122
https://tools.ietf.org/html/rfc4122 - Get ( UUIDNumber )
https://fmhelp.filemaker.com/help/17/fmp/en/index.html#page/FMP_Help%2Fget-uuidnumber.html
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?
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.
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.
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.
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.
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?
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?
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.
Yes, UUIDs are meant to be unique. It is kind of there in the name Universally Unique ID.
We have a couple meetups next week dueling over which features/methods are better.
https://community.claris.com/en/s/feed/0D53w00005MIAOdCAP
One of the duels is over Get(UUIDNumber).
I’m reminded of the FileMakerStandards topic on Key Values where Jeremy Bante made an excellent case for using number fields for UUIDs:
https://filemakerstandards.org/display/bp/Key+values
Thus, on DIGFM’s Thursday subtopic of “Get(UUID) vs. Get(UUIDNumber)” I was sure that I would take the side of Get(UUIDNumber), except I got inside information that Matt Navarre believes the text UUID is smaller and faster. So ran tests of my own.
The UUIDNumber in number fields didn’t get smaller until both the text and number fields are indexed, but nothing drastic if using minimal text indexing.
Also the numbers were still somewhat slower on creation.
So I’m wondering if you can still help make the case for Get(UUIDNumber).
You are welcome to the meetup, by the way. If you don’t have much time, “Get(UUID) vs. Get(UUIDNumber)” is the very first duel at 5:30pm Pacific.
Some of my test results below.
+1000 records
8.948 UUID
8.331 UUIDNumber
+1000 records, Freeze Window
0.349 UUID
0.370 UUIDNumber
+10,000 records, Freeze Window
4.010 UUID
4.422 UUIDNumber
+1000 records with indexing, Freeze Window
0.497 UUID
0.455 UUIDNumber
+10,000 records with indexing, Freeze Window
4.747 UUID
5.018 UUIDNumber
+1000 records with indexing, Freeze Window
0.403 UUID
0.419 UUIDNumber
+10,000 records with indexing, Freeze Window
4.014 UUID
4.846 UUIDNumber
50,000 records
10MB UUID
8MB UUIDNumber
Export tab 34,000
0.193, .205, .204 UUID
0.295, .220, .221 UUIDNumber
Import tab Update matching 34,000
7.914 7.932 UUID
8.283 8.170 UUIDNumber
Sort 34,000
1.494, .491 UUID
1.327, .581 UUIDNumber
GTRR 34,000
6.178, .709 UUID
6.235, .700 UUIDNumber
GTRR 50,000
7.611, 7.173, 7.007 UUID
7.679, 7.011, 7.183 UUIDNumber
Other articles on the topic:
https://app.works/getuuidnumber-is-here-how-does-it-compare-to-getuuid/
https://www.soliantconsulting.com/blog/filemaker-uuid-number/
https://24usoftware.com/news/text-or-numbers—whats-faster
Thanks for the input, Eric. It has also been pointed out to me that another possible blocker may be if you are using the Data API, you might run into a limitation with JSON returning long numbers. So there may not be one best answer.