To import data into multiple tables, you just use one import records operation for each table in Filemaker into which you want to import data. If you are selecting Import Records from the file menu to do this, first select a layout based on the table into which you intend to import data.
This assumes that your data to be imported already contains values for Customer_ID and Product_ID that uniquely identify each customer and each product.
First of all Filemaker can import csv files, so you don't have to import to excel. Filemaker can import into multiple tables. When you click import in filemaker a import dialog box will appear at the top left is a drop down where you select the source file and then on the top right there is another drop down where you select the target table. You can use a script to import but I don't see that it will make anything easier. Even in a script you would have to select the source and target files. Goto your customer layout to import customers, then goto your subscription layout to import subscriptions and so on.
Thanks for the feedback here - it's very much appreciated.
The first option is slightly confusing to me as I have 8000+ customers with about 100 different product subscriptions. Not too sure how create the correct CSV file to do this (although I do understand the concept of having to provide ID numbers). My confusion is how to deal with CustomerA having Subscription A,B,C whilst Custmer B has Subscription A,C and so on. Don't understand why I have to the relational sort?
As for second option I cannot choose multiple tables.
Any more pointers?
It depends on how your data is structured. If it is structured like this:
Customer A Subscription A
Customer A Subscription B
Customer A Subscription D
Customer B Subscription B
Customer B Subscription C
Then you would import once into a customer table using a unique value validation rule to filter out duplicate instances of the customer info. Then you would import once into your subscriptions table.
But if you have each customer listed once with each subscription in a different column, then your import process, while still quite possible will be more complex--you might need to import into a temporary table and use a script to move the subscription data into the subscriptions table.
PhilModJunk - thank you for your reply.
Unfortunately the CSV file I have is laid out as described in your second description.
My initial post tried to describe the same situation. I had the data in a table and needed advice/help as to how to script a process that would populate my relational tables. I am aware I obvioulsy need to do some more reading.
Does anybody know if this resource covers scripting in depth?
If not, where is the best online resource for scripting?
If you want, I can tell you how to construct a script such that you import your data into a temp file and then a script with two nested loops moves the data from your columns of data into separate related records that correctly link to your customer table.
On further review of this thread you mentioned a unique identifier field. Does your CSV file contain a column where the data in it uniquely identifies each row of data in the file? If so, we can skip the need for this table and script.
No. But I can easily add one in Excel and import from there; yes?
Then what you can do is make repeated imports into the Subscriptions table, specifying the ID column each time, but then mapping just one of the subscription columns in each of the imports. This will build your needed related data in the subscriptions table.
Add another import of the customer data (and ID field) into the customer table and you'll have all your data imported and linked.
You can set up a script to do this so that yo perform one script and a series of Import Records script steps pulls all the data into your two tables.
After Import you can update the ID number field in customers to be an auto-entered serial number field. Just be careful to specify a next serial value that is larger than the largest imported ID number.