1 2 Previous Next 17 Replies Latest reply on Jul 10, 2017 7:19 AM by makerbert

    Importing Records into Related Tables


      I'm reworking a solution I originally created as a flat file. I've now correctly redesigned  the solution with multiple related tables with corresponding join tables where needed.


      I'm at the point now where I'd like to begin importing records from the original flat file solution, but I'm unsure how to properly populate the related join tables.  From what I've read, I realize that I need to have a layout for each child table and will need to begin the import process from each child layout.


      I had planned on creating a temporary matching key for the separate parent and child records import, but with the join table in the mix it gets more complicated.


      Any suggestions?

        • 1. Re: Importing Records into Related Tables

          converting from flat file to relational database means that you'll have to create primary keys values for each entity row and back fill any foreign keys with the right values.

          Its not easy nor quick.

          • 2. Re: Importing Records into Related Tables

            I have done this by temporarily importing your flat file into a table in the redesigned database. In most cases working with organized flat data the ExecuteSQL calc works well to get the data needed for the records.


            In its simplest form I would assign a UUID to each record in the flat table. Then assign a variable $$LIST to ExecuteSQL ("SELECT \"UUID\" FROM \"FlatTable\""; ""; "").


            Then loop though the list of values using ExecuteSQL() to assign data to variables to use in the record creation.



            $i = iteration of the list

            $value = GetValue ($$LIST, $i)

            SetVariable [ $data1, value: ExecuteSQL("SELECT data1 FROM FlatFile WHERE uuid = ?"; ""; ""; $value)

            Then set proper field to $data1.

            You can do some variations of this method but the idea is the same.


            Have a special layout for this in your join table that will allow creation of records in the other tables. All fields need to be on the layout for all the related tables. Once you work through creating the script it is as simple as running it to bring everything over. All of the keys will be auto-enter and handle themselves.


            I am sure you might get some other suggestions, but I have found this to work quite well. The flat table can be deleted after import.

            • 3. Re: Importing Records into Related Tables

              Of course you need to set the parent records first so the keys carry through.

              • 4. Re: Importing Records into Related Tables

                Thanks for the response Tom. Unfortunately, I'm pretty new to FMP and those instructions flew right over my head. While I'm learning more about this powerful application, I'm still pretty ignorant to the more advanced concepts.


                TBH, I'm kinda surprised there isn't a standard method of accomplishing this type of import. Seems that importing related records in a many-to-many relationship would be quite common. The alternative would be to manually import them I suppose?

                • 5. Re: Importing Records into Related Tables

                  I understand the problem of stuff going over your head; I have less of that these days but I vividly remember the stage of my development when it was more common than not. So here is another take:


                  First, I assume you have set up in your new file a proper set of ID fields that you will use to drive your new relational database (if not, then do it now before going any further!). My advice is, don't populate these fields with any old data. Instead, create a new field in each table where it is relevant called, say, importID. You need to set up a set of temporary relationships between each of your new tables, using the importID field and use this set of relationships as described below.


                  Second, bigtom's idea of an import table within your new DB is a good one. This should contain a matching set of fields to your old flat file—just the recordID field and the basic data fields, not calc and so forth. If you have FMAdvanced you will be able to simply import the table from your old file and then delete any fields you don't need. With this table properly set up it is a simple matter to import the data using matching names in your field map. Once you have all the records imported your old flat file is no longer needed.


                  Now for the serious bit. The data that is now in your import table will now need to be transferred to various tables in your new file. You will complete this process by a series of imports—from the import table, as the source table in each case, to each of the new tables. It is most important to import the old ID field into the importID field IN EVERY TABLE. As you import, the new records in each table, including their new recordID (which you will of course have set to be auto entered).


                  What you now need to do is get the new recordIDs into the foreign key fields in each table so that the new set of relationships work properly. You do this using the relationships based upon the importID field. You could use Replace Field Contents, but I would prefer a looping script that goes through all records in each table and sets the foreign key field to its rightful value. Once this has been successfully completed in all tables and you can see all the related tables showing up correctly the importID fields and relationships are no longer needed, and can be discarded if you wish—this is why I described them as temporary.


                  If you need any further help following this process just ask, but hopefully it will put you on the path to success.

                  • 6. Re: Importing Records into Related Tables

                    Thanks for the detailed reply keywords, I'll work my way through it and let you know how it goes.


                    I'm very thankful for all the help available here. Cheers

                    • 7. Re: Importing Records into Related Tables

                      Ok, I've worked through your guidance, but I could use a little help. I'd like to follow the advice regarding the use of a loop script to set the foreign key fields in my child/join tables.


                      I have a general understanding of scripting but have never created one for this purpose. Can you provide an example of such a script? I'll then break it down to learn how it accomplishes the above goal and put it to use.

                      • 8. Re: Importing Records into Related Tables

                        I am sure Keywords will make this more clear, but you start with looping through your parent records. You store the ID of the parent in a variable. You identify the child records by doing a search for the identifier that connects them. Then loop through the found children and set the fkID field to the parent ID. You need a further script to manage the children of children.


                        Something like this:



                             Find parent ID


                                  Find related children by identifier

                                  Set ID in Children

                                  Go To Record [Next : Exit after last]

                             End Loop

                             Go To Record [Next : Exit after last]

                        End Loop



                        The advice I originally gave handles all of this automatically through relationships and portals on one layout with one loop. About the same amount of work, you just pay for it with time spent in different parts of the process.

                        • 9. Re: Importing Records into Related Tables

                          Once you have the importID in each record in each table, and a relationship created between tables using this key, then that relationship can be used to see data in other fields in the related table—on a parent record you would be able to see, in a portal, any child records; on a child record you would be able to see its parent, including the permanent primary ID field (pk_recordID). You can use this relationship to add the permanent ID to the child record's permanent foreign key field (fk_parentID). A simple script would operate as follows:

                          1. Go to Layout [ “childRecordLayout” ] // important, to establish the correct context for the rest of the script

                          2. Show All Records // could add Go to First Record here, but that will be the default anyway

                          3. Loop

                          4.     Set Field [ childTable::fk_parentID; parentTable::pk_recordID ]

                          5.     Go to Record/Request/Page [ Next; Exit after last ] // repeat through the full set of child records

                          6. End Loop

                          7. Exit Script [ ]


                          Run the above with just one child table first, to make sure it works. Then, if you have several child tables you can duplicate steps 1–6, change the name of the layout in line 1 and the field names in line 4, as many times as needed, so that all child records have their permanent parent ID added with a single running of the script.

                          Once that is done, check to make sure all the permanent relationships are working as they should. The temporary ID field, the import table, and this script have done their job and can be discarded.
                          • 10. Re: Importing Records into Related Tables

                            keywords wrote:


                            2. Show All Records // could add Go to First Record here, but that will be the default anyway

                            Is this assuming all the child records have the same parent? Maybe I missed something.

                            • 11. Re: Importing Records into Related Tables

                              No. Each child record will see the parent ID that it is currently related to through the temporary importID relationship.

                              • 12. Re: Importing Records into Related Tables

                                keywords wrote:


                                No. Each child record will see the parent ID that it is currently related to through the temporary importID relationship.

                                I did miss that part.

                                • 13. Re: Importing Records into Related Tables

                                  So, I'm now beginning to import the records from my flat file after getting this new FM file all set up. I successfully implemented keywords' suggestions and script to import the data into the one-to-many relationships. It took some time to break this all down and digest it, but the this import process make sense now.


                                  But, I'm at another road block so to speak. I have a number of many-to-many relationships with their corresponding join tables in this file as well. After thinking it through the best I can, I cannot figure out how to go about importing the related records from my flat file into these tables while also getting the related foreign keys in the join tables to populate too.


                                  To visualize, I have many Facilities (parent) which have multiple Addresses (parent) and these are related via a Facilities_Addresses join table (child). Importing into the Addresses table isn't an issue using the above procedures, but auto-populating the foreign keys within the join table to set up the relationships is giving me trouble.


                                  Any suggestions to get me going in the right direction?

                                  • 14. Re: Importing Records into Related Tables

                                    Without seeing your data it's hard to say exactly how you should go about this. To help make sure you understand how a join table should work I attach a small demo file. The question is, how have you made those relationships so far? Once you have your join table set up properly, you should be able to devise a similar method to script creation of appropriate join records from your imported data. It may be a two-step process—set it up with the temporaryID fields first, then import the permanent IDs from each parent table—but it should be possible if the data is there and useable.

                                    1 2 Previous Next