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:
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
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
IDfield 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.