Have you ever wanted to create a calculation that would tell you how many duplicate records you have in a database solution? Well, you can by using a self relationship, a logical function and a few aggregate functions.
First, create a relationship in a file to itself and use as the key field on both sides ... the field you are using to find duplicates. For example, customer name may be a field that you want to check for duplicates with. It is possible to use multiple fields in a duplicate detection by concatenating them into one calculation field.
Next, we need a logic function to branch if the result is one thing or another. I’m using the CASE function but the IF function would work just fine also.
Count(Self_Customer name::Customer Name) > 1, 1, 0)
Here we are saying that if there is more than one relationship match ( duplicate ) give us a 1, otherwise give us a zero.
Next we want to get a sum of the totals of the duplicates.
Sum(Self_Customer name::Mark Name Duplicates)
This will give us a total of all the records that have a 1 in them via the customer name relationship. So only the duplicated records will be showing the counts for their corresponding duplicates.
Now we also might want to detect if the record we are on is the original or one of the duplicates. This is done by assuming the earliest occurrence of the record is the original. To do that, we use the Min function to find the lowest occurrence and compare that to the record we are currently on.
Min(Self_Customer name::Serial Number)= GetAsNumber(Serial Number);"Original";
An example file can be downloaded by clicking here
More info about the author and FileMaker in general, contact me at firstname.lastname@example.org.
© 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.