2 Replies Latest reply on Jul 2, 2015 12:50 PM by LuisHernandez

    Options for complex composite keys in join tables



      Options for complex composite keys in join tables



      Context: Small school enrollment database

      Purpose: track student enrollment, grades, classes, etc.

      I am trying to link student enrollment with their courses. Currently a student will be enrolled in a semester and level, then enrolled in classes. I have used composite keys to accomplish this. The Student_Level enrollment keys have StudentID & SemesterID & Level. The Student_Course has the above id and an additional course id.

      The problem is that this results in an 8 digit number (StudentID) + 4 digit SemesterID + Level + CourseID. No one could be expected to enter that key in a layout. The only solution I can think of is to add fields to each of my tables with redundant info (semester, level, student, course) and let the computer auto enter both the foreign and primary keys.

      Would anyone be able to offer a solution to this issue?

      Thank you.

        • 1. Re: Options for complex composite keys in join tables

          Item 1, Users should NEVER be required to enter an ID. They should instead select a name or description from a list--in a value list, selection portal or other layout design and then the system enters the ID for them

          Item 2, You should not "encode" such data into your key in the first place. Define your student level enrollment key as either a number field with an auto-entered serial number or text field with an auto-entered Get ( UUID ) instead. The only two requirements you should meet here is that the value has to be unique in each record in this table and are values that will never, ever be changed.

          To explore options for value lists and other methods for selecting items by name/description but having the correct ID entered by the system I suggest downloading both files of the "Adventures in FileMaking" series. 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.)

          A field based value list with ID as field 1 and a name/description field as field 2 is the simplest of many options. #1 has a detailed example of this and #2 then demo's options that use a more sophisticated design in order to provide the user with a more user friendly interface.

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Options for complex composite keys in join tables

            Thank you. That clarifies my misunderstanding of how Filemaker works.Thank you for the instructional files too.