5 Replies Latest reply on Jun 19, 2014 10:18 AM by tonymak

    Importing from File with duplicates records

    tonymak

      I have a question on importing from a csv file.

      I have a dump from an appointment system that contains appointment records as well as the contact information (Name Address etc).

      the file contains a record for each appointment (Type, Date, etc) so naturally this file contains multiple record for each contact, it also contains records for contacts that are not yet in a contacts Table. (I'm updating two tables from this appointment file)

       

      I have no issues updating using the import routine into the appointment table as I'm checking against matched records for First, Last Name and appointment date and type, and updating those records and adding any new entries. Works fine.

       

      My problem which I can't wrap my head around is I'm using this same csv file to then add into the contacts table any New Contacts. But no matter what i do The import routine creates a contact for each instance in the appointment csv resulting in duplicate entries now into the contact file, I am testing for matched records I thought.

       

      When FileMaker does an import and if I'm testing for matches on Name, city, and what ever, does the import routine not check against newely imported records or only looks at the base found set (Show all records specified prior to import)

       

      If filemaker doesn't look at the new records to match what would the suggestion be to import into my contact table a single record and not the duplicates because of the multi matching name, address etc records from the appointment csv file?

        • 1. Re: Importing from File with duplicates records
          mikebeargie

          The match is always against the found set, so show all records before your import.

           

          I personally prefer to import all contacts into a single source, then write a script to mark duplicates in that single source. It's easy to add a "duplicate" flag field, and mark duplicates with a loop script that goes over a sorted table.

           

          Even if you are setting up some sort of recurring import, you could have the duplicate marker script run after import.

          • 2. Re: Importing from File with duplicates records
            mikebeargie

            Oh, and also, you might be importing what you think are duplicates, but in actuality have slight variations from a matching record, such as a trailing space or extra space somewhere in the text.

             

            I'm not quite sure, but your field type in filemaker might need to match the field type of the CSV as well. IE a "date" field in filemaker is not the same as a "text" field with a date value in CSV, but I'm only theorizing that could cause an issue.

            • 3. Re: Importing from File with duplicates records
              tonymak

              Yesw i do set show all prior to the import, but it is looking like filemaker import isn't adding the newly added record to the show all found set?

               

              example:

              the contact table has no record currently for John Doe, Some street, Some Town, USA

               

              but the appointment csv file has three records for John Doe

              John Doe, Some Street, Some Town, USA, 6/15/2014, Sales Appointment

              John Doe, Some Street, Some Town, USA, 6/16/2014, Follow up Appointment

              John Doe, Some Street, Some Town, USA, 6/20/2014, Install Appointment

               

              For the appointment table file maker imports all three new records perfectly. Naturally,

               

              But now i need to create a new contact record (Grabbing the Name & Address) into the contact table (Only one from those Three John Doe entries) when I then run an import script to update the Contact file to add the new John Doe to it (It wasn't there originally).

              the Import script is set to "Match  Records, then add if no match" based on First Name, Last Name, City, Country as the Matched fields.

               

              but when the import runs all three of those

              John Doe, Some Street, Some Town, USA

              records are added even though I'm testing against all those fields

               

              so I'm guessing Filemaker doesn't check the match against newly created records?

              • 4. Re: Importing from File with duplicates records
                mikebeargie

                No, it does not account for those records, as they are not part of the found set when the import is initiated.

                 

                The method I mentioned above is better for deduping later. After deduping, you could create a match to the appointment table based on the name/address, although doing some replaces to set a serial number from the contacts table into the appointments records.

                • 5. Re: Importing from File with duplicates records
                  tonymak

                  Thank you, I do get what your meaning, just now need to get familiar with finding and knowing which of the new formed duplicates to remove