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

Parent/Child Relationship in Imported Data

MrsEaves Jul 9, 2014 2:47 PM

Title

Post

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?

Thanks

1. Re: Parent/Child Relationship in Imported Data

philmodjunk Jul 9, 2014 3:33 PM (in response to MrsEaves)

     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------<Features>------Products|SerialNumb

     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
Like Show 0 Likes(0)

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

MrsEaves Jul 11, 2014 1:19 PM (in response to MrsEaves)

This was extremely helpful and solved my problem. Thank you so much!
Like Show 0 Likes(0)

Actions

Go to original post