1 2 Previous Next 22 Replies Latest reply on Jun 19, 2013 11:47 AM by fmpro_novice

    Problems with duplicates



      Problems with duplicates


           I have 3 fields:    Last Name ( Text field )      First Name ( Text field )      Full Name ( Calculation of Last Name & First Name )

           If someone enters the Last and First Name of a name that is already in the database, I need a dialog box to come and say, "There is already someone entered with the same first and last name."

           Then, the entry that is already in the database would display directly below the record that is trying to be entered with the same last and first name. The options would be:

           1. Continue - which would allow the duplicate and the user could continue adding to the record;
           2. Delete - which would delete the record the user is trying to create and show all records and allow the user to continue.

           I know this should be easy, but I'm a novice and I can't find what I'm looking for. Can someone show me a script that I could use which would trigger after someone tabs out of the Last Name field?

           Thank you.


        • 1. Re: Problems with duplicates

               To start, you can define Full Name as a text field and the calculation can be an auto-enter calculation. You can actually change the field type to text, open field options and when you select the calculation option, you'll find that FileMaker automatically has set up your original calculation as the auto-entered calculation. But now you can click the validation tab and select the Unique Values validation option.

               This won't quite get you the options you want as a response to duplicate values, but this is a good "insurance" setting to make sure that you catch duplicate entries even if the user finds a way around your layout settings for checking for duplicate values.

               To get a custom error message, you'll need to use the OnObjectValidate script trigger and write a script that checks for duplicates and then displays a custom dialog with your specified buttons for what to do when it happens.

               This code in that script can identify duplicate values if the above field option was specified:

               Set Error Capture [on]
               Commit Records [no dialog]
               If [ Get ( LastError ) // duplicate was entered ]
                  Show Custom Dialog ["Error: ...
                  If [ Get ( LastMessageChoice ) = 1

               Get ( LastMesageChoice ) is the get function to use to detect which button was selected in the custom dialog. You can use Exit Script [False] to terminate the script leaving the record uncommitted if the user opts to go back and modify the name that they entered.

          • 2. Re: Problems with duplicates

                 Thanks, Phil. This helps a lot. I'm almost there.


            • 3. Re: Problems with duplicates

                   I almost have what I need but I inserted a Find in the script with the operator ! for the criteria but it will not find duplicates. No matter what I do, it shows all records. Even if I do a manual Find and insert the ! in the field, when I hit enter, it shows all records. I've seen other posts with people having the same problem. I can't figure out how to make the Find show Duplicates when I use the ! symbol.


              • 4. Re: Problems with duplicates

                     Why did you use that method? It's not what i recommended for the script. Get ( LastError ) should detect when a duplicate was entered and you might also use a self join to check for the existance of another record with the same value.

                • 5. Re: Problems with duplicates

                       I used the script you wrote with the exception of adding the Find because I wanted the record that was already in the Data Base to show right below the record that was trying to be created. Your script worked great - it just didn't show the attempt and the record that was already there. It is important to show both records at the same time because there is a possibility that the user would want to keep both records. There is a high possibility that a father and a son could be in the Data Base with the same first and last name.

                       All your script did was notify me that there was a duplicate, which was great, and it got me the right direction, but the user needs to know whether to delete the one they are trying to create because the record is already there - or go ahead and create another record - even though the last and first names are the same.

                  • 6. Re: Problems with duplicates

                         Hi Charlie^2

                         this works for me...


                    • 7. Re: Problems with duplicates

                           Hi Sym - Thanks for the script. I'm going to attempt to try this and see what happens.

                           Thank you.


                      • 8. Re: Problems with duplicates

                             Hey Sim - Any chance I could get you to send me a FM doc with that script in it?


                        • 9. Re: Problems with duplicates

                               Ok, Can I back up here for a moment and try to explore the "!" operator in finds?


                               If it works, it should show all records that are duplicates, not any that are not duplicates.  If it fails, then it would go back to the found set it had originally (with an error that it did not find any).

                               So I'm curious, when you used the "!" in you find, how did it show all records?  (Just want to know for future reference.) Thanks!

                          • 10. Re: Problems with duplicates

                                 Good point and one of the reasons why I asked why you where performing a find with the ! operator. It will find more than you want here if you are allowing duplicate names in previous cases.

                                 Instead, copy the name data into a variable or two and use it as your find criteria so that you find records with names that are duplicates of the name data in just your current record.

                            • 11. Re: Problems with duplicates

                                   I found an old reply of yours that said if the      !      wasn''t working, then it could be the result of a corrupt index. So, I deleted all the records in my file and started adding new ones. The    !     started working again. I put the    !     in the Full Name field.

                                   My script now contains 3 buttons when the error for duplicate Full Name pops up.

                                   1) OK - which allows the user to continue adding to the record, even though it's a duplicate.

                                   2) Find - which is supposed to run a script called Find Duplicates. If I run Find Duplicates by itself, it works perfectly. If there are two people with the same first and last names, it pulls them up and displays them side by side so the user can decide what to do.

                                   3) Cancel - which deletes the attempted duplicate record.

                                   This would work really well for me if the 2nd button would run the Find script, but it won't. It acts the same as if you had pushed the OK button.

                                   I have double-checked to make sure the buttons are correct and they are. The buttons are OK; Find; Cancel and all of them are pointing to the correct items in the script.

                                   This is what the script looks like:


                              • 12. Re: Problems with duplicates

                                     I'm not sure why when you push the Find button it acts as the OK.


                                     In any case, I would change your 3 If statements with the Get ( LastMessageChoice ) to one If-Else statement. Ex.

                                If [ Get ( LastMessageChoice )=1 ]
                                Else If [ Get ( LastMessageChoice )=2 ]
                                Else If [ Get ( LastMessageChoice )=3 ]

                                End If


                                Set Variable [ $lastMsg; Value:Get ( LastMessageChoice ) ]

                                If [ $lastMsg=1 ]
                                Else If [ $lastMsg=2 ]
                                Else If [ $lastMsg=3 ]

                                End If

                                     Reason: If your second If statement that is the Find.  Runs a script that ends with a "Show Custom Dialog" The third If statement will use the results from that Dialog and not the original one in this script.  Hence, it could still run.

                                     However, I do not know what your Find Duplicates script does, so that is only a warning.  I doubt that this is the cause of your problem.


                                     I'm also not sure why your Find won't work, unless I see the "Find Duplicates" script.

                                     Also, if your database is corrupt, the best thing to do is make a backup of the file and use the Recover menu item to repair it.  (I'm not sure if the same applies with corrupt indexes.  Deleting Records and adding new ones, though, may allow for re-indexing.)


                                     Hopefully that helps

                                • 13. Re: Problems with duplicates

                                       Changed my script to do what you suggested above, but it still does the same thing. First screenshot is the script changed the way you suggested; 2nd screenshot is the Find Duplicates script that works perfectly - when I run it by itself.

                                  • 14. Re: Problems with duplicates
                                    /files/287c878fad/screenshot2.jpg 292x168
                                    1 2 Previous Next