The Original FileMaker Community
Business Templates - Demo Apps - Video Tutorials -Samples - Help - 46000 Member Forum

The Leading Filemaker Developer Tools

Validating Unique Values In FileMaker – FileMaker Today

Get real time updates directly on you device, subscribe now.


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.


=

More info about the author and FileMaker in general, contact me at This email address is being protected from spambots. You need JavaScript enabled to view it..

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More