Imports have to be one table at a time. Sometimes, to get the results that you want, you have to import into table C, then use a script to parse the imported data from C into tables A and B.
that's helpful, thank you. do you happen to have a sample script? (writing scripts definitely isnt my strong point!)
Without a few more questions answered, a sample script stands a good chance of being a poor "fit" to your needs.
Will you be importing the same file from the same location repeatedly? Or is this a one time thing to get your new database started with the current data?
Is there a column in the imported data that uniquely identifies the company? (A company name field will work for this provided that company names are always unique.)
It will be for regular updates to add new contacts in bulk rather than manually one by one. There isnt a unique code but i could add in a column which corresponds with the company code in the database? Each person does have a company name but there may be instances where 2 individuals are from the same company..Their email address is unique to each one. I assume that if individuals are from the same company, they can't be imported as 2 delegates into the same company record?
As long as you don't have two companies with the same name (this happens more often than you might think), we can work with the company name field. We can use the company name field in an added relationship to get to the company ID code for that company via some simple scripting.
My example assumes that the excel file always has the same name and is always placed in the same folder. A more sophisticated script can avoid that requirement but I'm trying to keep this example as simple as possible.
I am assuming these table names, field names and relationships:
Company::__pkCompanyID = Delegates::_fkCompanyID
You'll need to substitute the actual names for mine to get it to work in your database.
First, Open Manage | Database | Fields and specfiy "Unique Values", "validate always" as validation field options for the CompanyName field. This will be key so that you can import data into the Companies table to add New Companies without generating duplicates.
Next, Make a new Table Occurrence of Company and link it by companyName to a CompanyName field in Delegates. (You'll probably have to define a CompanyName field in Delegates.) You won't need to put this field on any layout or use it any where but for this purpose.
This will produce relationships such as this:
Companies|ByName::CompanyName = Delegates::CompanyName
This relationship will let us use the CompanyName to get to the corresponding value of __pkCompanyID.
Now the script:
Import Records [----> specify the name/location of your excel file here, import data into Companies only mapping those fields that contain company data]
Go to Layout ["Delegates" (Delegates) ]
Import Records [--->Import into Delegates table, only map to the CompanyName field plus those fields specific to a given Delegate record]
Replace Field Contents [ no dialog ; Delegates::_fkCompanyID ; Companies|ByName::__pkCompanyID ]
Sorry for the delayed silence, i have only just got round to testing this. Thank you so much for your help, reall yappreciated. I am currently stuck on the script step: Import Records [--->Import into Delegates table, only map to the CompanyName field plus those fields specific to a given Delegate record]
How do i import into the delegates table? i only have the option to import and then select file or folder path (not filemaker table)
Keep in mind that this is a SCRIPT. If you manually import records, you have to first select a layout based on the desired target table. With a script step, you select the table in the import field mapping dialog after adding the import records step to your script.
ok go it. thank you again.. seems to be working now (after a little tweeking!). really appreciate your help.