8 Replies Latest reply on Feb 1, 2013 10:55 AM by RealNameArcher

    Importing into related tables



      Importing into related tables


           I have developed a FM relational database with 4 related tables. I am importing information into each table from a flat FM file with over 3000 records. The foriegn key filed in my Child table is setup as a pop up menu that displays the Primary Key info from the parent so when i select the matching record it brings in all the info from the parent into the child table.

           My question is how can i set this up so it does it on import? All other information imports correctly into each table, but as it is now, in the child table i must select the info in the drop down to bring in the info from the parent on every record. With 3000 records that could take a while.

        • 1. Re: Importing into related tables

               Not a question we can answer without more information. FileMaker can't read your mind to determine which record in the related child table links to a given record in the parent table. So unless there is some common value in a field of the flat file table that you can use for this purpose, you'll have to link them manually.

               If there is such a field in your data to be imported, describe it and I can tell you how to use it to link your related records to the correct parent record.

          • 2. Re: Importing into related tables

                 The parent table is a list of tract numbers which display information about the land, location gross acres, ect. The child table is ownership information for the people who own portions in that tract. The common link is the tract number (ex: 001.002.003). I import the tract numbers into both sides from the flat file. The way it is set up now, the fk_TractId is a pop up that gets the value from the pk_TractId which is serialized and shows the info from the Tract Number field. So PK = 1(value list displays info as 1 001.002.003), FK= 001.002.003.

            • 3. Re: Importing into related tables

                   Does this mean that you have a field in your flat file that contains the correct tract number?

                   Do you use the tract number fields as match fields in your relationship or do you use an auto-entered serial number generated in the parent table?

                   Using a serial number is the better way to link your records, BTW, even though it would complicate your data imports here.

              • 4. Re: Importing into related tables

                     The flat file is tract Number based so that number is the constant between the two fields.

                     The PK is serialized so if I have 300 tracts the tract number is relative to the serial number ex. tract 001.002.003 = serial # 1, tract 001.002.004 = serial # 2 ect.

                     when I import into the FK field i'm importing the tract number. it shows up in the FK field for all my records, it just doesnt pull in the info that relates from the parent table unless i click on it and select the same number from my dropdown. it basically isnt triggering the relationship on import.

                     Thank you for your patience, im a newbie at all this. ;)

                     here is my relationship graph



                • 5. Re: Importing into related tables

                       As a newbie, you've set up your relationships correctly by serial ID instead of tract number--a common newbie mistake that creates problems for your related data should you enter data with an incorrect tract number and then try to correct the error.

                       Add a TractNumber field to your Mineral Owner Information table. You will not use this field except as a means of updating newly imported records in this table to link to the correct record in the parent table, Mineral Tract Information.

                       Once you've added that field, we'll create a new Table Occurrence for the parent table:

                       In Manage | Database | relationships, make a new table occurrence of Mineral Tract Information by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box. Let's name the new occurrence MineralTractByTractNumber.

                       We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                       Add it to your relationships like this:

                       MineralTractByTractNumber::TractNumber = Mineral Owner Information::TractNumber

                       If your flat file only contains information to be imported into the child table, skip this next paragraph.

                       Import records into the parent table and make sure to select the "auto-enter operations" check box that appears in the last little dialog before you initiate the import. Note that you can use a Unique values, Validation Always setting on TractNumber to automatically filter out duplicate entries in your parent table.

                       Now Import your data into the child table. Immediately after import, your newly added records form a found set. You can then use Replace Field Contents with the calculation option to copy over __pkMinTractID into _fkMinTractID to properly link your newly imported records by ID. The calcualtion you would use would simply be: MineralTractByTractNumber::__pkMinTractID. This will link all your newly imported records to the correct parent records in one batch operation.

                       I have described this all in terms of a manual operation, but if this is a task you must do repeatedly, the entire process can be scripted.

                  • 6. Re: Importing into related tables

                         Did not work for me. Here is what I did based on your instructions. 1. Created a new field in Mineral Owner called TractNumber. 2. Duplicated and renamed MineralTractInformation, creating a table called MineralTractByTractNumber. 3. linked MineralTractbyTractNumber::FulltractNum(this is the calculation field I use to get the tract number, it has always been the referenced field in the value list I used) NOTE: this creates a many to many relationship. 4. Imported the records into the child table. 5. replaced firld contents (fk_TractID), specified calculation MineralTractbyTractNumber::_pkMinTractID.

                         The result is that the field has nothing in it. here is a shot of my relationship.


                    • 7. Re: Importing into related tables

                           Also, after i create the new TractNumber field in MineralOwnerInfo does it need to be a field on the layout? It was I'm just wondering if I need to keep it out there. Thanks!!

                      • 8. Re: Importing into related tables

                             Ok I got it to work. The "TractNumber" field must be on the layout and setup in the import. Thank you so much for all your help!!!!