4 Replies Latest reply on Aug 1, 2014 4:49 PM by FentonJones

    Import data into two tables



      Import data into two tables


           Newbie here!

           I have a simple database with two tables (members and trees) that are joined by primary key & foreign key. I need to import a file of members, each having from 1 to 5 trees. I can import data into one of the tables but not both.

           How do I make sure the trees are connected to the right members. FM can generate a unique primary key automatically. How do i get the foreign key to match the primary key?

           Thank you

        • 1. Re: Import data into two tables

               What data in your file from which you will import data uniquely identifies each member?

               How is the data structured in that file? Is all the tree and member data all in the same row?

          • 2. Re: Import data into two tables

                 Hi Phil:

                 I have two options for unique identifiers, 1) current records all have a unique ID#, 2) Each record has owner's full name

                 As for he structure of the data yes all the information is on one long row. Name of the owner plus say 5 trees and tree details are listed as

                 First_Name, Last_Name, Tree1_Type, Tree1_Size, Tree1_Location......followed by Tree2_Type, Tree2_Size, Tree2_Location

            • 3. Re: Import data into two tables

                   But is there a unique ID# in the File from which you will import this data?

                   You have more issues here than that. From what I see, you will need to import the data into a separate table and then run a script with a pair of nested loops to loop through both the records and the columns in order to create new records in your table of Trees data and also link them to the correct members record. I am assuming that each cluster of 3 columns for each tree needs to be recorded as a separate record in your Trees table.

              • 4. Re: Import data into two tables


                     Either a "unique id" or the "full name" (if unique) will work (in that order of preference).
                     I see another way, not using a Loop. It could also be done with 6 Imports steps (in one script, or separately).
                     1. Import "members" (Members table)
                     a. unique ID (change to auto-enter Serial ID after)
                     b. person's name
                     2. Import "tree 1" (Trees table)*
                     a. unique ID
                     b. Tree1_Type to Tree_Type
                     c. Tree1_Size to Tree_Size
                     d. etc.
                     3. Import "tree 2" (Trees table)
                     a. unique ID
                     b. Tree2_Type to Tree_Type
                     c. Tree2_Size to Tree_Size
                     d. etc.
                     4., 5., 6. Import "tree 3, 4, 5
                     Do a Find for all Tree table records with NO data in critical Tree field (like name of tree?); i.e., blank records. Delete All Records of Found Set.
                     Do a Find of the above within the original file, so that each has a found set of only those needed (such as all with Tree2, then, for next Import, all of Tree 3 (using Constrain Found Set is faster, as each finds less). 
                     * When I said "with one script" I assumed you already HAVE the "unique ID" in your existing file/table (with "members" AND all 5 "trees").
                     If NOT, then you'd do just the Import of "Members". Then, going to your original data (after opening it with FileMaker), and Import the "unique ID". It will work as it is the SAME for both Members records/rows/lines.
                     Then you could do 2., 3., 4., etc. Imports into a Tree table.
                     You could just use the "full name" (if unique). In which case all Imports could happen as same script, since the connect is already there. However, you'd want to switch that to using a unique Member ID later. [Hence either more work later, rather than using ID now.]
                     Basically, with bringing this fairly simple data into FileMaker it is mostly about getting the ID in place, and understanding what to Import into what table.
                     P.S. I almost always create a script for the Import step. Because it is much the same as a manually Import, but it SAVES it. Then you can go do it later, when you're (surely) ready.
                     I may have not written this real well. But I've done the above many times. Phil's method also. If you post again I'm sure either of us could help you with either method.