UUID ( Type )

Generate a Universally Unique Identifier (UUID)

Average rating: 3.7 (55 votes) Log in to vote

Tom Robinson - Show more from this author

Sample input:

UUID ("4h")

Sample output:

f47ac10b-58cc-4372-a567-0e02b2c3d479

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/*
Generate a Universally Unique Identifier (UUID)

Parameters:
	1    Generate a version 1 (MAC address) UUID with no hyphens
	1h   Generate a version 1 UUID with hyphens
	4    Generate a version 4 (random) UUID with no hyphens
	4h   Generate a version 4 UUID with hyphens

Requires:
	The NumToHex custom function by Jeremiah Small <http://www.briandunning.com/cf/216>

Version 1 UUIDs are based on the current timestamp and MAC address and are in the form xxxxxxxx-xxxx-1xxx-yxxx-xxxxxxxxxxxx, where x is 0–F and y is 8–B.  e.g. 7f23c000-b6fa-11ad-802a-136a8c2f7100.

Version 4 UUIDs are randomly generated and in the form xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx.  e.g. f47ac10b-58cc-4372-a567-0e02b2c3d479.

The advantages of using a UUID over a serial number is there’s no need to worry about the next value of a serial number or a serial number collision, forinstance when importing records.  The downside is they’re not appropriate to expose to a user.

UUID example:  Dick and Harry both create records in their own copies of a database.  Each record needs a unique serial number so they can be combined at a later date.  After a total of 68,719,476,736 (2^36) records are created the probability of a collision is 0.0000000000000004 (4 × 10^−16)

Further reading:  <http://en.wikipedia.org/wiki/UUID>

Caveats:
- Because FileMaker’s clock resolution is only in seconds the version 1 UUID can return duplicates if called multiple times a second (there’s a 14-bit random number included which usually prevents this).
- FileMaker 8.5–10.0v2 (and maybe earlier) have a bug where on some machines the random function returns the same sequence of random numbers every time FileMaker is restarted!  This obviously corrupts the generation of unique identifiers.  Using a version 1 UUID is safer in this instance.  10.0v3 fixes this.
- I *think* I have the version 1 UUIDs correctly following the spec.  Note they include local time (not UTC) and clock sequence is always random—both allowed but not recommended.

Author:
	2009-02-01 Tom Robinson <http://www.tomrobinson.co.nz/>

Change History:
	2009-02-06
		Add patch parameter after discovery of FileMaker bug causing client’s machine to create duplicate UUIDs
		Generate lowercase UUIDs per RFC 4122
	2009-02-11
		Remove patch code and add generation of version 1 UUIDs as patch parameter was still returning duplicates
		Utilise DecToHex custom function
	2009-08-07
		Update docs to note random bug is fixed in 10.0v3
	2010-05-12
		DecToHex is now NumToHex
		Split last group in 4/4h from Random*281474976710656 (12 digits) to 2 × Random*16777216 (6+6 digits) as random function in 10.0v3 appears to have less precision—UUIDs were always ending in 0000 or ffff
*/

Case(
	Left(Type;1)="1";
		Let(
			[
				h=If(Right(Type;1)="h";"-";"");
				t=Lower(NumToHex((Get(CurrentTimeStamp)-49916304000)*10000000))  /* Subtract to move timestamp epoch from 0001-01-01T00:00:00 to 1582-10-15T00:00:00, multiply to get from seconds to hundred-nanosecond intervals */
			];
			Right(t;8)&h&      /* RFC 4122 time_low */
			Middle(t;4;4)&h&   /* time_mid */
			"1"&Left(t;3)&h&   /* time_hi_and_version */
			Middle("89abb";Random*4+1;1)&Right("00"&Lower(NumToHex(Random*4096));3)&h&
				/* clock_seq_hi_and_reserved & clock_seq_low                          */
				/* Note random can return 1 in which case result is 2nd ‘b’ or ‘000’ */
			Substitute(GetValue(Get(SystemNICAddress);1);":";"")   /* node */
		);

	Left(Type;1)="4";
		Let(
			h=If(Right(Type;1)="h";"-";"");
			Lower(
				Right("0000000"&NumToHex(Random*4294967296);8)&h&
				Right("000"&NumToHex(Random*65536);4)&h&
				"4"&Right("00"&NumToHex(Random*4096);3)&h&
				Middle("89abb";Random*4+1;1)&Right("00"&NumToHex(Random*4096);3)&h&
				Right("00000"&NumToHex(Random*16777216);6)&Right("00000"&NumToHex(Random*16777216);6)
			)
		);

	""
)

This custom function generates a Universally Unique Identifier (UUID) in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx (or the same sans hyphens). e.g. f47ac10b-58cc-4372-a567-0e02b2c3d479.

The advantages of using a UUID over a serial number is there’s no need to worry about the next value of a serial number or a serial number collision, forinstance when importing records. The downside is they’re rarely appropriate to expose to a user.

UUID example: Dick and Harry both create records in their own copies of a database. Each record needs a unique serial number so they can be combined at a later date. After a total of 68,719,476,736 (2^36) records are created the probability of a collision is 0.0000000000000004 (4 × 10^−16)

Comments

		David Owen, Bloomfield, CT, USA May 25, 2009
This looks very valuable. I am wondering about how to parse out the data later if needed. I'd need some help with the calculation. Any CF for that?

		The Author, Aotearoa New Zealand Aug 6, 2009
But how do you want to parse the output? The custom function returns a simple text string which is a standard UUID (either with or without the hyphens).

		Alexis Gehrt, Database Designs Nov 30, 2009
Just one little note: The DecToHex custom function by Jeremiah Small <http://www.briandunning.com/cf/216> is now named: NumToHex Just a simple find/replace.. ;-)

		Andreas Thyholdt, dbConsult AS May 11, 2010
I always get either 0000 or ffff as the last characters when using type 4. Changing the 48-bit number by adding a few numbers on the end does produce random numbers in the last 4 characters, but I am unsure of the consequences, if there are any. Any advice on this?

		The Author, Aotearoa New Zealand May 11, 2010
Thanks Andreas, fixed now. Looks like 10.0v3 fixed the duplication of random numbers but dropped the precision available.

		Jens Rasmussen, Danmark Oct 26, 2011
This entry fails to clearly mention dependence on NumToHex.

		Claus Lavendt, Denmark Feb 23, 2012
Create a simple DB with only one field. Set AutoEnter UUID ( "1h" ) and create a loop script like this: Loop ExitLoop If ( Get ( TotalRecordCount ) > 15000 ) New Record End Loop Search for duplicates and in my tests I found around 748 records, that were not unique. This makes version 1 useless.... Tried the same approach with version 4 and even increased to 250.000 records. No duplicates.... So either there is a bug in the calc or FM has a too low tome resolution for this.

		The Author, Aotearoa New Zealand Feb 24, 2012
Jens: the comments in the code header under â€˜Requiresâ€™ clearly state the NumToHex dependence. Claus: the comments in the header under â€˜Caveatsâ€™ clearly state version 1 can return duplicates if called multiple times a secondâ€”this is the only situation where it is â€˜uselessâ€™.

		Bachmann Support GmbH, Zurich Mar 2, 2012
We had the same issue that Claus Lavendt had. With V1 it creates duplicates, not unique UUIDs. V4 works. So dont use V1!

		Claus Lavendt, Denmark Mar 14, 2012
Version 1 would be great if it wasn't for the fact that Filemaker's time resolution only operates in seconds. In e.g. Java, the time resolution operates in miliseconds, which makes version 1 to a great approach, as you also will be able to know which device created the record. So stick to version 4, when using this in Filemaker. Just as a note, one of my colleagues created a test, where he created nearly 4 million records. ALL had unique ID numbers ! This was with version 4.

		Claus Lavendt, Denmark Mar 14, 2012
but, anyway, thank you Tom for creating this very useful CF and for sharing it with us !

		The Author, New Zealand Mar 2, 2015
Note in FileMaker 12 and later there is now a builtin function get(uuid).

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

UUID ( Type )

Comments

Support this website.

Support this website.