1 Reply Latest reply on Jan 3, 2011 8:39 AM by philmodjunk

    Unique value based on two fields having the same value

    Mitch

      Title

      Unique value based on two fields having the same value

      Post

      Hi, 

      Is there a method in which you can prevent a record being duplicated based on the value of two fields being the same.  In other words,  if a records is added and the value of "Field_One" and "Field_Two" are the same as a previous record, the record can not be added.  If one is differant, then record will be added.

      In the database "Field_One" is mandatory and "Field_Two" is not and can be left blank.

      If have thought of creating a calculated field combining both fields and making this new field unique,  "Field_One" has a value entered and "Field_Two" is yet to be entered and therefore empty, it can clash with another record.

      Therefore it needs to be some sort of script triggered after the second field has been decided....or something else?   

      Mitch

        • 1. Re: Unique value based on two fields having the same value
          philmodjunk

          Using the auto-entered text calculation should work for you provided that the record is not comitted in between editing data into field_two. If that's a possibility, then an On ObjectValidate, OnObjectSave or OnObjectExit script trigger could be used to check for a matching duplicate value.

          Define a self-join relationship:

          YourTable::Field1 = YourTable 2::Field1 AND
          YourTable::Field2 = YourTable 2::Field2

          Where YourTable 2 is a new table occurrence of YourTable. (Select YourTable, then click button with two gree plus signs to make this new table occurrence.)

          Your script can then use count ( YourTable 2::Field1 ) to see if a duplicate records exists. (The count will be greater than 1 if a duplicate exists.)