5 Replies Latest reply on Nov 1, 2010 2:26 PM by philmodjunk

    Duplicate Records Not Needed

    BernieGrice

      Title

      Duplicate Records Not Needed

      Post

      New to the FM program and would like to mark/flag/delete all duplicate records.  

      Brief rundown:  A large database of names and addresses with many duplicates, and I only need one entry per.  I've created a new field "BuyerCode" and placed FName, MIntial, LName and address (street only), using a calculation under Manage Database.  Then I used the "!" operator to find unique in the "BuyerCode" field, but some records/names are not represented in the resulting find.

      Thank you in advance.

        • 1. Re: Duplicate Records Not Needed
          philmodjunk

          Did you sort your found records by "BuyerCode" so that the duplicate records are grouped by duplicate value?

          • 2. Re: Duplicate Records Not Needed
            BernieGrice

            Appreciate the fast response Phil.  I performed the find "!" on "BuyerCode", viewed the Show Omitted Only from the Records menu, then sorted on "BuyerCode".  Most appear to be correct but I can identify several which are not in the list.

            • 3. Re: Duplicate Records Not Needed
              philmodjunk

              Two things I can think of to check:

              1. Is your calcuation defined to return text or number? (sounds like it should be text).
              2. Any chance the duplicates that weren't found aren't really duplicates? Perhaps one of the felds has an invisible character in it such as a space or return and this is keeping the value of BuyerCode from being a duplicate like it appears to be.
              • 4. Re: Duplicate Records Not Needed
                BernieGrice

                Again, fast, thanks.

                In response:

                1.  Defined as text.

                2.  Possible, but does not appear to be.  When I review the missing from the full database, one of known missing, they all seem to identical.  After sorting the full database on the BuyerCode, they all appear together as suspected.

                Do you have another suggestion on accomplishing this task?

                • 5. Re: Duplicate Records Not Needed
                  philmodjunk

                  They might still sort together, you'd need to click into BuyerCode and see if the cursor can be located one or more spaces to the right of the last character or if the field pops out to show it has more than one line of text (if there's a return in the field.)

                  You could re-index all the fields involved to see if that makes a difference. You can re-index a single field by turning indexing off, then back on using Manage | Database | Fields.

                  You can also re-index all your fields by saving a clone of your database and then importing all your data from all your tables into the clone.

                  If you are using FileMaker 11, you can also use Advanced Recovery options: Copy file blocks as is, Rebuild Field Indexes/Now to rebuild all the indexes in your file. (Learned that one here in the forum from LaRetta.)