Basic FileMaker Meta Data

Friday 22nd August, 2014

Almost every table in a FileMaker file should have include a basic set of meta data fields. You might not need them to start with, but they can be awfully handy later on for all sorts of things. Everyone has their own personal take on ids and reference numbers, creation and modification dates, account names, and the tricks for making dealing with them as straightforward as possible.

So here’s mine. I thought I’d start with id numbers and serial numbers.

Create a new file

Let’s start by creating a new empty file called “Meta Data Demo”, saved to the desktop.

Manage Database Dialog

In version 13, FileMaker lets you create fields in layout mode, using the Field Picker floating window. Put me down as a staunch traditionalist: I much prefer to do the editing of fields and relationships inside the Manage Database dialog.

One post-hoc rationalisation might be that it stops you from getting into the “doing things on the fly” habit, but I suspect I’m just a reactionary.

Open the Manage Database dialog (Ctrl/⌘-Shift-D), and create a simple text field called id. With the field still selected, click the Options… button.

Dealing with overlapping serial numbers

Back in the day, the way to create auto-entered serial numbers for records was with the serial number settings options dialog. It’s still there, and still useful for creating human-only serial numbers. The problem with serial numbers that you generate using this option is one of orthoganality.

Suppose I have save a copy of my database, and give it to someone else to add some records of their own. At this point both our copies have the same serial number setup for the next time we create a record. Now suppose, I get busy and add some records to my copy, and she does the same and adds a few records to hers. The serial numbers of these new records now overlap. I can’t simply import her records into my database, without the chance of things breaking.

If your clients only ever access your files from FileMaker server this will not be an issue. But if you’re working on a standalone copy of the database (or say, ahem, on a database copied to an iDevice) then it’s something that might come back to bite you in the future.

The simplest way around this is to use the Calculated value option. Check the box, and up should pop a Specify Calculation dialog.

Universially Unique Identifiers

Since version 12, FileMaker has offered the Get(UUID) function, which the help succinctly defines:

Get(UUID) returns text representing a Universally Unique Identifier (UUID).

When using Get(UUID), FileMaker Pro will returns a unique 16-byte (128-bit) string. For example, you can use this function† as an Auto-Enter Calculated value to generate a unique ID†for a record when it is first created.

Note: Unstored calculations will return a new string each time Get(UUID) is evaluated.

What this means that Get(UUID) will return a random string of characters that is so random that it is statistically infeasible to get the same number twice. We can therefore use it to calculate a pretty much guaranteed unique number.

This number is perfect for uniquely identifying a record. However “random” tells you that this is not a serial number - you can no longer use it to determine the where in a sequence of records an particular record occurs. For this, depending on your data table you may want to create an additional serial number field using the traditional method.

Having set the calculation as shown, click OK to close the Specify Calculation dialog.

Let’s not screw this up now

At this point any new record you create in the table will have a unique id associated with it. Well, almost. Right now, the fly in the ointment is duplication. If you duplicate a record, the pesky Do not replace existing value of field (if any) check box will ensure that the duplicated record will have exactly the same id as the original. Uncheck this box to make sure that when duplicating a record, FileMaker knows to generate a new value from Get(UUID).

Lay it out

Okay, in layout mode, add your new id field to the layout (if it wasn’t automatically added) and re-size it to allow for what will be a long random string. You can also re-size the body element of the layout, as we’ll be viewing it in a list view.

What does a UUID look like?

Well, switch to browse mode, click the view as list button, and create some new records. You should see a bunch of random ids appearing in the records you create.

Checking that duplication works

Open the Manage Database dialog, and add new text field called name, and click OK to close the dialog.

Take it for a spin

Add your new name field to the layout (if it wasn’t automatically added) right below the id field. Fill in some names in your records, and then duplicate a record. You should see duplicate name data, but a new id value.