5 Replies Latest reply on Oct 4, 2013 10:21 AM by philmodjunk

    Enforcing unique records on a table

    ShaneB

      Title

      Enforcing unique records on a table

      Post

           In my database users create parent/child relationships on a join table using a script. The script loops through a found set of records and makes a set of records on the join table to "combine" them with a new record. I would like for the script to also check if a current join record exists on the table already so that, for example, record ID #4 could not be joined to record ID #25 more than once. I would also like users to get a message displayed when a certain record (or records) already existed and they tried to create the join. So this essentially is enforcing unique records (which on this table are simply two field joins of a parent and child record ID) on the join table. How would I go about doing this?

        • 1. Re: Enforcing unique records on a table
          philmodjunk

               A common way to enforcing uniqueness across the data in two or more fields is to add a text field with an auto-enter calculation that combines them such as:

               KeyField1 & " " & KeyFIeld2 //do not separate values with returns!

               Then specify a unique values validation option on this new field.

          • 2. Re: Enforcing unique records on a table
            ShaneB

                 OK, this makes great sense and works to eliminate duplicates. When a duplicate is discovered, however, the error message pops up on the join table and gives what could be considered confusing options to the users. Is there any way I can simply have the option automatically to be not to create the duplicate record and simply tell the user one of their joins already existed?

            • 3. Re: Enforcing unique records on a table
              philmodjunk

                   Option 1, Use OnObjectValidate to run a script that uses set Error Capture [on], Commit records and Get ( LastError ) to detect this error, but then your script offers it's own less confusing dialog and supporting scripting to catch and correct the error.

                   Option 2, Use the same basic script and trigger, but detect the duplicates via a self join that uses both fields as match fields.

              • 4. Re: Enforcing unique records on a table
                ShaneB

                     Is there a way I can just have the duplicate record not be created (or automatically deleted) without worrying about offering a dialog to the user? I essentially just don't want duplicate records to be created in the first place.

                • 5. Re: Enforcing unique records on a table
                  philmodjunk

                       The script I described could automatically delete or revert the record, but wouldn't that create confusion for you user? I guess it depends on the design of your database.

                       And some methods you might use for data entry can also prevent the creation of duplicates. Value lists--sometimes a dwindling value list, can limit user options to avoid duplication. Or the user enters data in a global field and a script then creates a new record only if a matching record does not already exist.