7 Replies Latest reply on Oct 26, 2010 2:44 PM by philmodjunk

    Searching for duplicates in FM11

    JeffWilson

      Title

      Searching for duplicates in FM11

      Post

      I am attempting to find records in a table where people have registered twice for an event. My find criteria are (1) a serial number in the event serial number field and (2) a ! in the contact serial number field.

      I would expect this to find records for that specific event where the contact serial number is duplicated.

      Instead, I'm getting all registrations for the event, which is impossible since there are 144 different registrants with 146 registration records. In other words, I know at the outset I have two duplicates. I just can't find them.

      What am I missing?

      Thank you in advance for your assistance3.

        • 1. Re: Searching for duplicates in FM11
          philmodjunk

          Are you doing ths by hand or in a script? If you are using a script, can you get what you want by entering find mode, then entering the criteria? (That clues us in that there's a script problem).

          If you can't get this to work with a manual find, what is the structure of your database? One table, Two related tables or ?

          If more than one table is involved, in which fields from which tables are you entering the two values? How are the tables related? and on what layout are you performing the find?

          • 2. Re: Searching for duplicates in FM11
            JeffWilson

            By hand. It's a one-time situation. I'm going to program in the future to prevent duplicates at all.

            The database has many tables, but I'm searching on a layout that's based on the table in question. Consequently, I'm not searching through a relationship.

            Layout based on table: History   Fields: History::SerNumContact and History::SerNumEvent

            Thank you.

            • 3. Re: Searching for duplicates in FM11
              philmodjunk

              That suggests that either there are no records for the event ID that have duplicate contact IDs (maybe you have duplicate contact records with different contact IDs) or you have a misbehaving index on one or more of the fields involved.

              You can rebuild the index to a single filed by using Manage | Database | Fields to turn this feature off then back on. You can rebuild all the indexes in your file by importing all your data into a clone of the file, or if you have FileMaker 11, you can use Recover with only the following advanced options selected to rebuild your file (Thanks to LaRetta for teaching me this last trick).

              Copy File Blocks as is
              Rebuild Field Indexes Now

              • 4. Re: Searching for duplicates in FM11
                JeffWilson

                If there are no records, then the find should find none, not 144.

                Thanks for the other tip.

                • 5. Re: Searching for duplicates in FM11
                  philmodjunk

                  Good point. I know this may be obvious, but it never pays to assume stuff, did you sort your records after the find so that records with duplicate values will be grouped together?

                  • 6. Re: Searching for duplicates in FM11
                    JeffWilson

                    That's the step I was trying to avoid; LOL.

                    • 7. Re: Searching for duplicates in FM11
                      philmodjunk

                      But if you don't sort your records, how could you be sure you've spotted all the duplicates for a given value--especially with more than a hundred records?

                      BTW, another way to seach for duplicate values is to use a self join that matches on the fields where you have duplicate values. It's more work to set up--especially if this is a one time fix.