3 Replies Latest reply on Mar 25, 2015 8:26 AM by philmodjunk

    Automatically Enter Foreign Key from Primary Key

    JoanneVermulm

      Title

      Automatically Enter Foreign Key from Primary Key

      Post

      I am trying to wrap my brain around primary/foreign keys. I have three tables - Classes, Sections and Teachers. Once a Class record is created, I can create a Section of that class using the Section Portal (located in Class Layout). It automatically creates the Foreign "Class ID" Key in my Sections Table.

      Also within the Sections portal, I can choose a teacher based on which department is chosen from a Section portal's drop down field. (The dropdown is related to the Teacher Table only through some secondary TOs of Teacher and some Value Lists).

      Without somehow getting the Teacher Foreign Key filled in on the Sections Table, there is no direct link between the Teacher Table and the Sections Table and I cannot see which Sections a teacher is assigned to in the Teacher Layout (through a Sections Portal located in Teacher Layout).

      So my question is - how do I get the Teacher ID Foreign Key to populate in the Sections Table when I choose a teacher from the dropdown? I get the need for Primary and Foreign Keys but I always seem to run into the issue of how to get this unnatural field filled-in to connect the tables without using some sort of calculation, which is not best practice for keys, or so I have read.

      Any help written as if it were to a second grader would be greatly appreciated.

      Screen_Shot_2015-03-24_at_2.45.05_PM.png

        • 1. Re: Automatically Enter Foreign Key from Primary Key
          philmodjunk

          Also within the Sections portal, I can choose a teacher based on which department is chosen from a Section portal's drop down field.

          That's the user action that should enter the Teacher's PK value into the FK field of the portal record.

          Here's the "Beginner level" approach to set up first to confirm that your system is properly set up and working:

          Define a "use values from field" value list that lists the Primary Key from the teacher's table as field 1 and the teacher's name as field 2. If the proper data and relationships are in place this can be a conditional value list limited to a specified department. You then format the Teacher foreign key field in your portal record to use this value list. You select your teacher by name, but the value list enters the Primary key value when you do. You can choose to hide the first field or not and you can compare results between a pop up menu and a drop down list to see what you like.

          That may be all that you need, but I have some resources if not all of that works for you or if you need something that enables you to better handle longer lists of teachers:

          You might check out the Adventures in FileMaking series. Adventures 1 and 2. They are free to download.

          Adventures in FileMaking #1 - Conditional Value Lists (includes details on how to set up a basic field based value list)
          Adventures in FileMaking #2 - Enhanced Value Selection (what to do when a simple value list won't cut it.)

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Automatically Enter Foreign Key from Primary Key
            JoanneVermulm

            Thank you very much not only for your quick response, but your 2nd grade level response :0). I often feel like an idiot when attempting to ask these questions.

            That did work. So to summarize, I really don't need the "teacher" field in the Section portal, just the Teacher Foreign Key value list as you indicated, which essentially is killing two birds with one stone. Correct?

            Thanks again!

             

            • 3. Re: Automatically Enter Foreign Key from Primary Key
              philmodjunk

              That is correct, for this method of linking records. If you explore the adventure files, you'll find that one method requires such a text field in order to implement a drop down list of names that "auto-completes" to produce a more user friendly experience while still linking records by ID's.

              A basic rule of thumb for relational database design is to never store exactly the same data in more than one table--except for primary key to foreign key match fields.

              But a basic rule of good user interface design is that sometimes, the above rule has to be bent or broken in order to produce an interface that is easy to use and that minimizes user data entry errors.