6 Replies Latest reply on Aug 28, 2014 10:31 AM by philmodjunk

    Checking for duplicate records by field on record creation

      Title

      Checking for duplicate records by field on record creation

      Post

           Hi

           Can someone explain the best way to check that a record does not exist when i create a new one - specifically by the 'name' field.

           I have a very large customers database so want the check to be quick on creation of new record - I assume the script would execute when the user exits record? The name field is automatically calculated as an auto enter calculation of first and second name or company name. 

            

           Thank you

        • 1. Re: Checking for duplicate records by field on record creation
          philmodjunk

               And what should you do if you get two customers with the same name?

               Leaving that aside for the moment, I suggest a set up that checks for a duplicate name first and only creates the new record with that name if it passes your validation checks for a duplicate entry.

               Example:

               Set up two global fields, YourTable::gFirstName, YourTable::gFirstName. Add a new table occurrence of YourTable so that you can set up a self join using these global fields as match fields:

               YourTable::gFirstName = YourTable2::FirstName AND
               YourTabls::gLastName = YourTable2::LastName

               This script can be performed by a button or script trigger to check for an existing record with this name and creates a new record if one does not exist:

               If [IsEmpty ( YourTable2::LastName ) // record with this name does not exist ]
                  New Record/Request
                  Set Field [YourTable::FirstName ; YourTable::gFirstName ]
                  Set Field [YourTable::lastName ; YourTable::gLastName ]
               Else
                  Show custom Dialog ["A record with this name already exists"]
                  You can add scripting here to do what you want to see happen when a record with this name is already present in YourTable.
               End If

          • 2. Re: Checking for duplicate records by field on record creation

                 You are right -- I think I shall stick to a customer code to use as the name to prevent duplication...

                 However, I shall implement the search script for existing customers anyway as some people register twice....

                 Thank you

            • 3. Re: Checking for duplicate records by field on record creation
              Markus Schneider
                   You can have an extra layout for the creation of records with some buttons (and some scripts behind those buttons)
                    
                   - Enter the data into global fields
                    
                   - Check for existing data using (ie) the 'Tannenbaum' method (typing the first letter shows all that are starting with that letter, enter a second letter shows all starting with those two Letters and so on).
                    
                   - if there is already an entry for that customer, stop it by selecting the already existing one (no new record is created)
                    
                   - if no record exist for that customer, klick on a button with a script that creates a new record with the data entered in globals
              • 4. Re: Checking for duplicate records by field on record creation
                Markus Schneider

                     I've had written a much longer posting - but I lost most of the text (might be a known issue with this forum software, who knows...). Sorry for that )-:

                      

                      

                • 5. Re: Checking for duplicate records by field on record creation

                       No problem - Your suggestion sounds like a good one. I shall attempt implementation once I have sorted out another issue I have exporting to excel

                       Thank you for your help

                  • 6. Re: Checking for duplicate records by field on record creation
                    philmodjunk

                         Due to a noxious forum bug, please protect yourself with a "Select-All, copy to your clipboard" action just before submitting a private message or comment to this forum. The bug can lose your comment and log you out of the forum--forcing you to sign back in and re-enter the comment or message. By copying to the clipboard before posting, you can re-enter your message by pasting from the clipboard instead of having to retype it all over again.