8 Replies Latest reply on Oct 12, 2011 10:08 AM by philmodjunk

    Remove Duplicate Records

    joelande

      Title

      Remove Duplicate Records

      Post

      I know I can find duplicate records using the "!" operator.

       

      But how do I remove the duplicates (while keeping one record for each duplicate)?

        • 1. Re: Remove Duplicate Records
          catinthehat

          script. run a loop> enter find mode> search for duplicates the way you were doing it "!">  if statement that if there is more than one record > go to the last record and delete it.

           

          If there are more than one duplicate, run the script again.

          • 2. Re: Remove Duplicate Records
            philmodjunk

            Perform your find with the ! operator. Sort the records on the same field so that duplicates are grouped together.

            THen enter your loop and loop through your records. You can delete duplicates in the loop or you can use set field to "mark" them and then, after the loop is completed, find and delete all "marked" records.

            Your loop can run something like this:

            Loop
               If [ GetNthRecord ( Table::FieldWithDuplicateValue ; Get ( RecordNumber ) - 1 ) = Table::FieldwithDuplicateValue // you have a duplicate]
                   Set Field [Table::Flag ; 1 ]
               End If
               Go to Record/request/page [ next ; exit after last ]
            End Loop
            Enter Find Mode[]
            Set Field [Table::Flag ; 1 ]
            Set Error Capture [on]
            Perform Find[]
            Delete All Records

            • 3. Re: Remove Duplicate Records
              catinthehat

              listen to phil :)

              • 4. Re: Remove Duplicate Records
                joelande

                OK.

                 

                I tried Phil's method.

                I added a Perform Find FieldWithDuplciateValue = ! and Sort by FieldWithDuplciateValue step at the beginning of the script and removed the "Delete All Records" step so that I could verify the results before committing.

                 

                So then I performed the script and it returned 38 of 663 records marked "1"

                As a verification, I created another layout with a sub-summary part, broken by the FieldWithDuplciateValue and placed the "Flag" field on the layout as well to verify that it marked all but one occurrence of a duplicate record.

                I did a manual search for FieldWithDuplciateValue = !, and that found 303 records.

                So already, something isn't working.

                • 5. Re: Remove Duplicate Records
                  philmodjunk

                  First, I'd put a pause after the find and sort steps and then check to see if the correct records were found.

                  If it appears to be finding all the duplicates, I'd then either use FileMaker Advanced's Script debugger or insert a pause inside the loop and watch the script step through the records to see if anything doesn't appear to work correctly.

                  • 6. Re: Remove Duplicate Records
                    philmodjunk

                    Here's another trick you can try:

                    Save a clone of your database.

                    Open the clone and use a validation rule for the field that can containe duplicates to specify Unique values, validate always.

                    Now import the records from your original file into the clone.

                    During the import, the duplicates will be automatically excluded.

                    • 7. Re: Remove Duplicate Records
                      joelande

                      I thought about the clone/import thing before I posted here, because I was looking for a more elegant solution.

                      I looked through this script one more time, and had an epiphany:

                      Something I often forgot about newer versions of FileMaker (been using since at least version 3) - after performing a sort, you have to insert a go to first record step.

                       

                      After inserting that script step, it works perfectly.

                       

                      Thank you very much for your help.

                       

                      For those that search for this issue here is my final script:

                      Perform Find [FieldWithDuplicateValue=!]
                      Sort Records [FieldWithDuplicateValue]
                      Go to Record [First]

                       

                      Loop
                         If [ GetNthRecord ( Table::FieldWithDuplicateValue ; Get ( RecordNumber ) - 1 ) = Table::FieldwithDuplicateValue // you have a duplicate]
                             Set Field [Table::DuplicateFlag ; 1 ]
                         End If
                         Go to Record/request/page [ next ; exit after last ]
                      End Loop
                      Enter Find Mode[]
                      Set Field [Table:: DuplicateFlag ; 1 ]
                      Set Error Capture [on]
                      Perform Find[]
                      Delete All Records
                      • 8. Re: Remove Duplicate Records
                        philmodjunk

                        Good catch. Should have spotted that one myself. Maybe its because I didn't get my usual caffiene fix this morning...