7 Replies Latest reply on Nov 13, 2009 1:37 AM by bodhibrian

    Finding Duplicate Records Except First Instance Help



      Finding Duplicate Records Except First Instance Help


      Hi Everyone.

      Thank you so much for your time and energy with your help.


      FMP 10

      Mac OSX 10.6.2

      Beginning Level User to FMP & databases in general

      Database size is over 10 Million Records


      The Problem:

      One of the fields in my database is an email field.

      I know there are quite a few duplicates as I put in "!" in the Email field in Find Mode.

      I want to identify and then remove the duplicate (as defined by having the same email) records from my database.

      I have followed the 7 steps as outlined in the FileMake Pro Help, "To find duplicate records except the first instance" but I must be doing something wrong.

      Once I am done, in the Check Duplicates field every record has the word "Unique" in that field instead of them separating with some "Unique" and some "Duplicate"


      Please advise....thank you. 

        • 1. Re: Finding Duplicate Records Except First Instance Help
             You haven't followed the instructions correctly. You should have a self-join, and your calculation should be referencing a field from one side of the relationship, and comparing it to the same field from the other table occurrence.
          • 2. Re: Finding Duplicate Records Except First Instance Help

            Hi there.

            Thanks for helping Hiatts.


            I have already created the self-join relationship from the email field to itself in the relationships area.

            And I have inputed this calculation for the Check Duplicates Field:

            If (Counter = Same::Counter, "Unique", "Duplicate")  where Same is the name of my first table.



            • 3. Re: Finding Duplicate Records Except First Instance Help
                 You could print your script to a PDF, copy the text and then paste it here for us to see. We might just spot a detail you missed that explains the issue.
              • 4. Re: Finding Duplicate Records Except First Instance Help

                Hi Phil.


                Thanks for popping in.


                I did not use a script.

                I only followed the directions on the FMP 10 Help Window pasted below.


                I think I may have solved the problem.

                I changed the name of "Same" in the Calculation line (or "table1" in the below paste) to the name of the second table (the one that was created during the self-join) instead of the name of the primary/first table.


                Does that seem right to you guys? 



                If you plan to delete the duplicate records that you find, make a backup copy of the file.
                For more information, see Saving and copying files.
                Identify a field that determines a unique entity in your file.
                For example, in a Contacts database, the Last Name field is probably not a good choice, because you might have several people with the same last name. Social Security Number is a better choice. You can also create a calculation field (returning a text result) that combines data in several fields to make a unique identifier. An example formula is First Name & Last Name & Phone Number.
                Define a self-join relationship.
                Use your chosen identifying field as the match field in both tables in the relationship. For more information, see About self-joining relationships.
                The primary record is the first matching record according to the sort orderdefined in the relationship.
                Define two fields:
                Counter, a text field with an auto-entered serial number (select Serial number and accept the default values for Next and Increment by).
                Check Duplicates, a calculation field with a text result, with the formula:
                If(Counter = table1::Counter, "Unique", "Duplicate")
                Click Show All in the status toolbar.
                Click the new Counter field, choose Records menu > Replace Field Contents, and Replace with serial numbers. Again, accept the default values. Select Update serial number in Entry Options, and clickReplace.
                This will assign a serial number to all existing records in your database. Serial numbers will automatically be entered in new records.
                Perform a find for Duplicate in the Check Duplicates field.
                The first record in any series of duplicates now holds the value “Unique” in the Check Duplicates field, and all duplicate records within the same series are marked “Duplicate”.




                • 5. Re: Finding Duplicate Records Except First Instance Help

                  Did you add the auto enter serial number?


                  And if you already have data, did you force the SerialNo field to populate itself? 

                  • 6. Re: Finding Duplicate Records Except First Instance Help

                    ok on the table where you are adding the calculation you will have the serialno field... and it must have values... and a calculated field


                    on the relationships tab you will have two occurrences of this table... call it 1 and the other 2.


                    in the table (not occurrence) in the "Duplicate" field where you place the calculation... enter the specify calculation screen.


                    The first thing on this screen, from the top, is "Evaluate this calculation in context of: ...." make sure it says table 1 (the first occurrence).


                    Click the "Current Table" combo box and find related tables.. and select Table 2.


                    Now build your formula if ( field = table2::field ; ....)


                    the first part of the if is going to be defaulting to Table 1, you dont need to spell the table out, you can if you wish, it will just remove it if it doesnt need it. 



                    • 7. Re: Finding Duplicate Records Except First Instance Help

                      Great...thanks so much for your help.


                      I think I've got it.  :)