This is Matt from AppWorks and today we’re going to be talking about UUIDs in FileMaker databases. A UUID, there’s an article you can read in Wikipedia, is a way to have a primary key in a given table of data. Primary keys, what we used to do back in the day, is we had serial numbers. If you look at an invoice, it would be like 1 or 1001, and it would be unique for each record. A UUID is set by a function, the function called get UUID. It looks like this. It’s a very  simple thing, and it’s a very long number. You certainly wouldn’t want to see it.

Let’s just look at one of those so we can see what they look like on a layout. I’ll add this one here and put the ID field on the layout. We can see how long they are. It’s a big long number like this. You’d never want to print one on an invoice, you’d never want to show it to a customer. It’s just used behind the scenes to make relationships.

In this case, all the records on the person table are connected to the company table through this UUID field. We have the relationship here that goes from the company ID to the company ID over in the person table. Settings in the UUID field – there’s a couple that are defaulted in FileMaker that aren’t a good idea. Let’s take a look at what can go wrong and what will happen. 

If you go back to your graph and go into your ID settings, here’s what will happen if you set this wrong. You should definitely use a calculated value and it should be stored. That’s set up in the storage tab. You have minimal or all indexing or no indexing. Minimal indexing is all you need for an ID, because that’s the formatting for an index where it stores just the entire key as opposed to parts of it, like words in a sentence. The auto-enter settings, as we looked at a second ago, just have the get UUID function. 

If you have this box checked, it’s not a good idea because if you duplicate the record, it will keep the key the same in the duplicate, and you definitely don’t want to do that, so uncheck that. You do want this box checked, because it prevents a user from editing the record if they click it. For example, I’ll uncheck this and if I click ok and click ok, it will let me click into here and delete part of it. I’ll just cut this part off. Now I’ve broken the relationship to all of the person records. 

If you accidentally have a UUID field on a layout and you have not checked that box to make it not editable, you should definitely fix that. Once I change that, I’ll go ahead and commit those changes. Now I won’t be able to change it. If I try to change it, it tells me this action can’t be performed because the field’s not modifiable.

Two other important settings I recommend are on the next tab over, the validation tab. That’s these two right here – not empty and unique. If you have a UUID field that for some reason is empty, the user will have no way to fix it. This can happen sometimes if you import data from some other source and don’t’ check the box to import UUIDs or if you actually import data that has nothing in it and you let that actually be the primary key. You can sometimes end up with a null value, but if that happens, the user can’t do anything, they basically are presented with a box in some cases, the only way out of it is to force quit FileMaker or they get a confusing dialog that says revert record, which they don’t know how to deal with.

The same thing with unique – UUIDs need to always be unique, so this doesn’t do anything except slow FileMaker down, because every time it makes a record, it has to double check if everything’s unique. I would recommend unchecking those boxes because they basically don’t do anything. That’s it for best practices for UUIDs, except that you should use them in every table, pretty much all the time when you’re starting a database. Thanks for your time.