Finding Duplicate FileMaker Records Via A Scripted Loop

From Dwayne Wright PMP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit. For the best experience, please visit the journal directly by clicking (here). 

It has been many years ago when I created my first looped script, but I believe it was created to deal with duplicate records in a found set. This is a technique I used for years and you can use it for other flagging actions besides duplicates.

First we want to find the duplicates. This is done by going into FIND mode and placing an exclamation point symbol ( ! ) in the field. This is the symbol for finding duplicates. You can find this listed on the left side of the screen, in the status area, while you are in find mode.

After you perform the find, you will have all of the duplicate records. You may be thinking "Yee Haw", delete the found set and head to the break room for a freshly toasted bagel. Once returning however, you would be dismayed because along with the duplicates, the originals were deleted as well.

SORT DUPLICATES
We want to put all our duplicates in a row, like soldiers in review. This sets us up for the next step.

MARK DUPLICATES
I think this script was my introduction to the ScriptMaker Loops. What we are going to do is go from record to record asking "Did I see you before?" If the answer is yes, we are going to set the field called "Flagged as Duplicate To Delete" with the letter "X" The individual steps are

Freeze the Window
This speeds things up because the monitor doesn't need to show you what it's doing.

Go To Record (first)
This makes sense, we are starting at the beginning of the line.

Set Field (Global Marker, “the field you used to indicate what was a duplicate”)
This is how we are going to know if each record matches the previous one in the loop. For the sake of this discussion, let us say it is a field called email. We are wanting to tag records with duplicate email address information.

Loop
Now we are going to do the same thing again and again until we have found the last record.

Go To Record Next [Exit after last]

If [EMAIL = "Global Marker"]

Set Field ["Flagged As Ok To Delete", "X"]
We know that a global field has the same information for all records. So we are going to the next record and seeing if the field we are checking for duplicates with (email) is equal to the global field. If it is, we are marking it in a field that we are later going to use the find command on, to then delete (but I'm getting ahead of myself)

Else

Set Field ["Global Marker" = "EMAIL"]
So if this next item on the shelf is different, we need to remember it before we go to the next record ... correct?

End If

End Loop

We are going to be in the loop until we hit the last record. It is just now that I realize I made a mistake. The way it is now, we will not mark the last record if it is a duplicate. We should put the Go To Record Last a little farther down in the script.

Next we do a find command to locate the records we marked and then delete them. I did put in a little error checking logic to halt the script, if no records are found.

=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.