1 2 Previous Next 16 Replies Latest reply on Apr 11, 2012 4:43 AM by swheeler

    Find Duplicates within a Found set


      I'm trying to find duplicate records within a found set. The found set consists of records within a date range but some of the records found will be duplicates of records outside the date range. I only want to find duplicated records within the found set, ignoring any records within the found set that have a duplicate outside it. If I use ! after finding the date range it finds what looks like single records but they are one of the duplicates that falls within the original found set. Example: I have 3 records with the same data in field 1 and therefore they are duplicates but only one of the records falls within my date range. I don't want any records to show because there are no duplicates within the date range even though the record has duplicates.


      Hope that makes sense. Anyone any ideas please?


      I've browsed the other discussions on duplicates but they don't quite fit this scenario.

        • 1. Re: Find Duplicates within a Found set

          Have you looked at Constrain Found Set? Once you have something FOUND, you can perform another find within that using this script step. (or manually from the menu)





          • 2. Re: Find Duplicates within a Found set

            That was how I expected it to work but it doesn't. What I have left after the Constrain Found Set are some single records within the Date Range but that have a duplicate that wasn't shown in the original Find - the duplicate still doesn't show. I want to know if a record has a duplicate within the Date Range but I want neither record to show if it hasn't.

            • 3. Re: Find Duplicates within a Found set

              It might help if you post your script (if any).


              I might be confused, too. You FIND, then you find within that for duplicates. But are you saying that you DON'T want to show the records that have duplicates (in or out of the original find)? You just want to show the records with NO duplicates (within the range)?



              • 4. Re: Find Duplicates within a Found set

                Not quite. As an example, I have 5 records with the following dates (1/1/12, 5/1/12, 1/2/12, 5/2/12, 1/3/12) these same records have a field called KEY with respective values (A, B, A, C, B). I only want to display records that have a duplicated KEY in January and February. My first find will locate records 1, 2, 3 and 4 but exclude 5. I'm only interested in duplicates so my next FIND should only return records 1 & 3 because they are the only ones with duplicates where both are within the date range. Using ! in the Constrain returns records 1, 2 and 3 (but not 5). 1 & 3 are obvious duplicates but record 2 creates the confusion.

                • 5. Re: Find Duplicates within a Found set
                  Stephen Huston

                  You could use your initial found set, sort it on the field that you want to test for duplication, then run a loop through the records testing the value in that field against the value in the previous record using $variables to store and set the values to test.


                  You could remove/omit the first instance of the value and go to next record when the  value matches, leaving you with a found set of just the extra duplicates.

                  1 of 1 people found this helpful
                  • 6. Re: Find Duplicates within a Found set

                    Thats almost there but I want to keep all the records that are duplicates and discard those that aren't duplicated within the records found at the first pass. It isn't just the second and subsequent records but both (or all) duplicated records that I want to find.


                    In my example earlier I want to keep records 1 & 3 for analysis of other fields and discard record 2.


                    I have a very crude answer that works. FIND records that fall within the date range and import them into a temporary table. Then do a Find duplicates. Whilst that works by cutting out the records that aren't in the date range at the first pass (and therefore the spurious duplicates) I'm not now working with the original records. If I need to make a change I need to re-find the original records. It startes to get a little messy.


                    Thanks for the thoughts so far though. Its certainly stimulating.

                    • 7. Re: Find Duplicates within a Found set
                      Stephen Huston

                      Take a look at FileMaker's built-in Help file on Finding Duplicates.


                      They offer a solution which requires adding a field and a relationship, but then supports finding all copies of duplicates.


                      This might be what you want if you need all instances of a record where there are duplicates, both the original and the copy(ies).

                      • 8. Re: Find Duplicates within a Found set

                        I've had problems finding duplicates within a found set too.


                        My solution was to find duplicates then constrain that set by the other find criteria




                        • 9. Re: Find Duplicates within a Found set

                          Thanks Malcolm but that does exactly the same as doing it the other way around.


                          In my example above, finding duplicates returns records 1 & 3 and 2 & 5 but whenyou constrain the set for January and February you only lose record 5. I would want to lose both 2 & 5 because they aren't a duplicate within the dates - one of them falls outside. Good thought though.

                          • 10. Re: Find Duplicates within a Found set

                            I'm not sure I can achieve my desired result this way but I'll give it some thought. Thanks

                            • 11. Re: Find Duplicates within a Found set

                              Why don't you do the search in one step?


                              See attached example file. Here your example works as expected.

                              Edit: It works also with the individual steps (irrespective of order, whether you first search the range and then constrain with !, or vice-versa).


                              It might be that you have a problem with indexing. Turn indexes for the fields in question off and on.


                              Message was edited by: MartinBraendle

                              1 of 1 people found this helpful
                              • 12. Re: Find Duplicates within a Found set

                                I've tried the index switch and it only works with indexing turned off on both fields. When I turn them back on the problem reappears and the extra records are found again. Looks like I will have to go with no indexing and hope that doesn't affect performance elsewhere. Thanks for the tip though.

                                • 13. Re: Find Duplicates within a Found set

                                  Why do you want to find those records? What are you planning on doing with them after you have your found set? How many are there?


                                  You could sort by the key field, loop through, and omit the dupes.

                                  • 14. Re: Find Duplicates within a Found set

                                    If you create 2 global date fields and create a self joining releationship based on the globals and the KEY field you will have a second table that lists only the records within your requested dates. You can then create a calculation field that uses PatternCount(List(Table2::KEY);KEY) and preform finds based on this field.

                                    1 2 Previous Next