Do you have these tables and relationships?
Foods::_kp_food_id = Foods_Nutrients::_kf_food_id
Nutrients::_kp_nutrient_id = Foods_Nutrients::_kf_food_id
And you've imported data from your original files into the Foods and Nutrients tables?
If so, so far so good, but what data do you have from your original files to tell you that Food A should link to Nutrients 1, 2, and 3 and that Food B should link to Nutrients 2, and 5?
Thanks for getting to me. I have 2 tables.
1. I have a foods table that I have imported with a _kp_food_id, food names etc etc.
2. I have a record table that has several fields one field is what would be the _kf_foods_id another field is what would be the _kf_nutrients_id. So this table tells me each relationship.
There are the only 2 tables I have.
I have attached an image of the second table. The image shows 3 foods and you will see that not every food has the same nutrients, there are actually over 50 nutrients. This table has all the fields that I usually expect to find in a join table. The Field 'description' and 'scale' and 'category' would actually be fields from the NUTRIENT table. '_kp_nut_content_id' and 'value' are fields specific to each related record.
There is not NUTRIENTS table so I have created one with no records. I have created a _kp_food_id field, a 'description' and 'scale' and 'category' field in the NUTRIENT table.
I have been using filemaker for 8 months but have never had this issue. Thanks for your assistance. It is greatly appreciated.
Thus, you have the data for the join table and the nutrients table all combined in a single file.
What you can do is import from this one file twice. Once into the join table to create join records with the required keys and other join specific items. Then you import the data a second time into the nutrients table.
Set up your import into nutrients like this:
define the fields for nutrients before importing. Set up a unique values, validate always validation rule on _kf_nutrients_id. Then when you import this data into nutrients, this validation rule will filter out duplicates during the import.
PS. After the imports are complete, I'd modify the design of all three tables to use auto-entered serial number fields for primary keys for all three tables in order to get better data integrity.
Had a busy few days so did not have a clear head to give it a go until today.
I did as you sugested and it worked perfectly. So simple and taught me something new.
Thanks for the suggestion.