2 Replies Latest reply on Feb 9, 2014 2:03 PM by keywords

    How to import and update/extend to a table?

      Hi,

       

      I wonder what is the best strategy to import data (like contacts) to a certain Table "myContacts" from an external data base. The issues are

       

      1. that already existing contacts should get updated.
        And I know that there is an import option to do so if (and yes there is!) a key field can be used to identify records in the internal as in the to-be-imported-external table.

      2. that new contacts in the external data table should be added.
        And my understanding is that the import-features do not support that.

       

      How can I update and add contacts in the target-table in my filemaker database best? Did I overlook a built-in feature?

       

      Or do I have to write a script. If yes, how should it work? I would consider the following:

       

      • import to a new, empty copy of the target table
      • loop through this table record by record
        • check if the record is already present in the target table
          • id yes: update fields of the existing record
          • of not: create a new record and insert the data to it

       

      If this is the way - is there a ggod way to to the upset or insert of all the fields of the source record? Or do I have to implement that for each field?

       

      Thanks for advice, Luns

        • 1. Re: How to import and update/extend to a table?
          siplus

          Don't see the problem.

           

          Screen Shot 2014-02-08 at 14.31.19.jpg

          • 2. Re: How to import and update/extend to a table?
            keywords

            siplus has pointed you to the import setting to use. An issue you may have to address is how the imported data is matched to existing data. The standard way of matching within your database via an ID field may not be possible when bringing in external data, so you will have to set up another means of comparing imported with existing data. A common way is to set up a temporary key matching both first and last name, which will cover 90% of the records, but will break if you have two contacts called John Smith to import, or one to import and a different one with the same name already existing, etc. Adding an address field to the match might get over this but could still break if you come across a mother and daughter living at the same address and with the same name. All of this can be overcome, but you need to account for it and develop ways to avoid mixing records up, thus giving you dodgy data.