2 Replies Latest reply on Jul 11, 2014 1:19 PM by MrsEaves

    Parent/Child Relationship in Imported Data



      Parent/Child Relationship in Imported Data


           I am creating a database of products and features available by season. Each product in a given season has many features. Each product only appears once in a season but may appear in multiple seasons. Each product has a unique 4-digit identifier code that may be used in several seasons. Example:

      Spring 2015
           product ABCD
           feature 1, feature 2, feature 3
           product EFGH
           feature 1, feature 2, feature 3

      Fall 2015
           product ABCD
           feature 1, feature 2, feature 3
           product JKLM
           feature 1, feature 2, feature 3

           I will be importing data from two separate sources: one to create my Products table and one to create my Features table. Each Product and each Feature will include the four-digit identifier along with the a code for the season. Every season I will be adding to the database with additional imports.

           A simple way to set up my relationships is to match the product code + season code in the Products table to the same product code + season code in records in the Features table. I have that functionality working, but sometimes the product code for a product will need to change which, of course, creates a bit of a mess as the product is no longer related to its features.

           I'd like to have a unique identifier to relate the parent and child records so the changing of the product code does not create problems but I can't figure out how to do. If I import the data for my Products table using an auto-entered serial number as ProductID, how do I match the correct related features to that key field when I import my features?


        • 1. Re: Parent/Child Relationship in Imported Data

               The need to link newly imported data  in two different tables by an ID value supplied by a field defined in FileMaker in the parent table is a fairly common one.

               This can be done pretty easily if you have a field or combinations of fields that uniquely identifies the parent record and also the set of related child records. The trick is to set up two relationships to two different occurrences of the Parent table. One is based solely on the identifier data imported into the table, the other is based on the auto-entered serial number field. You use the relationship based on the imported identifiers as a temporary link only for the purpose of copying over the serial number value in order to match values by serial number.

               Say you have these table occurrences in these relationships:


               Products|Import::Product = Features::Product AND
               Products|IMport::Season = Features::Season

               Products|SerialNumber::__pkProductID = Features::_fkProductID

               Products|Import and Products|SerialNumb would be Tutorial: What are Table Occurrences?. __pkProductID would be an auto-entered serial number field. (Enable auto-enter options during import or use Replace field contents with the serial number option to assign a serial after the Products records have been imported.)

               To get the needed value copied into _fkProductID for every features record, either enable an auto-enter calculation: Products|Import::__pkProductID or use Replace Field Contents to copy the value of this same Products|Import::__pkProductID field into the _fk field after importing the records.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          • 2. Re: Parent/Child Relationship in Imported Data

                 This was extremely helpful and solved my problem. Thank you so much!