Universal ID

[panchristo]

What suggestions do you have for building trully universal ID fields? (To be used by stand-alone copies of the same application)

I was thinking of a global field in the system table of my application which will be appended to every table's primary field (in Aut-Enter). But then I found out that it doesn't update.

Any recomendations?

[David Jondreau]

Try Ray Cologon's solution:

http://www.nightwing.com.au/FileMaker/demos9/demo910.html

[panchristo]

Should have not forgotten about this, thanks.

However I was thinking about something that will make sense when looking at it, as to where it has come from. How would you go around this?

[David Jondreau]

Why not use a second field to keep track of that?

[panchristo]

Using Ray's solution for Unique ids, do you think it would performance if I used uID fields in every table?

Just because they are 20 or so characters long, does this have a negative effect when filtering through relationships or sorting?

[jbante]

Universally unique IDs (UUIDs), including Ray's uID, in FileMaker don't make much of a performance difference on relationships or sorting. There are three aspects of performance that ARE affected: speed of performing Finds, speed of the calculation creating the ID, and file size. Ray's function doesn't do very well on any of these counts, compared to other solutions. He designed the function to be relatively human-readable (though not human interpretable). Tom Robinson put a function on Brian Dunning's site that calculates faster, but it's still a text-based ID (as implemented in FileMaker), so it still makes for slower finds and bigger files. If performance is a real concern, you need a number-based ID, which is why I wrote mine, also on Brian Dunning's site.

[Ocean West]

Jeremy - one thing I noted with Ray's and your CF was because it relies on FMP time stamp and record id in certain solutions if you are trying to loop thru records setting two fields with a UUID in the same script it will always fail to give each field a unique id because the record is the same and the the script happens to fast for the clock to trip.

I don't remember the exact solution but It may have been some de duping or normalization routine that I was doing to build the UUID's for the children records in one table thus the script needed to set multiple UID fields at the same time.

Tom's Function mentions this

- 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).

[jbante]

Tom and I both resolve this issue by providing versions of our function based on random digits rather than timestamp-record ID-NIC address. (Tom uses one function to make both; I have a separate function for each; all are on Brian Dunning's site.) This is also the solution where regulations about data privacy are especially strict, disallowing recording any information about the device that created the ID (such as in Germany, for example) — the existence of such regulated privacy concerns is why random (version 4) and hashed URI (version 5) UUIDs are much more prevalent (outside of FileMaker) than timestamp-NIC address (version 1) UUIDs. Hashing a URI is too much for a FileMaker calculation to handle reasonably efficiently, so we resort to the random numbers. There is technically a possibility of eventually generating a duplicate, but it's absurdly remote — remote enough to be used as an international standard. In the random version of my function, I still don't replace the timestamp with random digits, betting that it's still extremely difficult to randomly generate the same 21-digit number twice in the same second.

It sounds like you're describing a situation where the UUIDs either aren't being used as primary keys (or else there would only be one per record), or you're generating an ID in a context other than the record it will be applied to. I only ever use the timestamp-record ID-NIC address version of my function in the auto-enter calculation of a primary key field in a table definition, which has never generated the same ID (because of different record IDs) in the same second. For all other uses, I go with the random version.