4 Replies Latest reply on May 28, 2009 10:51 AM by LaRetta_1

    eliminating duplicates on import (FM6)

    webguy262

      Title

      eliminating duplicates on import (FM6)

      Post

      We are getting duplicate records when importing from a MsSQL dbase, even though I have set the import to overwrite the record if three fields match.

       

      We have a file of youth basketball players from around the country.  When we run tournaments, we get updated info in our website dbase from any players who play in the tournament.

       

      We connect to the dbase and import the player records, setting FM to match FIRST NAME, LAST NAME and ZIP.  If these match, the record should be overwritten with the imported data.

       

      But we are getting new records on some imports, even though the existing record and the import record have the same FIRST NAME, LAST NAME and ZIP.

       

      Any suggestions? 

        • 1. Re: eliminating duplicates on import (FM6)
          LaRetta_1
            

          I would first check that I am Showing All Records  before every import, since this is the most frequent culprit.  Are you sure the data is clean in both files, ie, no leading/trailing spaces, carriage returns or lower ASCII characters which might make them mismatch?

          • 2. Re: eliminating duplicates on import (FM6)
            webguy262
              

            I am just opening the player file before doing the import so I assume it is showing all records.  But I'll double check.  Obviously, what you are suggesting is that if we import into a found set of records, the matching would be done only against the found set, not the entire list, correct?

             

            When we look at the matched fields in a dupe they appear identical.  Again, first name, last name and zip code are all were matching so there's not much there that could interfere with the match making.

             

            I read that data type can cause a problem with matches.  Any advice in that area?

            • 3. Re: eliminating duplicates on import (FM6)
              davidanders
                

              leading or trailing spaces and returns are the most common problems, given your specs.

              Case sensitivity is also a concern.

               

              • 4. Re: eliminating duplicates on import (FM6)
                LaRetta_1
                  

                " the matching would be done only against the found set, not the entire list, correct?"

                 

                Correct, your import will only impact the current found set of records; omitted records (not in the current found set) will be ignored in the update and if there are any imported matching records, they will be added as new.  Is this FM file being served?  If it is not served, and you reopen it, you will still maintain the same found set as when it last closed.  I would not trust it.  It never hurts to add a Show All Records and, if you don't, you might later change that script and do something before the import which might change your found set. 

                 

                I would not be inclined to suspect data type here although it is possible, ie, incoming zip is 02374 and FM zip is number field but accepts it the first time.  Later, the FM record is modified which updates that field (maybe a perform auto-enter) and that zip will change to 2374 and no longer match.  But you say that they appear the same so I doubt that's the issue.  Case sensitivity might play a part as well, if the FM field is set to ASCII (now unicode); check Options > Storage > Default Language.  If ASCII or unicode, it IS case sensitive and would not match if different.