I have two related databases that I'm trying to import Excel data into. Database A is the main (parent?) database and holds basic client information. Database B is for the products that the clients in Database A have purchased (I suppose that's similar to an invoice file). The two databases are related by a serial number key field. Normally to enter a purchase I click a button in Database A which creates a new product record in database B. I enter the appropriate purchase data and then click a "Done" button which brings me back to database A. The new purchase record is then listed in a portal on the clients record. Here's the problem:

I have to import a bunch of records from an Excel spreadsheet. The problem is that all puchase information (what would go into database is on one row along with the clients name, address, etc.

So, for example, I have multiple rows of "John Smith" in the Excel file each with a different product purchased. If I import from this worksheet, I then get multiple "John Smiths" even though he is actually just one person. What I need to do somehow import each client only once and then import each product he/she has purchased. Obviously I need to keep the client and the products related somehow.

I've been pondering this for quite some time and I just can't get my grey matter wrapped around the problem.

Anyone have any ideas?

Do all of the names in the excel file currently exist in the parent database? If so, you only need to import into the sales file. If not, you will also need to import into the parent file to create the new customer records. You will then have to delete the duplicates that may be created. There is an example script in the Filemaker user manual (page 10-13) that does this.

Now after saying all this, I hope you have a unique customer ID field in the excel file that matches the customer ID in the database. If not, you have to pray that you don't have different customers with the same name (two different John Smiths).