4 Replies Latest reply on Mar 18, 2012 5:51 PM by brettpix

    Create kp in a new table from imported join table kf

    brettpix

      Title

      Create kp in a new table from imported join table kf

      Post

      I have imported a database. In essence there were two files. One (foods.txt) was one side of a many to many relationship (foods - nutrients), the other file (nut_content.txt) was the individual records of what would be the join table. From these two files I want to create the 'nutrients' table (the other table of this many to many relatipnship). I have assigned _kf_food_id and _kf_nutrient_id in join table. I have created new NUTRIENT table and field _kp_nutrient_id. How can I extract the _kp_nutrient_id form the _kf_nutrient_id of the join table? Got me beat, is this really simple and I can not see it? Been trying for hours.

        • 1. Re: Create kp in a new table from imported join table kf
          philmodjunk

          Do you have these tables and relationships?

          Foods---<Foods_Nutrients>----Nutrients

          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?

          • 2. Re: Create kp in a new table from imported join table kf
            brettpix

            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.

             

             

            • 3. Re: Create kp in a new table from imported join table kf
              philmodjunk

              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.

              • 4. Re: Create kp in a new table from imported join table kf
                brettpix

                Thanks Phil

                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.