3 Replies Latest reply on Apr 3, 2016 3:31 PM by shona1

    Check for Unique validation between two Fields

    shona1

      In my database, my records can have up to two barcodes to identify the same record, so Im thinking I need two separate fields to record the barcodes in, I have the first field set up with unique validation but am unsure how to ensure that the same barcode isn't entered in the 2nd field a completely different record?

        • 1. Re: Check for Unique validation between two Fields
          Vaughan

          shona1 wrote:

           

          In my database, my records can have up to two barcodes to identify the same record, so Im thinking I need two separate fields to record the barcodes in..

           

          No. Use a separate Barcodes table and relate records to the "Item" table. The barcodes than then be validated as unique. You an also have more than two barcodes.

          • 2. Re: Check for Unique validation between two Fields
            keywords

            A couple of approaches I can think of:

            1.     Set up a relationship between each barcode field and the whole list of barcodes from the other field—not sure that's possible directly, or either you would need a field to parse each barcode to text first—then you could use a boolean field to tell you whether there is a match to any in the other field.

            2.     Store all barcodes in a separate table—where you can make sure each exists only once, and then reference them in the main table of whatever you database is—where you can make sure each barcode is referenced only once.

            • 3. Re: Check for Unique validation between two Fields
              shona1

              Thanks "Vaughan" and "Keywords", for waking me up. Yes of-course a related seperate table is the logical answer. My case is a typical example of working in a existing working database and then later needing to add additional functionality to it and I was trying to fix it with a bandaid.
              I was hoping someone would direct me to a function that could be used in Validation by Calculation to check the other field for uniquenss, but can now see many advantages within my Database to run with a New related table.