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

    Finding Duplicate Records

    MatthewMackay

      Title

      Finding Duplicate Records

      Post

      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.

      Cheers

      Screen_shot_2011-05-11_at_20.17.19.png

        • 1. Re: Finding Duplicate Records
          philmodjunk

          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
            MatthewMackay

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

            • 3. Re: Finding Duplicate Records
              philmodjunk

              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:

              Jim
              Joan
              Jim
              Steve
              John
              Joan

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

              Jim
              Joan
              Jim
              Joan

              I can then sort the records to get:

              Jim
              Jim
              Joan
              Joan

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

              • 4. Re: Finding Duplicate Records
                MatthewMackay

                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
                  philmodjunk

                  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
                    MatthewMackay

                    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
                      philmodjunk

                      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]
                      Loop
                          Exit Loop If [Get (FoundCount ) = 0 ]
                          If [ $PrevValue = YourTable::cCalcField ]
                                Delete Record [no dialog]
                          Else
                                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