5 Replies Latest reply on Oct 3, 2012 5:50 PM by Mike_Mitchell

    Check to see if a record already exists

    jlwayne

      For simplicity, lets assume the following:

       

      Table 1 has fields A and B

       

      Table 1 has some records

       

      I have a script that adds records to Table 1

       

      Here's what I want:

       

      If the script enters a new record that matches both field A and field B of an existing record, I don't want the new record to be added.

       

      What do I include in my script? Or is there another approach?

       

       

      Thanks for any help,

        • 1. Re: Check to see if a record already exists
          Mike_Mitchell

          Hello, jlwayne.

           

          At least three approaches I can think of:

           

          1) Put a couple of global fields in your table, say, gFieldA and gFieldB. Make a self-joining relationship from those global fields to the fields you want, where gFieldA = Field A and gFieldB = Field B. Put the candidate values in gFieldA and gFieldB. Then, make a layout based on the self-join table occurrence (where the global fields represent the "local" or "parent" side of the relationship). From that context, execute a test:

           

               IsEmpty ( relatedTable::keyField )

           

          If the key field from the original table is empty, then it means there's no match with the global fields - and therefore, it's safe to add the new record. If, on the other hand, there IS a match, it means the combination of Field A and Field B already exists.

           

          2) Create a concatenated calculation field, such as:

           

               dupTest = Field A & "-" & Field B

           

          Then, make a value list of the dupTest field. You can run a test like this:

           

               not FilterValues ( ValueListItems ( Get ( FileName ) ; {valuelistname} ) ; {Field A test value} & "-" & {Field B test value} )

           

          3) Use ExecuteSQL to fetch the values and do something like (2). It would look something like this:

           

               not FilterValues ( ExecuteSQL ( "select distinct table.fieldA, table.fieldB from table" ; " - " ; "<cr>" ) ; {Field A test value} & "-" & {Field B test value} )

           

          where "<cr>" is the pilcrow (carriage return). You might need to enclose your field names in escaped quotes (\") or use the Quote function if they have spaces in them or begin with underscores to use the ExecuteSQL function.

           

          Anyway, maybe one of those will work for you.

           

          Mike

          • 2. Re: Check to see if a record already exists
            comment

            jlwayne wrote:

             

            If the script enters a new record that matches both field A and field B of an existing record, I don't want the new record to be added.

             

            Define a text field, auto-entering the following calculation =

             

            Field A & " | " & Field B

             

            and set the field's validation to Unique, Validate always. Make your script go:

             

            ...

            Set Error Capture [ On ]

            Commit Records/Requests

            If [ Get ( LastError ) ]

               Revert Record/Request [ No dialog ]

            End If

            1 of 1 people found this helpful
            • 3. Re: Check to see if a record already exists
              Mike_Mitchell

              Yes, that will work. Only problem would be that it leaves a record created. I was trying to offer some methods that avoid that. If that doesn't matter to you, this is definitely cleaner.

               

              Mike

              • 4. Re: Check to see if a record already exists
                comment

                Mike_Mitchell wrote:

                 

                Only problem would be that it leaves a record created.

                 

                Not really. It opens a new record, then reverts if validation fails. You can set the serial ID to generate on commit, so it doesn't skip a number.

                 

                Another option, if a script is running anyway, is do a find before creating the new record. But IMHO if it's important to prevent duplicates, there should be validation in place - and the script can just heed it along with the rest of us.

                • 5. Re: Check to see if a record already exists
                  Mike_Mitchell

                  Well, shut my mouth. Revert removes the record, as you point out. I stand corrected.