1 2 Previous Next 16 Replies Latest reply on Nov 14, 2011 2:44 PM by dansan500

    Easy Duplicate Removal

    dansan500

      Title

      Easy Duplicate Removal

      Post

      I never really understood the Duplicate Record procedure

      I have a table that contains duplicate information I need to remove.

      Table: storeroom_Issued

      Fields: Part#, Qty, Sales_Order

      I have a part# that has been issued mulitple times to the same Sales_Order and the same Qty

      In Simple terms, how can I create a script to remove the duplicates.

        • 1. Re: Easy Duplicate Removal
          philmodjunk

          Save a copy of your file just in case.

          On a layout based on Storeroom_Issued, enter find mode.

          Enter a lone ! into the Part# field

          Perform your find.

          Sort your records by Part# so records with the same Part# field will be grouped together.

          Then mark or delete all but one record of each group. Marking is easier because deleting a record puts the script on a different current record and this makes the process a bit trickier to manage without leaving a duplicate or deleting the wrong record. When you mark the records, your last step is to find all marked records and delete them.

          You can find a script for this in the knowledge base.

          Here's a slightly shorter script I recently came up with that does the same thing: You'll need to add an extra number field, Duplicate, to your table before creating this script.

          Go to Layout [storeroom_Issued]
          Enter Find Mode [] //clear the pause check box
          Set Field [Storeroom_Issued::Part# ; "!"]
          Set Error Capture [on]
          IF [ Get ( FoundCount) //duplicate values in Part# were found ]
             Sort [no dialog ; restore ] //sort by Part# field to group records
             Go To record/request/page [first]
             Loop
                Set Field [StoreRoom_Issued::Duplicate ;
                               If ( GetNthRecord ( storeroom_Issued::Part# ; Get ( RecordNumber ) - 1 ) = storeroom_Issued::Part# ; 1 ) ]
                Go to Record/Request/Page [Next ; exit after last]
             End Loop
             Enter Find Mode []
             Set Field [ storeroom_Issued::duplicate ; 1 ]
             Perform Find []
             Delete All Records []
          End IF
               

          • 2. Re: Easy Duplicate Removal
            dansan500

            The script, I got an error when creating the script... Can you tell me what I am doing wrong

            • 3. Re: Easy Duplicate Removal
              philmodjunk

              It's a typo in my script. Remove the highlighted parenthesis and the error message should no longer appear.

              • 4. Re: Easy Duplicate Removal
                dansan500

                Thanks.......

                • 5. Re: Easy Duplicate Removal
                  dansan500

                  Ok, I ran into another error before the script saved.. Did I do something wrong?

                  • 6. Re: Easy Duplicate Removal
                    philmodjunk

                    Take a closer look at the set field step where I posted the script. Set field needs two parameters separated by a semi-colon. The IF you have set up as a script step, is actually the second parameter of the Set Field step and should be an If function inside that second parameter, not an If step in the script. The expression in it is complex so I put it indented on a second line when I posted it--which is what confused you--but it's actually the second part of the set field step.

                    • 7. Re: Easy Duplicate Removal
                      dansan500

                      Ok, This is what I have, but still, when I run the script, it finds 0, but when I search the fiield Ref Part# I find 1900 duplicates

                      • 8. Re: Easy Duplicate Removal
                        philmodjunk

                        Where's the perform find step that should be placed immediately after the Set Error Capture[on] step?

                        ahem, err, I see I left it out in my example...Embarassed

                        Can't believe the typos in that sample script...

                        Here's the corrected version:

                        Go to Layout [storeroom_Issued]
                        Enter Find Mode [] //clear the pause check box
                        Set Field [Storeroom_Issued::Part# ; "!"]
                        Set Error Capture [on]
                        Perform Find[]
                        IF [ Get ( FoundCount) //duplicate values in Part# were found ]
                           Sort [no dialog ; restore ] //sort by Part# field to group records
                           Go To record/request/page [first]
                           Loop
                              Set Field [StoreRoom_Issued::Duplicate ; If ( GetNthRecord ( storeroom_Issued::Part# ; Get ( RecordNumber ) - 1 ) = storeroom_Issued::Part# ; 1 ) ]
                              Go to Record/Request/Page [Next ; exit after last]
                           End Loop
                           Enter Find Mode []
                           Set Field [ storeroom_Issued::duplicate ; 1 ]
                           Perform Find []
                           Delete All Records []
                        End IF

                        • 9. Re: Easy Duplicate Removal
                          dansan500

                          Could the problem be in line 5

                          • 10. Re: Easy Duplicate Removal
                            philmodjunk

                            That's why I posted the correction and showed the changed areas in bold face.

                            • 11. Re: Easy Duplicate Removal
                              dansan500

                              "WORKS GREAT"..................Thanks

                              • 12. Re: Easy Duplicate Removal
                                dansan500

                                If I caould ask one more question relating to Duplications;

                                Using this script:

                                If I wanted to remove something that was issued too many times, but I needed to look at a couple or three fields to get the duplication how would I do that?

                                 

                                Part#

                                Issued to

                                Issued date

                                These would be the three fields for an example, If all three fields had duplicate information I would need to remove all but on record.

                                 

                                I don't know how to fine duplicates relating to more than one field as you showed me in your script.

                                Thank you for your information.... It's much apreciated..

                                • 13. Re: Easy Duplicate Removal
                                  philmodjunk

                                  Define a calculation field with a calcualtion such as: Part# & "|" & "Issued to" & "|" & Issued date

                                  Then use the ! operator in this calculation field when searching for duplicates.

                                  One limitation of searching for duplicate in FileMaker is that returns in the field change what is found as a "duplicate".

                                  If you have a field with: "Apple¶Lemon"

                                  (¶ is the return character)

                                  and another record has "Apple" in the same field, a find with ! used as the operator will find these two records even though the values in the fields do not actually match.

                                  Likewise, a record with "lemon" in this same field will be found as a duplicate of the first record.

                                  • 14. Re: Easy Duplicate Removal
                                    dansan500

                                    So Do I make a calculation Field in the Storeroom_Issued table

                                    I don't understand where to place it in the script

                                    1 2 Previous Next