1 2 Previous Next 21 Replies Latest reply on Nov 12, 2010 8:12 AM by miw

    Avoiding Duplicate Records on Entering

    Keystone

      Title

      Avoiding Duplicate Records on Entering

      Post

      Hi,

       

      Looking for a simple script that I can add to a layout that when two fields (i.e. last name and first name) are entered, a pop-up warning comes up telling the user that they've entered a duplicate record, and would they like to continue?

       

      Thanks in advance! 

        • 1. Re: Avoiding Duplicate Records on Entering
          philmodjunk
            

          Create a third text field, Fullname.

           

          In the auto-enter field options specify an auto-entered calculation:

           

          LastName & ", " & FirstName

           

          (The ", " term is not strictly needed, but it sets it up so you can use this field in layouts.)

           

          Clear the "Do not replace existing value..." check box.

          Click the validation tab and select "unique value".

          Enter a custom validation message if you want.

          • 2. Re: Avoiding Duplicate Records on Entering
            Keystone
              

            Thanks for the hint! 

             

            This is a good solution, however the pop-up only comes up when you navigate away from the record, i.e. when I click on a button to go to a new layout, or go to enter another record.

            I was hoping that I can find a solution that causes a pop up as soon as the full 'Fullname' field is populated, which would stop someone from entering all the other fields in the layout.

             

            Ideally, when they see a message that says "Duplicate Record, Continue?", and they click on "No", the record is automatically deleted.

             

            Thanks again! 

            • 3. Re: Avoiding Duplicate Records on Entering
              philmodjunk
                

              The field doesn't populate until you exit one of the name fields. Clicking on a button or changing records exits the field and then triggers the validation test.

               

              With FMP 10, you might try using a script trigger, but most such triggers also will not "fire" until you exit the field.

               

              Think about it this way:

               

              Let say that John Smith is alread entered into the database.

               

              If you start to enter John Smithson in a new record, do you want this message to appear when the user hits the h key or do you want nothing to happen as John Smithson is not a duplicate name?

              • 4. Re: Avoiding Duplicate Records on Entering
                Keystone
                  

                That definitely makes sense, but what's happening is that the pop-up isn't coming up until I actually leave the record. I'm using FMP 10, BTW.

                 

                There's no need for the warning to come up when I hit the "h" to use your example, however I'd like the warning to come up when I click or tab away from the field... maybe I'm missing something.

                 

                I just don't want a user to enter the name(s), addresses, notes, etc. etc. and THEN get the pop-up...

                 

                Thanks, I appreciate the help. 

                 

                 

                • 5. Re: Avoiding Duplicate Records on Entering
                  philmodjunk
                    

                  Ok, I get it. I'm forgetting that the validation rules don't kick in until the record is committed. You could write a one line script:

                   

                  Commit Record

                   

                  Then use OnObjectExit to trigger this script when you exit the field.

                   

                  To set a script trigger, select the field in layout mode and right-click (ctrl click for Mac) it and choose Script Triggers.

                  • 6. Re: Avoiding Duplicate Records on Entering
                    Keystone
                      

                    Fantastic, thanks... that works. Any way to automatically delete the file after selecting "no" when asked if the user wants to continue?

                     

                    (I'm a complete newbie at scripts) 

                    • 7. Re: Avoiding Duplicate Records on Entering
                      ninja
                        

                      A different approach:

                       

                      Enter the two names through custom dialog, do a find, and create the new record only if it doesn't already exist.

                       

                      Setfield (fieldA; "")

                      Setield (fieldB; "") 

                      Custom Dialog (trap two fields for first and last names; fieldA, fieldB)

                      Enter Find Mode

                      Setfield (firstname; fieldA)

                      Setield (lastname; fieldB)

                      Set Errorcapture [on]

                      Perform Find

                      If (Get (FoundCount) > 0)

                           Exit Script

                      Endif

                      New Record/Request

                      Setfield (firstname; fieldA)

                      Setield (lastname; fieldB)

                      Setfield (fieldA; "")

                      Setield (fieldB; "")

                       

                      Wow, no smileys...

                      HTH

                      • 8. Re: Avoiding Duplicate Records on Entering
                        philmodjunk
                          

                        Try this script as your OnExit Trigger:

                         

                        Set Error Capture [ On ]

                        Commit Records/Requests

                        If [ Get ( LastError ) ≠ 0 /* validation error */ ]

                           Show Custom Dialog [ Message: "Combined first and last names must be unique."; Buttons: “OK”, “Cancel” ]

                           If [ Get ( LastMessageChoice ) = 1 /* OK was clicked */ ]

                              Go to Field [ NameValidation::Last ]

                              Exit Script [ Result: False ]

                           Else

                              Revert Record/Request [ No dialog ]

                           End If

                        End If

                         

                        Set error capture keeps the system from popping up error messages that interrupt the script. Get ( LastError ) returns 0 if there's no error, or the error code of the error if one occurs--so this detects the duplicate name. Exit Script [ False ] terminates the script and leaves the cursor in the field being exited.

                        • 9. Re: Avoiding Duplicate Records on Entering
                          Keystone
                            

                          Great, thanks. I guess I'm a little lost on where to enter this script... i.e. when I go to "Manage Scripts", click on "New", it gives me a list of categories, (including Commit Records/Requests), but I'm not sure how to enter the script you outlined.

                           

                          Dam*n Newbies... I know! 

                          • 10. Re: Avoiding Duplicate Records on Entering
                            philmodjunk
                              

                            From Manage Scripts...

                            Simply click New... to pop up the script editor.

                            Double Click each step on the left to add the script step to your script.

                            For items with [parameters] shown to the right of the script step, buttons and checkboxes appear in the dialog when the script step is highlighted that you can use to specify the parameters.

                            • 11. Re: Avoiding Duplicate Records on Entering
                              Keystone
                                 Awesome, thanks so much. Have a great weekend/Halloween.
                              • 12. Re: Avoiding Duplicate Records on Entering
                                Keystone
                                  

                                Sorry to have to come back to this, but I'm having problems figuring out how to modify the script that PhilModJunk posted earlier (which has everything to do with me)

                                 

                                Here's my issue...

                                 

                                - I have a third field that combines First_Name and Last_Name, called Full_Name

                                 

                                - Full_Name is displayed on my Header (for display purposes only, the user doesn't tab to it)

                                 

                                - What I'd like to see is after I enter a value in First_Name AND Last_Name (after I tab out of Last_Name), a pop up dialog box comes up warning the user of a duplicate (without having to click on the Full_Name field).

                                 

                                - The dialog box should give the user the following options:

                                 

                                "This name exists, what would you like to do?"

                                 

                                Options: "Continue with this record" ; "Go to Duplicate" 

                                 

                                *Basically I want the user to be able to check to see if the new record they're attempting to enter already exists. If they "Go to Duplicate" and it's the same person, great. If not, they just have to click on "New Record" again, but this time they just click "Continue with this record" 

                                 

                                Any suggestions on a script that would allow me to do this?

                                 

                                 

                                Thanks in advance!  

                                • 13. Re: Avoiding Duplicate Records on Entering
                                  philmodjunk
                                    

                                  Set Error Capture [ On ]

                                  Commit Records/Requests

                                  If [ Get ( LastError ) ≠ 0 /* validation error */ ]

                                     Show Custom Dialog [ Message: "This name exists, what would you like to do?"; Buttons: "Continue with this record" , "Go to Duplicate" ]

                                     If [ Get ( LastMessageChoice ) = 1 /* Continue with this recordwas clicked */ ]

                                     Else

                                        Set Variable[$FullName; Yourtable::YourFullNameField]

                                        Commit Record

                                        Delete Record/Request [ No dialog ]  //Discard record you started to create with duplicate name

                                        Enter find mode []

                                        Set field [Yourtable::YourFullNameField; $FullName]

                                        Perform Find []

                                     End If

                                  End If

                                   

                                  Set both your first and last name fields to trigger this script when the field is exited.

                                  • 14. Re: Avoiding Duplicate Records on Entering
                                    philmodjunk
                                      

                                    Qutoing from the other thread:

                                     

                                    I don't see how I add these lines to the script: 

                                     

                                    Go to Field [ NameValidation::Last ]        (do I click on "Select Perform" or "Go to target Field"?) Why can't I get the script line to look like this??

                                     

                                    Go to target field. If it doesn't look exactly like this, don't worry, as long as you see the correct table and field listed.

                                     

                                          Exit Script [ Result: False ]            (when I enter this into the calculation box, this just gives me a "The specified table cannot be found" error.)

                                     

                                    Enter just the word False, nothing else, no quotes. Then you'll see the result you want.

                                    1 2 Previous Next