6 Replies Latest reply on Aug 2, 2017 3:27 PM by JuliaOlsen

    One-Many Relationship Tables and Data Imports


      I have a DB which has a number of related tables.


      I need to import data into "Summer Work" which is linked both to "Teacher Data" and "Business Records".  Each teacher has one summer work record, but each business may have many summer work records as one business may employ multiple teachers each summer. The summer work records which I initially create show up correctly in both "Teacher Data" and "Business Records".  I use portals to display these connected record. See Map of Relationships attached.


      1. I create the Summer Work records in advance through the "Teacher Data" interface. This creates a record in "Summer Work" which does in fact link to both linked tables ("Teacher Data" and "Business Records")
        • See Summer Work History Record Creation, attached.
        • This is necessary as I have the positions available for the teachers before we have all the details to import
        • The data comes from excel files which are generated from online forms.
        • I am having two problems (or the same problem) with both sets of relationships.
      1. To import the records into "Summer Work", the following happens
        • I import the data as "update matching records" based on year and teacher last name.
        • I have the "Teacher Data" and "Business Records" referenced as foreign keys with a lookup value.
        • A new record is created which does NOT seem to pull the FK lookups
        • See Summer Work, attached.


      You will notice in the relationship map I also need to import "Teacher Weekly Feedback" (many per teacher per year) which links to "Summer Work" (one per teacher per year). Same issue. 


      To summarize, the data is importing, but how do I get the correct linkages to my other tables on import?  I need an outside observer to help me out here. Thank you in advance.

        • 1. Re: One-Many Relationship Tables and Data Imports

          You need to create an import table for the flat data and have a script that will run through and create all the relationships/records in the proper tables.

          • 2. Re: One-Many Relationship Tables and Data Imports

            What I see is kinda like using your cell phone to call the cell phone service provider to tell them that you have no service.


            Your relationships link by Business ID AND by business name to a business. They link only by Teacher ID to the teacher table unless one of those other table occurrences is an occurrence of the same table.


            Your imported data probably does not have those needed business and Teacher IDs and thus cannot look up those IDs as they can't link to those tables without them.


            Big_Tom is giving you good advice that is often given here in the forum. It may very well be the best way to go.


            But if you have consistent, accurate data in your forms for business and teacher names that will match to the same names in your table, you might be able to use your current approach if you just add two more table occurrences to your relationship graph and use them to look up the needed ID values.


            Here's how to do it for businesses. The same approach can work for teachers as well:

            In your relationship graph, select the Business_Records table occurrence by clicking the duplicate button (two green plus signs). This creates a new box on your graph that refers to Business_Records, but does not create a new table. You can double click the box to get a dialog where you can rename it to something useful such as Business_Records|Name. Then link Company of this table occurrence to Company_Name of your Summer_Work table occurrence. You can now set up the FK_BusinessID field in Summer_Work to look up BusinessID from Business_Records|Name.

            • 3. Re: One-Many Relationship Tables and Data Imports

              BigTom I have done quite a few scripts, but could you provide an example of this to get me started, as this is a new concept for me?

              • 4. Re: One-Many Relationship Tables and Data Imports

                Thanks phil I do have consistent data. I've created additional table occurrences for other purposes, but hadn't considered your approach for this instance.  You are clearly the expert and I the novice here ....

                • 5. Re: One-Many Relationship Tables and Data Imports

                  Yes but don't count out the other advice here. I'm making a pretty big assumption based on your reference to "forms" that the data is going to be perfect when it comes to names. Name matching can be problematic even when the data entry is error free as a company's name may be written in more than one way and both company and people names are not guaranteed to be unique. (They might even change their names on you, both companies and people).


                  Thus, importing into a staging table to keep problem data out of your working tables can be a necessity. So look carefully at both options here.

                  • 6. Re: One-Many Relationship Tables and Data Imports

                    Could you direct me towards a sample script, should I want to go that route?