1 Reply Latest reply on Sep 27, 2012 2:36 PM by philmodjunk

    Excel Import: How to match on parent table's fields to import new records into related table

    NithyaLakshmanan

      Title

      Excel Import: How to match on parent table's fields to import new records into related table

      Post

           I have 2 tables defined in Filemaker: Customers and Invoices.

           I have an Excel sheet with fields: Customer Name, Invoice #, Invoice Amount. 

           To import this Excel file, I want to validate first that Customer Name matches an existing record in parent Customer table. And only if there is a match, then create a new record in Invoices table with the Customer Name, Invoice #, Invoice Amount. How can I set this up?
           Still learning Filemaker. In the Excel Import setup window in Filemaker, I can select ONLY one table - both for matching and for record creation/update. In my scenario, the match is on the parent table's fields while the records are being created in related table. How can I configure this? Also, is there a way to log back into Excel which records did NOT find a match.

        • 1. Re: Excel Import: How to match on parent table's fields to import new records into related table
          philmodjunk

               If you can establish an ODBC connection to your excel file, that may be the simplest way to go. I haven't tried an ODBC link to Excel, however.

               Another option is to import your data into a new, temporary table and define a relationship from it to your Customers table that matches by customer name. You can then import the data from Excel to the temp table, perform a find to find only those records that match to a customers record and then import that found set into the final table.

               Sending data back to an existing Excel file, unless the right type of ODBC link can be set up, would be difficult to do. It might be possible to output the data to a text or excel file that can then be imported into the Excel file via a Macro in the Excel file.