Unique is not UNIQUE

[JamData]

hi, in my multiuser database i have a key field and in The validation I checked de "unique" check box.

How ever, I still can be able of entering duplicated records from diferent computers at the same time (that is not a good thing...) so, i do not understand the meaning of that "unique" check box.

can any body please explain me how can i be sure that no body can enter a duplicated id in my key field?

thank u very much

[ November 16, 2001: Message edited by: JamedData ]

[LiveOak]

The easiest way is not to user ANY value the user enters as a key field, but to create unique keys under script control.

For example, if a user wants to user Project Number as the key and later decides to change the project number (for whatever reason, yes it will happen!). All the Time Card records can be disconnected from the project. Use some sort of auto entered serial number as the key and keep Project Number as just another piece of data about the project.

-bd

[Kurt Knippel]

See my article on creating a unique ID for each record. The user should never see or have access to this field.

http://www.fmforums.com/cgi-bin/ultimatebb.cgi?ubb=get_topic&f=8&t=000018

[JamData]

yes, i know that, thank you, but my problem is that i can not use serial because i have to update the versions of my aplication, and then i will have to import the records. and my boss wants not always to go and check the serial numbrs on the field difinition. so, i created the id number as a calculated field that adds one to the maximun id in the table. that seems to work pretty fine but when two users start to create new records as fast as their pc and mac can go, some of the keys are duplicated.

Sorry i did not give this information before...

soo... any ideas?

tks tks many tks

[The Bridge]

CaptKurt's unique record id solution should guarantee unique record id's whether multiple users are entering records at the speed of light or if you're importing from a previous version of the database. I have saved a copy of his calculation in a handy place on my computer and use it as a standard calc, with minor differences: I have removed the hyphens, reduced the Status(CurrentRecordID) to 5 digits, and added a unique text key (e.g. "Job") at the beginning. Also, I've rearranged the order of the various Status functions to Date, Time, RecID.

Example:

"KEY" &

Right( "000000" & Status(CurrentDate), 6 ) &

Right( "000000" & Status(CurrentTime), 6 ) &

Right( "00000" & Status(CurrentRecordID), 5 )

So far CaptKurt's is the best solution I have ever seen for creating unique keys. Thank you! Thank you! Thank you!

Now, where's that Rate button?

[ November 19, 2001: Message edited by: The Bridge ]

[Ocean West]

With FMP 5.5 you can script the update of autoenter serial numbers...

Another approach is to use a 1 record database that contains the Next number.

Under script control you would in the remote database, increment the serial number field by one.

Here is the concept

In the file you are adding a record to, you run a script the first thing it does is to see if the related field "SWITCH" is 1 or 0 if it is 1 then it pauses for 1 second, then loops until it is 0. If it is 0 then it sets it to one, then increments the serial number field in the related file by one. Then sets the value of the field to a global. The script returns to the first file then creates a new record and set the local serial number to what is stored in the related global. Then sets the switch back to 0.

This concept you could store all the next unique ID numbers in one file:

In one of my solutions I keep track of the customer ID, Job ID, Invoice, PO, Employee ID, Note ID, and a few others. This way when it is time to update the solution I only need to go to one datababse and run a script that gets the max of each out of each database and update the fields.

The idea of the loop is that in a multi users soluion if two people hit the new record button at the very same time the second user is paused for only the time it takes for the first user's script to complete.

Another nice thing is that you know the next number before a record is created. So you can prepare data that can be validated prior to createing a record.

hope this wasn't too confusing...

sd

[BobWeaver]

Stephen: I use a similar technique generating unique serial numbers but rather than checking that Switch is 0, I check to see if it's empty. If so, I concatenate the current user ID (one that is guaranteed to be unique) onto the end of it, and then immediately read it back to see that switch begins with my user ID. If so, proceed with the rest of the script. If not, go back to the beginning. At the end, clear the contents of Switch.

This avoids the situation where two scripts could see that Switch is zero and then both scripts change it to one and then proceed, possibly duplicating a serial number. This may seem like paranoia, but I got burned on an application once, where there were a lot of users madly generating new records, and occasionally getting duplicates.

[ November 19, 2001: Message edited by: BobWeaver ]

[Anatoli]

I am first locking "system" file and when unique ID is successfully created in main file last I unlock that lock in "system" file.

It dramatically decreased duplicates to practically zero.

[JamData]

thank you very much, now it works

[Steven H. Blackwell]

Remember that in FileMAker Pro 5.5 you can both get and set the next serial value by script.

HTH

Olad Advance Man