I don't think there is a simple way via a script step or something. If someone has a nice easy answer, I hope to hear it.
You can probably rig something up though, using a relationship and calculation like this:
Create a second table occurrence of the table you are looking at, and relate it to the original occurrence based on the field/fields that define duplicity to you. For instance if this is a contact, maybe you relate based on email address. Call this occurrence a self join (just a convention). Ex: SelfJoinOnEmail.
Then define a calculation that is something like: Count ( SelfJoinOnEmail::_pk ) - 1. Call it cNumDuplicates. Doesn't matter what field you count, as long as it is in the SelfJoin occurrence.
Then just find records where cNumDuplicates > 0. Voila.
I agree with the first response, that you should create a second occurence of the table relating on the field that has duplicate information. Let's call that relationship "Dups". Also, it is important that the table has a unique identifier for each record, such as Record ID.
Then make a calc field that is:
If(Record ID = Dup::Record ID), 1, 0)
then, do a find for 1 and you will have the unique records (both, those that do not have a dup, or just one of the dups.)
I think nseala adds a necessary component, as I slightly misread the original post. My method allows you to find records that are unique or not unique, but that isn't exactly the same as what the DISTINCT keyword in mysql returns.
Your wording in your subject vs. the body message threw me. But between these two methods, you should be able to get either the set of completely unique records, or the set of records containing all distinct values for a particular subset of fields.
Thank you both for your help.
I've realized that my Message Subject "Finding set of unique records" caused some confusion, and I should have said I wanted to find records with distinct subsets of fields:
- so many records might have either "value A" in "field 1" OR "value B" in "field 2"
- but only 1 record could have both "value A" in "field 1" AND "value B" in "field 2"
While moving data from Filemaker to MySQL and back, I realized the "DISTINCT" function was a nice feature in cleaning up duplicates and thought maybe I'd overlooked it's twin in Filemaker.
Both solutions look good, and have made me think that maybe I can adapt them to make a custom function that would simulate the effect of having another table occurence.... maybe setting one or more variables and recursively looping....?
My goal is something I could easily add to new databases to get this function.
I need this feature! 5 years later...has FileMaker made any progress on this?
I need this feature! 6 years later...has FileMaker made any progress on this?