FileMaker Tip: How and Why To Use Universally Unique IDs (UUIDs)

NOTE: Requires FileMaker Pro Advanced to setup.

When setting up your database - you probably already use some sort of unique record id that you can use when you relate data to/from another table. It's called a primary key. And, if you're like most of us, you probably use the handy unique number auto-enter option:

Now, there's absolutely nothing wrong with this approach - and most of the time - it will work just fine. However, I was working on a project for a customer the other day - and while their solution was working fine - they wanted to be able to create a runtime for their remote salespeople who sell in areas with limited Internet/phone service.

Their problem was - once the sales folks entered the data in their runtime - how to get it back into the system without creating duplicates. After all, if you use a serial number for your primary key - and you have multiple runtimes - everyone will have the same value for the "next record."

For example, if you have a contact list with a serial number as a primary key (let's say the "next" value is 100) - and you create runtimes - then the first contact that Salesperson A creates will have a primary key (ID) value of 100. And, the first contact that Salesperson B creates will also have a primary key value of 100. Then say that each salesperson enters a new invoice (which will also have the same "next" primary key values) for each of their respective new customers.

When you go to import the records back into the "master" solution at the office - you would have to do all kinds of key replacements, finds, updates, etc to ensure that every record ID is unique. That can get complicated very, very quickly.

The good news is - there's a simple solution - it's called a Universally Unique ID - or UUID for short. A UUID is a set of 32 digits that is "practically" unique. Here's the Wikipedia definition:

A UUID is a 16-byte (128-bit) number. The number of theoretically possible UUIDs is therefore about 3 × 10³⁸. In its canonical form, a UUID consists of 32 hexadecimal digits, displayed in 5 groups separated by hyphens, in the form 8-4-4-4-12 for a total of 36 characters (32 digits and 4 hyphens). 

For example:
550e8400-e29b-41d4-a716-446655440000

Whoa! Cool, right? So how in the heck are you supposed to use that as your primary key in FileMaker? The short answer is: you need to create a custom function - and set the auto-enter value to a calculation. Don't worry - it's easy!

First - cruise on over to Brian Dunning's fabulous repository of custom functions - and search for "UUID" in the search box:

You'll see a number of different options. I like the one called "UUIDRandom" written by Jeremy Bante of The Support Group. Just go to the detail page for the function, copy the code in the box - and go back into FileMaker and choose File -> Manage -> Custom Functions. Click the "New..." button and enter UUIDRandom as the function name, and paste the code into the space provided:

Click OK and dismiss the Custom Functions manager window. Now choose File -> Manage -> Database... and in each of your tables create a new text field. You can name it whatever you want - but I suggest that you use "UUID" somewhere in the name.

Once the field is created click the "Options..." button and click on the "Auto-Enter" tab and click the "Specify..." button next to "Calculated value". Enter "UUIDRandom" (no quotes) into the calculation dialog:

If you're building a new solution - you're all set! However, if you're trying to integrate a UUID into an existing solution - there are a few more steps - but this whole process is strictly a one time thing:

• For each table - you need to create a UUID value for all the existing records:
• Add your UUID field to an existing layout (temporarily);
• Go into Browse mode and show ALL records;
• Click into the field and choose Records -> Replace field contents...
• Choose "Replace with calculated result:" and enter "UUIDRandom" (no quotes) in to the calculation dialog like you did when you defined the field.

Now you have a unique value for all your records. The next thing you have to do is to create another text field in all your related tables (ones that are linked using relationships) - so that there can be a foreign key that will relate the master record to the children records.

You need to populate these foreign keys in the child table with the primary UUID from the master table. For example if you have a relationship that links Customers to Invoices by Customers::Cust_ID = Invoices::Cust_ID then you need to create a new field in Invoices called Cust_UUID and put the value of the customers UUID into that field.

To do that - we're going to force a lookup of the simply go to your child table (in the above case "Invoices" table) - put the Cust_UUID field temporarily on a layout, choose to replace the field contents via a calculation (like we just did above) - but this time - use your relationship to enter the value of the master record. In our example - we would use:   Customer::Cust_UUID.

Once you've done that - MAKE A BACKUP OF YOUR FILE BEFORE CONTINUING!


The next step is a biggie - so take your time! You need to change your relationships to use the UUID fields rather than your existing "id" fields. In our example - our relationship that linked Customers to Invoices would now be based on Customer::Cust_UUID = Invoices::Cust_UUID.

After making the relationship changes - you may also need to go back into your scripts and if you do stuff like reference the Cust_ID field when setting variables, or you copy/paste it - you need to use the UUID field instead.

Yeah, I know. That's a lot of work.

BUT, in the end - you'll have a solution that uses UUIDs and you won't have to worry about record id collisions - and in the future when you create new solutions - if you use this scheme right from the beginning - there's no "pain" at all.