How to count the number of each duplicate record in Filemaker Pro 13

I have a Filemaker Pro database that contains duplicate records. After finding the duplicates, how do I find a count of how many duplicates of each duplicate record exists (i.e. 2,3, etc.)


John O'ConnorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Will LovingPresident, Dedication Technologies, Inc.Commented:
Hi John, a common way of finding and counting duplicates is to create a "self-join" relationship, joining two occurrences of the same table using the field you wish to check for duplicates on. For example, if you have a table (and table occurrence or "TO" as it's usually abbreviated) called Contacts and you wanted to check for duplicate records using a field called "Full Name", you would create a second TO for the Contacts table and name it something like "Contacts_SJ_FullName". Then create the relationship from the FullName field on Contacts to the same field on Contacts_SJ_Fullname.

Once you have the relationship setup you can then add a calculation field in the Contacts table to count duplicates. The calculation would look like this:

Count( Contacts_SJ_FullName::FullName )

Keep in mind that every record will see itself, so for records that have no duplicates, the count will be 1. You can  then perform a find on that calc field for greater than one.

Here's one further trick. If you want to identify all duplicate records, but not the oldest or original record (in creation order) you can do so as long as each record has a unique serial number (I'll use ContactID in this example) that was auto-entered during creation. To identify duplicates, use this calc.

Case( Contacts::ContactID <> Contacts_SJ_FullName::ContactID ; 1 )

This works because when you relate one record to a group of other records, the first record it "sees" or relates to will also be the oldest record or the one with the lowest serial number. Since only the oldest record will be have the same serial number as the first record that all duplicates "see", then the calculation gives a value of 1 for every record except the first, all the ones that do not have the same serial number as the first/original/lowest serial number record. You can then perform a find on any record that has a value of 1 in it for the above calculation and delete those, which will leave only the oldest record which have a value of null or nothing

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John O'ConnorAuthor Commented:
Thanks Will, excellent solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.