7 Replies Latest reply on Dec 21, 2016 4:20 AM by taylorsharpe

    Problem with Finding Duplicate Records

    ericellis9

      Hi there...

       

      I am importing records into FMP from another source to find duplicates from that source.  The import script is attached.

       

      Once the data is imported, I perform a find on a calculated field called C_Full_Name  (see also attached).

       

      This is the strange behavior:

       

      When I perform a duplicate find on the "C_Full_Name" field, I get duplicates.  For example, it tells me there is a duplicate for the name "John Daly" (see attached).

       

      However, if I then search on the "C_Full_Name" field for "John Daly", it only finds one record, not the two I expected.

       

      Any thoughts?

       

      Here is my system info:

       

      CLIENT INFO:

       

      FMP 13 Ver 13.0v4 (11-13-2014)

       

      Running on:

      Multiple MAC Computers, all running OS X (Ver 10.8.5)

       

      SERVER INFO:

       

      FMP 13 Server (Admin Console version: 13.0v9 x64 (01-09-2015)

       

      Running on: 

      Mac mini (Late 2012)

      OS: OS X (Ver 10.8.5)

      Processor: 2.6 Ghz Intyel Core i7

      Memory: 16GB 1600 MHz DDR3

        • 1. Re: Problem with Finding Duplicate Records
          philmodjunk

          "When I perform a duplicate find on the "C_Full_Name" field, I get duplicates.  For example, it tells me there is a duplicate for the name "John Daly" (see attached).

           

          However, if I then search on the "C_Full_Name" field for "John Daly", it only finds one record, not the two I expected."

           

          I suggest describing how you did those two findsstep by step. Your screenshot clearly shows two records with the same name. So it is not clear why you would need to perform a second find.

          • 2. Re: Problem with Finding Duplicate Records
            ericellis9

            Hi there...

             

            A little more info on this.  So FMP is telling me I have a duplicate record.  When I go to the source software to search for the duplicate record, I do not find it.

             

            So I was playing around in FMP to see if the problem might be there.  That is why I was testing the duplicate by doing a find on the "C_Full_Name" field, just to make sure the duplicate record was there.  That is when I found the inconsistency.

             

            Again, when I do a find using the duplicate feature ("!") on the "C_Full_Name", it says it finds a duplicate for "John Daly" (and displays two records).  But when I actually do a find in that same field, entering in the "John Daly" text, I expected to find two records, but it only finds one.

             

            Doesn't that seem strange?

             

            One more point - I originally did not h ave a Serial Number field on the table that I was importing into, but decided to create one just so I could be sure there were actually two different records when I did the duplicate search.  However, I can not seem to get the new SN field to populate.  I am running the import script from the server side (see original attachment for the import script) but can not see how to tell it to fill in the SN field as it imports the records.

            • 3. Re: Problem with Finding Duplicate Records
              philmodjunk
              But when I actually do a find in that same field, entering in the "John Daly" text, I expected to find two records, but it only finds one.

              so this is a find on the field in the original table?

               

              I'm not sure about ODBC importing, but regular imports pop up a little dialog asking if you want to enable auto-enter options. If that is also the case for importing from an ODBC source, that option should allow an auto-entered serial number to populate as part of the import.

               

              And as I recall, an ODBC import can specify an SQL query in order to control what data is imported. If so, I wonder if there is some issue in the SQL that is generating the duplicate record--such as a join that matches to the same record with that name for two different records on the other side of the join.

               

              And this is pretty obvious, but presumably you don't have this happening because "John Daly" was already in a record and then you do an import that adds it in again?

              • 4. Re: Problem with Finding Duplicate Records
                taylorsharpe

                Is it possible you are searching for a field in a related table to the layout you are in?  That will cause this to happen sometimes.  It is possible, but unlikely, that your index has become corrupted.  You can unindex it, then turn indexing back on to see if that works.  Or you can try to recover the database which will rebuild all indexes.

                 

                EDIT:  My bad.... I  didn't realize the datasource was external.  What happens if you do a SQL search for it via ExecuteSQL?  Can you import using DISTINCT?  Or import using that as a matching field to eliminate duplicates?

                • 5. Re: Problem with Finding Duplicate Records
                  ericellis9

                  OK, I finally figured it out.  It was an issue of bad indexes (I think).  The clue was that I was able to run the import script locally and use the option to create serial numbers.  When I then did a find on duplicates, I noticed the records it was finding did not have any serial numbers.  However, the total count of the table did not change, so it was almost as if there were "ghost" records still there.  To confirm the issue, I deleted one of the found records that had no SN and then ran the find duplicate process again and that record did not show up as a duplicate.

                   

                  I then went into the table and turned off all of the indexes for each field, deleted all the records, then imported new data in and it seemed to fix it.

                  • 6. Re: Problem with Finding Duplicate Records
                    ericellis9

                    Thanks for all your help!

                    • 7. Re: Problem with Finding Duplicate Records
                      taylorsharpe

                      Glad you figured it out.  It is pretty rare that indexes go bad, but I've seen it happen.  It seems to happen more often on non-Server hosted files.