6 Replies Latest reply on Dec 22, 2013 9:14 PM by jgalt

    Finding set of unique records

    ncam

      Title

      Finding set of unique records

      Post

      Is there an easy way to find a set of unique records (no duplicates) as you can in MySQL?

       

      I've made Filemaker scripts before that flag duplicates and delete the extras , but was wondering if there was a function or script step similar to "DISTINCT" in MySQL that would return a set with just one each of all records, even if there are some duplicates.

       

      This would give me more options and speed up some of the data merges I'm doing between Filemaker and MySQL. Sometimes I send data to MySQL, find a "DISTINCT" set of records, and bring them back just to avoid setting up a flag duplicates script.

       

      Thanks for any info!

       

        • 1. Re: Finding set of unique records
          swc
            

          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.

           

           

          • 2. Re: Finding set of unique records
            NancyAdelman
              

            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.)

             

            • 3. Re: Finding set of unique records
              swc
                

              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.

               

               

              • 4. Re: Finding set of unique records
                ncam
                  

                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.

                 

                • 5. Re: Finding set of unique records
                  jgalt

                       I need this feature! 5 years later...has FileMaker made any progress on this?

                  • 6. Re: Finding set of unique records
                    jgalt

                                        I need this feature! 6 years later...has FileMaker made any progress on this?