You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 17 Next »

When developing in FileMaker, your default option for key values is FileMaker's 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 remembering to reset auto-enter serial numbers) and 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.

Best Practice

It is suggested 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 ways: FileMaker's Get ( UUID ) function, 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.

In the above image the Calculated value is using the suggested UniqueID custom function. The option of "Do not replace..." is checked to prevent the replacement of the original value used. 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).

The validation options for the id key field have been set to require a "Unique value" and to ALWAYS validate by unchecking the "Allow user to override..." and changing the default setting of "Only during data entry". 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.

Supporting Record Duplication

By far, the most ensured way of avoiding any possible duplicate ID values is to use the settings above. Of course, FileMaker's validation will always catch any duplicates based on the "Unique value" validation. The method of supporting the creation of duplicate records, or cloning, is typically best handled via an OnRecordCommit layout trigger and through the use of a scripted process.

It is ALWAYS suggested that record cloning be managed via a script within the user interface. However, using a script trigger is inherently tied into the user interface and is layout or object specific. If a lower level method of duplication support is required then the following modifications may accomodate - with caveats.

In order to support FileMaker's native record duplication feature with UUIDs, a few changes to the above dialog boxes must be made. The "Do not replace existing value of field (if any)" checkbox must be unchecked. The Auto-Enter calculated value would be replaced from the simple UniqueID to the following.

Auto-Enter for Duplicating Record Support
Case (
	not IsEmpty ( Self ) and creationTimestamp = modificationTimestamp;
		UniqueID;

	IsEmpty ( Self );
		UniqueID;

	Self
)

As you can see, two additional fields are required and referenced. The creationTimestamp and modificationTimestamp from Field naming should be the exact same value when the record is duplicated. Given that any newly created record will not have any values pre-existing within the fields, it's the job of auto-enter to populate these fields. Using the not IsEmpty() test simply determines if the field does contain an existing value, which, in the case of a duplicate record, it will. In that case, the calculation will evaluate to a new UniqueID only when the two other referenced fields match. On record duplication these values will be initialized and should be the same at the point in time the record is cloned.

The default result for the key field must always be itself. You always want to limit your exposure to potentially invalid data. Also of note is the fact that the calculation will always evaluate any time the record is modified - by virtue of the fact that it references the modificationTimestamp field. This contributes, albeit a VERY small amount, to any performance degradation and is another vote for the script trigger based method of duplication management.

So long as no other fields are reference by this calculation, 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).

Caveats

While the above modifications should work in most cases, there is no guarantee. As an example, there is a well-known method of forcing a portal refresh by setting a key value to itself to force the relationship to update the display of records. The key field may also be scriptomatically (yes, you read that correctly) set to itself in order to force other auto-enter fields which reference the key field to update or to force a re-lookup of field data. As stated above, the best method to employ the use of these modifications is to script the cloning process and to create the duplicate and commit first and then proceed with other steps required by a script.

UUID Options

Get ( UUID )

In version 12, FileMaker introduced the Get ( UUID ) function, which creates a unique 36-character string of mostly random hexadecimal digits according to the RFC 4122 standard. For example:

03E1BB14-B04C-4ACF-A1ED-C90E59EE0396

Plug-in based UUIDs

ScriptMaster by 360Works

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. Though you do need FileMaker Pro Advanced to install custom functions in a file, 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.

Ray Cologon's Base36 uID System

Ray Cologon has been using a text-based UUID scheme since 2005. The uID function combines the creation timestamp, the device's network card (NIC, or MAC) address, and FileMaker's internally generated record id. These are encoded in Base36 (0-9 and A-Z) to be relatively compact and human-readable. Cologon's demonstration file includes additional custom functions for reading the encoded timestamp, NIC address, and record id from a generated key. uIDs look something like this:

1 8ADOO OJU0R W8FWV TPML4

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 1) UUID looks like this:

1de7b380-cff7-11df-b581-00264a0d4b42

For situations where the privacy of a device NIC address is a concern, calling UUID ( "4" ) or UUID ( "4h" ) will generate a value with random digits:

f47ac10b-58cc-4372-a567-0e02b2c3d479

Jeremy Bante's UUID function

The UUID function by Jeremy Bante (maintained in the fmpstandards Github repository) encodes the creation timestamp, the device's network card (NIC) address, 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 NIC address, and for converting values to the conventional hexadecimal representation. A value from UUID looks like this:

1-2-063432286093-2600000-02476-000164451142466

For situations where the privacy of a device NIC address is a concern, there is a related UUIDRandom function, which generates values like this:

4-2-034631749357-3649139-15519-272178513838080
  • No labels