1 Reply Latest reply on Apr 4, 2011 1:50 PM by philmodjunk

    Looking for selective import script

    WalterVMorsan

      Title

      Looking for selective import script

      Post

      Hello,

      I need to selectively import a sizable number of names and addresses  from an Excel spreadsheet into our company contact database, which is in  Filemaker.
      Some names and addresses already exist in the database, others do not.

      The proposed methodology is to convert/import the Excel document into  Filemaker, and then do the import using a script. Since my scripting  expertise in Filemaker is somewhat limited, I'm hoping someone has or  could develop a script that would do the following:

      Given a record in the source database (the converted Excel spreadsheet),
      1) copy the "LastName, FirstName"
      2) go to the target database -- the company contacts
      3) perform a find using the copied data ("LastName, FirstName") on an analogous calculated field
      4) If there is a match, show result "Contact already exists", while  displaying found record(s) to allow visual verification of address  details
      5) If there is no match, show result "No contact found. Import record"  and import matching fields from source database to target database,  again, displaying imported record for visual verification by operator.

      This will be an ongoing process that will need to be repeated from time to time as new contacts accumulate in Excel.

      I would be grateful for a response. Also, if there's a different way to accomplish this, I'd appreciate any suggestions.

      Vlad Morosan
      www.musicarussica.com
      The World-wide source for Russian Choral Music

        • 1. Re: Looking for selective import script
          philmodjunk

          You should not have to convert the excel file to FileMaker, you can import directly from and excel file into a table in your file.

          Instead of perfoming a find one record at a time, you can define a relationship between your two tables:

          contacts::FirstName = ImportTable::FirstName AND
          contacts::LastName = ImportTable::LastName

          You can then add as many fields as you need to see and work with from Contacts to your ImportTable layout. If there is a match between the record in ImportTable and a record in ImportTable, these fields will show the contact data from the matching contact record. If you enter Find Mode and enter an * in the contacts::LastName field on this layout, you'll be able to pull up a found set of all records where the first and last names match so that you can edit and evaluate them.

          Once you have finished that part of the process, you can use Import Records to Import all or just some of the Record in you ImportTable into contacts. THere are a lot of options to choose from when importing records, so read up on the process in FileMaker help and make frequent back up copies so that you can start over if you don't get the results you expected.

          Note, there are also more sophisticated relationships and/or a filtered portal that can be set up to search for matching contacts where the names aren't quite identical such as two forms of the same first name (Phil, Phillip, Philip, for example).