¶ Key values
When developing in Claris/FileMaker, there is an option for key values which are auto-enter serial numbers. While this option can use an alphanumeric value instead of strict serial numbers, the general consensus for these documented standards is to use a universally unique identifier (UUID).
Using a UUID scheme for creating keys nearly eliminates the chance of creating duplicate keys, which is a common problem when migrating data to new files (and having to remember to reset auto-enter serial numbers) and also when merging data created on multiple devices.
Warning about duplicates
By default, the native FileMaker environment provides the Duplicate Record option within the Records menu. If one of the goals in using a UUID is to eliminate the possibility of duplicates, then a few extra steps will be required. See the best practice section below.
¶ Implementation
It is suggested, although not required, that a wrapper custom function be used when integrating UUID values. A wrapper function is simply a Custom Function with a unique name which points to another chunk of code, internal or external function - see method stub. The suggested standard name for this reserved function is UniqueID.
In FileMaker, UUIDs are generated using one of three methods: FileMaker's Get ( UUID ) or Get ( UUIDNumber ) function, via plug-ins, or custom functions. Any one of the methods below may be referenced within the UniqueID custom function. When using a UUID for your key value you may also want to follow the settings in the following dialogs.
¶ Auto-enter options for key fields
In the above image the Calculated value is using the suggested UniqueID custom function. The option of Do not replace existing... is unchecked to allow for FileMaker's ability to duplicate records natively. The Prohibit modification... option is set to prevent any accidental changes to the key value (should it be exposed to the end user - which is not suggested).
¶ Validation options for key fields
The validation options for the id key field have been set to require Not empty, Unique value and to ALWAYS validate by unchecking the Allow user to override...
Leaving the Not empty option unchecked is optional as the auto-enter nature of the field implies that a value will be inserted (even when using ODBC). By setting these options, any action, either by the user or via scripted alteration, if a duplicate key is used, the file will prompt for record reversion. This type of setup compels the solution developer to ensure the uniqueness and canonical state of the key field.
Features, such as the OnObjectValidate script trigger and using File > Manage > Custom Menus... (e.g. removing the Duplicate Record menu item) are methods one can employ to achieve the desired goal.
¶ Record Duplications
By following the settings in the images above, any record in FileMaker can be successfully duplicated using the native Duplicate Record/Request script step or menu option.
When an even tighter degree of control over the record duplication process is required, it's suggested that record duplication be managed via a script within the user interface. Preventing native record duplication is simply a matter of making sure the Do not replace existing value of field (if any) option is checked in the Auto-enter settings. This forces the use of a scripted, and more tightly controlled, method of record duplication. Using a script trigger is also inherently tied into the user interface and is either layout or object specific.
"Do not replace existing..." unchecked warning
So long as no other fields are reference by this calculated value, it should only generate a new UniqueID when a new record is created or duplicated. With the "Prohibit modification of value during data entry" checked, the user cannot modify the field value even if the ID field is shown and accessible (which, in most all cases, it should not be). If through accident or design, the auto-enter calculation can trigger a new evaluation of UniqueID then the potential for orphaned records will certainly exist!
¶ Displaying key fields
This practice highly promotes the use of UUID key values for connections between records. Because of this, they are neither human-readable or user-friendly. It is suggested that primary and foreign key values rarely (if ever) be shown within the user interface. If a human-readble unique value is required, then using a secondary serial value is suggested. There is absolutely no harm in having two fields which are completely unique to a record.
¶ UUID Options
¶ Native options
Get ( UUID )
A native function added in version 12, Claris/FileMaker provides the Get ( UUID ) function, which creates a unique 36-character string of mostly random hexadecimal digits according to the RFC 4122 standard. Storage should be as text. For example:
03E1BB14-B04C-4ACF-A1ED-C90E59EE0396
Get ( UUIDNumber )
As documented in the Claris/FileMaker help this native function returns a unique, 24-byte (192-bit) number. It makes mention that "Using this function instead of Get(UUID) as the calculated value of a primary key field may improve the performance of operations based on relationships." Storage should be as a number. Here's an example:
6116978998861629574347698198457920641937931780026424754168
¶ Plug-in based UUIDs
The ScriptMaster plug-in by 360Works can call on Java standard libraries to generate UUIDs. The demo file included with ScriptMaster contains an example generating a random (version 4) RFC 4122 UUID, which looks like this:
4bf08957-6c8e-465a-946c-aa7b36d831cd
¶ Custom Function based UUIDs
Custom functions for creating UUIDs are more portable than plug-in based UUIDs. Well-designed functions will execute correctly in all FileMaker platforms without the need to distribute and install a plug-in, to create a server-compatible version of a plug-in, or even the ability to run a plug-in in the case of FileMaker Go.
For security reasons Apple removed the ability for iOS apps, including FileMaker Go, to access the device NIC address in iOS 7. FileMaker Go running on iOS 7 will simply return "02:00:00:00:00:00" for the NIC address, regardless of the actual NIC address. Historically, some FileMaker UUID functions used Get ( SystemNICAddress ) as a component of generated UUID values, but this leads to a significant risk of different devices creating duplicate values at the same time, so any UUID functions using Get ( SystemNICAddress ) are now strongly discouraged. The Get ( PersistentID ) function introduced in FileMaker 12 is an acceptable substitute.
Tom Robinson's UUID ( Type ) function
Tom Robinson's UUID ( Type ) function creates hexadecimal UUIDs consistent with the international standard scheme. By passing the function different parameters, you can generate version 1 UUIDs (encoding timestamp and NIC address) or version 4 UUIDs (encoding random numbers). A sample (version 4) UUID looks like this:
f47ac10b-58cc-4372-a567-0e02b2c3d479
Calling this function with UUID ( 1 ) or UUID ( "1h" ) will attempt to use the Get ( SystemNICAddress ) function, which will not return a correct result in iOS 7 or later, potentially leading to duplicate values. So using this function to generate type 1 UUIDs is strongly discouraged.
Jeremy Bante's UUID function
There are a number of UUID related functions in a repository from Jeremy Bante. For example, the UUIDTimeDevice function encodes the creation timestamp (UTC), part of the device's persistent ID, and a self-managed serial number. The generated values can be stored in a number field, which has certain performance benefits, and are meaningfully sortable by creation timestamp. Related functions are available for extracting the encoded timestamp, and for converting values to the conventional hexadecimal representation. A value from UUID looks like this:
12063524304235234000007854171467283736889
For situations where the privacy of a device persistent ID or a record creation timestamp is a concern, there is a related UUIDRandom function, which generates values like this:
42141279241146726753204187162615025729536