7 Replies Latest reply on May 11, 2011 2:27 PM by philmodjunk

    Finding Duplicate Records



      Finding Duplicate Records


      I'm trying to write a script to find duplicate records, it works fine as a standard find, but I'm trying to make it loop through all the records, if anyone can have a look at the script and give me some pointers that would be great.



        • 1. Re: Finding Duplicate Records

          The biggest problem with your script is that the finds completely change what records are in your found set. That then keeps the loop from enabling you to loop through all the records.

          I think a better approach is to use ! in a find request to find all records with dupilcate values, sort the records by this same field to group them by the duplicate entries, then loop through this list to find and delete the duplicates.

          • 2. Re: Finding Duplicate Records

            Could you give an example of using !, i've never seen that being used before

            • 3. Re: Finding Duplicate Records

              If you enter find mode and put ! as the sole character in a field, then perform the find, FileMaker will find all records where duplicates exist in that field.

              So if I have:


              and perform a find with ! in this name field, I'll get:


              I can then sort the records to get:


              and then I can loop through the records and either delete the duplicates or mark them for later deletion.

              • 4. Re: Finding Duplicate Records

                I'm trying that just now, the problem is the records I'm searching through all have several fields each, I'm only looking for records that have exactly the same field contents bar the PK_ID.

                I've created a CalcField that takes the data from all the required fields, I thought I could search for exact matches in that field but it's proving more difficult than I thought.

                Can you perhaps describe in more detail why adding the loop to my script stops the find from working?

                • 5. Re: Finding Duplicate Records

                  Using a ! to search in the calc field should work as long as you do not have any return characters in that field. There's a known bug that identifies duplicates on any one return separated item if another record has that one item in the field:

                  For More Information see:    Find Duplicates (!) matches by duplicate paragraphs

                  This is one of many acknowledged bugs that can be found in the Known Bug List here in the Report an Issue section of the forum.

                  It can also be downloaded as a database file from:   http://www.4shared.com/file/8orL8apk/FMP_Bugs.html

                  For your script that you've written, if you have FileMaker Advanced, enable the script debugger and use it to step through your script and watch how your found set and current record changes as the script executes.

                  If you don't have advanced, you should get it. In the mean time, insert a pause/resume step right after the loop step and run your script. Click continue or press enter to continue the script and watch how your found set changes.

                  • 6. Re: Finding Duplicate Records

                    Ok, using the !, I can see how it finds and sorts the records now so I can see all the duplicates grouped together.

                    Perhaps I'm looking at this in the wrong way, but if I can omit 1 of each of the grouped duplicates and leave 1, that would solve the problem. Is there any way of doing this?

                    It's annoying when you can see how to do it manually but trying to get a script to do the same steps.

                    • 7. Re: Finding Duplicate Records

                      Try this script:

                      Enter Find Mode [] //clear pause check box
                      Set field [YourTable::cCalcField ; "!"]
                      Set Error Capture [on]
                      Perform Find[]
                      Sort Records [Restore ; No dialog ] //sort on cCalcField to group duplicates
                      Go To Record/Request/Page [First]
                          Exit Loop If [Get (FoundCount ) = 0 ]
                          If [ $PrevValue = YourTable::cCalcField ]
                                Delete Record [no dialog]
                                Set Variable [$PrevValue; Value: YourTable::cCalcField]
                                Omit Record
                          End If
                      End Loop
                      Show All Records
                      Sort [Restore ; no dialog] //optional, but a good way to see that the script worked