Help on scripting for importing a flat file into a simple relational database
Hi... I'm a new FileMaker user finding my way here and I'd greatly appreciate some help on what (I hope) to be a simple scripting problem. See the attached JPG. In this example, I have an Excel table listing products made by different companies for different markets. I want to import this into a relational database, also shown in the image. I can import the file into the "Product" table easily enough (additional fields would be hand-entered later), but I want to create records for the "Company" and "Market" tables in the process. Specifically, I want to create a new record in the "Company" table only if that company doesn't already have a record... and regardless, I want to link from the "Product" table to the "Company" table after the import. Likewise for the "Market" table.
So, for this example, following importing the file into the "Product" table, I should end up with:
- 4 records in the "Product" table as in the file
- 2 records in the "Company" table (ACME, WidgetCo)
- 3 records in the "Market" table (automotive, aerospace, marine)
- All fields in all records not specified in the import file would, of course, be left blank
If someone could teach me the basics here, I would really appreciate the help.... Thanks in advance!