Unique IDs - How reliable

[Marc .]

Hey everybody,

I'm trying to set up an autofill calculation to generate a unique ID for each records upon creation.

I don't want to use FMP's serial function because it doesn't get reliably updated when you import data into your solution (I mean it doesn't get updated at all an the workaround is cumbersome).

I thought I might not use FileMaker's Serial function at all and instead rely on a calculation:

[color:blue]SerialIncrement(GetNthRecord ( Tâche_ID ; Get(TotalRecordCount) -1 );1)

That of course works beautifully in a simple setup and it has the advantage of relying only on the data that is in the solution (assuming the "Get last record" works reliably).

BUT: I'm anticipating problems in multiuser environments and I would like to hear opinions on that. What do you think would happen if 2 (or more) users create new records at exactly the same time?

And has anyone got a better idea (other than to use a plugin which I know is out there).

Thanks in advance.

[LaRetta]

I import all the time and the serial function indeed works very reliably. But you must specify 'Perform Auto-Enter' for it to fire.

I would NOT 'roll-your-own' with serials. They are very dependable and trying any other scheme will just cause you grief and they work beautifully in multi-user. :wink2:

[T-Square]

LaRetta--there are times when you don't want auto-enter to trigger--as when you are importing records that have related records to be imported (say a Client record with related invoice records). If you rewrite the client IDs, your related records get trashed (well, lost, disconnected, aimless, directionless, and generally full of despair).

That said, Marc, as a longtime griper about the Autoenter serial features in FM I have to agree with LaRetta. Any workaround you come up with is uglier than the "cumbersome" workaround you hint at. And I've tried some pretty ugly ones.

(Big Aside: DO NOT attempt to use FM's internal recordID as a unique ID. I tried that, and discovered that when a client with fewer records than my test database tried to add a new record, the system cross matched a huge number of records. Ugh!)

It is easy to script an import so that at the conclusion, you locate the highest used ID (using the Max function), and set the Next Serial value to one above. That way, the Serial function will keep working.

HTH,

David

[comment]

I think the only time to turn auto-enter off is when you're re-importing your own records. To import new records and their related children, I would import their IDs into auxiliary fields. Filemaker is polite enough to leave a found set of imported only, so using a relationship based on the aux fields, you can quickly re-establish the links using your own ID fields and relationship.

[LaRetta]

I dunno, David. If I was importing a different CustomerID with related Invoices, I'd import the 'old' customer ID into a tempID field. A relationship would have been established from the TempID to the Invoice TempID and I'd loop and insert the correct FM-generated ID (which would STILL be created using Auto-Enter, first) from the Customer table into the current Invoice::CustomerID field. I've done it this way (without fail) for years with (guessing) over 500 imports - most of which have (many) related records associated (from several different tables) with the import. I would be more than happy to demo this type of process.

I DO NOT EVER manipulate the auto-enter serials myself. I have simply never had the need. But then, maybe this is one of those work-arounds you mention. Still ... by using *this* type of work-around, the unique serials are preserved and there is no need to manipulate them further (allowing possibility of error). It has never failed me yet.

UPDATE: You beat me, Michael!!

LaRetta

Edited February 17, 2006 by Guest
Added Update