9 Replies Latest reply on Feb 21, 2014 9:57 AM by scottworld

    Wish there was a way to search for duplicates within the found set

    scottworld

      Summary

      Wish there was a way to search for duplicates within the found set

      Product

      FileMaker Pro

      Version

      13.x

      Operating system version

      OS X 10.8.x

      Description of the issue

      Really wish there was a quick way to search for duplicates WITHIN THE FOUND SET, without having to script an entire tedious looping script in order to do so.

      The OBVIOUS method of doing this would be to perform a "Constrain Find" for ! (the exclamation point) in the field that you're searching for duplicate values.

      However, FileMaker has no idea what you mean when you perform this sort of a search. Using the exclamation point to find duplicates is essentially worthless when you're performing a CONSTRAIN FIND… the exclamation point ONLY WORKS when you're doing a normal find.

      So we are stuck creating a long looping script in order to find duplicates within the current found set.

        • 1. Re: Wish there was a way to search for duplicates within the found set
          TSGal

               scotty321:

               Thank you for your post.

               I recommend that you enter this suggestion into our Feature Requests web form at:

          http://www.filemaker.com/company/contact/feature_request.html

               The entries into this web form populate a database that is hosted and monitored by Development and Product Management.  Each entry is discussed and considered for possible implementation in a future release.  Although I could copy your post and paste it into the web form, there are a couple of questions asked that only you can answer.

               TSGal
               FileMaker, Inc.

          • 3. Re: Wish there was a way to search for duplicates within the found set
            scottworld

                 It's possible that FileMaker envisioned this to work this way:

                 "A duplicate search is ALWAYS based on whether the record has a match anywhere in the database, regardless of how the found set has been limited."

                 However, even if FileMaker envisioned the behavior to work in this fashion, there is yet another bug when doing a Constrain Found Set on duplicates (the exclamation point), which is that it will also return results that have no duplicates at all in the entire table. So it doesn't actually omit the unique records out of the found set at all.

            • 4. Re: Wish there was a way to search for duplicates within the found set
              philmodjunk

                   I can think of a work around that eliminates the looping.

                   Perform your find or other action to get your original found set.

                   Use the new List of summary field to put a list of all the found set's primary keys into a global field.

                   Define a self join that matches by the global field of primary keys (to limit the match to the current found set) and also by the field that contains possible dupcliate values.

                   A calculation that uses the count function to count the related records from that relationship will return a value greater than one of there are duplicate values within the found set.

              • 5. Re: Wish there was a way to search for duplicates within the found set
                scottworld

                     Hi Phil,

                     Thanks so much for the idea! I don't think that idea will work, though, because the relationship will match ALL the records in the entire table, not just the found set.

                     This is the method that I ended up using:

                     I sorted my found set by the field which I'm looking for duplicate values in, then looped through the found set from top to bottom... using GetNthRecord() to test the current record's value against the previous record and also against the next record's value. If both tests are negative, I omit that record. Otherwise, I go to the next record.
                      
                     Thanks,
                     Scott
                • 6. Re: Wish there was a way to search for duplicates within the found set
                  philmodjunk
                       

                            because the relationship will match ALL the records in the entire table, not just the found set.

                       Not when you match records using the list of IDs from the current found set AND the field that may contain duplicate values. The first part limits the matching to only records from the current found set. It's really only practical in FileMaker 13 due to needing the new "list" type summary field added in that version of the software.

                  • 7. Re: Wish there was a way to search for duplicates within the found set
                    scottworld

                         But every record in the entire table has a value in that field, so it would be matching on other records that aren't in the found set. (BTW, you could still manually get the "list of" feature in previous versions by doing a "Copy All Records" or looping through records.)  

                         Here is the exact situation, so you can see a real-world example. This is what my client is trying to accomplish:

                         - He is a talent agent who books children on movies & TV shows & commercials.
                         - He is looking at a found set of hundreds of children.
                         - Each child has a "Family ID", which is the ID# of the family that they belong to. 
                         - He would like to reduce (constrain) his found set to only those children whose Family ID's have 2 or more children IN THAT CURRENT FOUND SET. In other words, he wants to reduce his found set to siblings who are already in the original found set. 
                         - So let's say that Family ID #35 normally has 8 children in the entire FileMaker table who all have that family ID, but ONLY ONE child from that family is currently in his current found set. He wants that one child to be removed from his current found set, because none of that child's brothers or sisters are already in the found set with him. 
                         - But let's say that TWO (OR MORE) CHILDREN from Family ID #35 are in his current found set. He wants all of those children to stay in his current found set. 
                          
                         To solve this problem, I wrote the script in the screenshot below. I really didn't need to add that $last_record variable, but I thought it would be cleaner to add it in.
                          
                         See screenshot below.
                          
                         Best,
                         Scott
                          
                    • 8. Re: Wish there was a way to search for duplicates within the found set
                      philmodjunk
                           

                                But every record in the entire table has a value in that field, so it would be matching on other records that aren't in the found set.

                           That is not the case. As the relationship only matches to records in the current found set.

                           Your match fields would look like this:

                           LayoutTableOccurrence::gIDList = LayoutTableOccurrence 2::PrimaryKey AND
                           LayotuTableOccurrence::DupField = LayoutTableOccrurrence 2::DupField

                           The first pair of match fields limits the matching to only those records in your current found set.

                           

                                So let's say that Family ID #35 normally has 8 children in the entire FileMaker table who all have that family ID, but ONLY ONE child from that family is currently in his current found set.

                           In which case, the count returned is one and it is not identified as a duplicate within the found set. What you describe would be the case if the self join only matched by the DupField and not also by the list of IDs.

                           

                                BTW, you could still manually get the "list of" feature in previous versions by doing a "Copy All Records" or looping through records.

                           I'm aware of that but don't consider it a truly practical option due to the amount of extra layout design and coding needed to make it work.

                      • 9. Re: Wish there was a way to search for duplicates within the found set
                        scottworld

                             Ahhhhh, I see what you're saying. Thanks for the further explanation! That makes sense!! The list of ID's is narrowing the relationship to just the current records. That's a pretty nifty way of doing this trick, without a looping script. Thanks, Phil! :)