Duplicate data always seems to creep it's way into your system no matter how hard you try. Unfortunately, in FileMaker there is no easy way to deal with junk. Or is there?

This process depends on the fact that you have a unique id in your table. Ideally, this is an auto-enter serial number. If you have one of these then we are ready to go... if not, shame on you! You should pretty much always have a unique id of some type in your table and quite frankly you might as well make it a serial number. Go ahead and create one now... I'll Wait.

Alright, at this point you are ready to go. Now you just need to decide what fields determine if the data is duplicate. If you want to weed out all products with the same sku or if you want to axe all customers with the same first name, last name and zip this solution is going to work for you.

Now that you have your fields ready, we are going to create a self-relationship. Create a new table occurrence of the table you want to remove duplicates from and connect it to the original one. Using my two examples from above, here is what your relationships should look like:

[Archived image: This relationship links a product record to any other duplicates.]

or

[Archived image: This relationship links a customer record to any other duplicates.]

This relationship says: "Find me all the records that don't have the same unique id as this one but have matches in all the fields I want." You can add as many predicates to this list as you want and it will still work.

Note: Predi-whats? Predicates. That's the nerd word for the conditions you add in the Edit Relationship dialog box when you tell FileMaker how two tables relate.

Now that you have your relationship setup you have to make yourself a looping script. For this example, the layout containing all the records is called record_list and the relationship we are using to determine if there are duplicates is named duplicate_records:

 Go To Layout[ `record_list` ]
 Show All Records
 Go to Record/Request/Page [ First ]
 Loop
    If[ Not IsEmpty( duplicate_records::ID ) ]
       Delete Record/Request [ No Dialog ]
    Else
       Go to Record/Request/Page [ Next, Exit After Last]
    End If
 End Loop

So you are basically just looping through all the records and checking to see if there is a related duplicate.... If there is you don't need it, so we just remove the record.

Note: This method of deleting records will remove the oldest record in the table in terms of creation order. So if you were to create a record today and one tomorrow, the record you created today would be deleted. If you would like to keep the oldest version of the duplicate record, just sort your ID field in descending order before executing the above loop

Instead of showing all the records, you first to narrow your found set by simply finding the records that have duplicates. To do this just execute a find on the id field of the duplicate_record table occurrence while you are on the record_list layout. Whatever you do... your table is now free of pesky, garbage data.