Over 46,000+ Business Solution Developers Find answers, ask questions, and connect with our community of business solutions developers, business owners and partners.
The unique setting in the validation dialog box is designed to prevent duplicate values in a field for all the records in a table. The Unique validation option ignores capitalization, ignores word order (to an extent) and ignores punctuation options (again, to an extent). For example, if you have the word Dwayne Wright and you want to make sure it is validated as a unique occurrence in a particular field in a particular database, then the following is true about validation
Original text Dwayne Wright
versus a second record with the entered values of
dwayne wright – unique validation will fail and FileMaker will bark at you
wright dwayne – unique validation will fail and FileMaker will bark at you
Dwayne – Wright – unique validation will fail and FileMaker will bark at you
wright, dwayne – unique validation will pass, FileMaker will consider this a unique value
As with most validation settings, you need to make sure you have the user override set properly to your needs. If you do not allow the user to override validation, make sure you allow a user a method to communicate any problems this validation setting may cause.
I’ve seen and heard many developers say that the most common use for unique validation setting is to make sure a primary relationship key (that binds two or more tables together) is unique. I would disagree with this statement because in most cases, you wouldn’t have users entering data into your primary key field. Most primary key fields should be auto entered, more than likely never be on a layout a user can see and never allow the user to edit it.
I would argue that the most common use of this validation is the cases where you want a user to do a search for a record before entering a new one. For example, let us say that John “Ripper” Reid is ordering a new widget from us. The data entry person skips looking for an existing customer record and starts entering a new one. When the user enters in the name, FileMaker’s unique validation barks are the user saying there is a unique validation problem.
Now here is where it can get interesting.
You can setup a FileMaker layout so the user has to commit to new records and edit record changes. So you can now have the user back out of entering the new record for John “Ripper” Reid. The user then can do a find for John “Ripper” Reid in the table. The user finds the record and decides if that John “Ripper” Reid is the same one as the John “Ripper” Reid that is placing the new order. If they are the same person, the user adds the new order using the found record. If they are NOT the same person, the user enters in the new record, overrides the validation ( if they can ) and then enters in the new order. If the user cannot override the validation, the user will need to enter in a variation of John “Ripper” Reid such as J. “Ripper” Reid, so the unique validation will not fail.