Brian Dunning's FileMaker Custom Functions


Creates a unique, never to appear again number

  Average rating: 4.1 (38 votes) Log in to vote

Joshua Beckett & Anton Anderson   Joshua Beckett & Anton Anderson
The Wine Curators

Share on Facebook Share on Twitter

  Sample input:
  Sample output:

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

Creates a unique 13 digit number based on year, day of year and time; result will always be 13 digits
EXAMPLE: may 23, 2010, 1:48 PM (and 3 seconds) = 2010142134803
Use to generate unique numbers (for IDs, identifiers, etc) within a solution without the need ever to check for duplicates, because there can only and ever be one instance of the year, day of year and exact time, including seconds.
[**NOTE: Intended for a single-user environment.
Filter ( Get ( CurrentTimestamp ) ; "0123456789" ) gives you almost the same result (e.g. 624201010157) but will not sort as well as UniqueNum (2010175130157); also, UniqueNum will always result in the exact same number of digits, which, for some purposes, is critical]



Brian Dunning   Brian Dunning,
Jun 24, 2010
Why not just Filter ( Get ( CurrentTimestamp ) ; "0123456789" ) ?
Joshua Beckett   Joshua Beckett, Los Angeles
Jun 24, 2010
Filter ( Get ( CurrentTimestamp ) ; "0123456789" ) will result in something like this: 624201010157 -- which is fine, but will not sort as well as 2010175130157 (perhaps i should add this to the description?)
Darren Terry   Darren Terry, Pacific Data Management, Inc.
Jun 24, 2010
Instead of using Choose to pad with leading zeros, why not do this instead?:

daynum = Right ( "000" & daynum, 3 )
Bart Bartholomay   Bart Bartholomay, Vero Beach, FL
Jun 24, 2010
If you're saying that only one record can be created per second, thus allowing uniqueness, then I'd have to disagree. Depending on your CPU and the number and type of fields which might have auto-entering data, it's possible to create at least 4 records with the same timestamp if that's a field, Creation(TimeStamp). Though the Random function might get you more toward your target, it still leaves a slight gap for duplication.
Joshua Beckett   Joshua Beckett, Los Angeles
Jun 24, 2010
you're right about that. feel free to remove this if you feel it's flawed. for my my purposes, four records per second never happens.
Joshua Beckett   Joshua Beckett, Los Angeles
Jun 24, 2010
i think you're daynum = Right ( "000" & daynum, 3 ) is a great idea
Joshua Beckett   Joshua Beckett, Los Angeles
Jun 24, 2010
thanks for these notes. i have now included a note indicating this is intended for a single-user environment.
Anton Anderson   Anton Anderson, Altadena, CA
Jun 24, 2010
@ Darren: Great suggestion!
@ Bart/Brian: We wrote the function for a run-time app, so it was only expecting a single-user environment, where sorting was a key requirement. While not at the level of a UUID, this function did the trick.
Dave Graham   Dave Graham, San Diego, CA
Jun 25, 2010
Computer clocks are *not* reliable. They require routine updates to maintain accurate time. If your computer syncs via a time server you may not even realize that your time was automatically adjusted backwards one minute. Records created individually aren't too likely to obtain a duplicate key, but records created via import increase the probability.

When it comes to database integrity, why take the chance? I'd highly recommend that you use Ray Cologon's excellent Base36 uID custom function instead.

Dave Graham
Bit Tailor, LLC
Martin D. Brunner   Martin D. Brunner, Zürich
Jul 1, 2010
I'd recommend you to use the uUID instead of this or of the Base36 uID. It can be packed into any file and is very fast in creation - and the probability that you encounter two identical numbers is veeeeery low :-)

Martin D. Brunner
Edward Souza   Edward Souza, Victoria
Aug 15, 2010
Hello, all,

Mr. Brunner and Mr. Graham are right, for there are situations where FileMaker could generate from two up to 'n' records within a single second — it depends mostly on CPU speed and scripting. If you add FileMaker's own Record ID to your CF, then it should generate unique numbers. It's redundant, though: for one could use FM's record id preceded by several zeroes, such as Right ( "00000" & Get ( RecordID ) ; 5 ).
The notion of using date and time data for id-ing a record is quite old, and has to do with the possibility of retrieving such data through a calculation (ergo without the need of specific date and time fields).
I, for one, rely on Ray Cologon's Base 36 uID CF for generating unique IDs.
Check his website, especially the demo section — — for he has unique and ingenious ideas and a deep knowledge of FM and CF.
Good luck.

Log in to post comments.


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

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 15 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: