5 Replies Latest reply on Nov 1, 2011 8:30 AM by mgores

    How to prevent duplicate entries



      How to prevent duplicate entries



      Is there a way to prevent duplicate entries to my database? So when I try to enter the same information into a field it will alert me if that information already exists in another record.



        • 1. Re: How to prevent duplicate entries


          Under Field Options click Validation and check the Uniqu Value box.

          My own question: How to do this for several fields? I have a table of addresses and want to prevent dublicates being registered. Can one check for dublicates based on several fields? Preferable a script could run in the background and notify the user only when necessary. 



          • 2. Re: How to prevent duplicate entries

            Henning - you could create a calculation field that concantonates the fields you want to check and make it a unique value.

            • 3. Re: How to prevent duplicate entries

              This I have tried, but it triggers even if there is no dublicate records.

              My setup:

              Validation: Unique value

              Validate by calculation -> Specify -> Field=Name&Surname&Postcode

              Maybe this is wrong? How about a script that triggers when these fields are entered?

              Multible good solutions must exist or a built in function since this is a common problem?


              • 4. Re: How to prevent duplicate entries

                Don't put this as "validate by calculation". Click the auto-enter tab, click the by calculation option and then enter your expression there.

                One caveat when it comes to uniqueness checks using the unique values validations or when using ! to perform a find for duplicate values in a field, if there are any returns in your field, such as a list of values, the uniqueness check is applied to each value--not the value of the field as a whole.

                Thus if you have "Apple¶Orange" in a text field, the unique values validation will kick in if any other record stores "Apple" or "Orange", not just if they have both such values. Performing a find using ! will likewise find this record if another record stores "Orange" or "Apple".

                • 5. Re: How to prevent duplicate entries

                  I imagine you could write a script that captures those 3 fields as variables, performs a find using those variables and shows a dialog if found count is not equal to 1 or show all records if it does = 1.  you could trigger it on record commit to let the user fill in all the fields before it runs or trigger it on modify for the last field that gets entered.