3 Replies Latest reply on Apr 14, 2014 9:27 AM by philmodjunk

    Working with relationships...



      Working with relationships...


           Hello, I am using FMP12.

           I am trying to get a layout "Transcript" to display student results.

           I have a "Students" Table (with potentially hundreds of records), and a "Courses" table (with about 80 records).

           Each Student is enrolled automatically in 5 Courses. There are no electives. The Transcript layout thus needs to have five "slots" on it, one for each course. Each slot can only potentially have a unique and limited set of Courses that will appear in it, so all Courses have, aside from their unique course codes, a "slot number" 1 to 5.

           The actual Courses that have to appear for any student are determined (all 5 of them) by a single set of parameters in the Students table (department, major, stream, etc...). I have a calculation field in the Students table that sets a "profile" code for each student based on these variables. I have a similar field in the Courses table. All courses can hence be uniquely identified by either their course code (which is established by my university and has nothing to do with my database, but which is a field in my Courses table) or by a combination of this "profile" code + the Course's slot number. My "profile" field in fact identifies unique "degrees" if you like, where each "degree" has a unique combination of courses in these 5 slots.

           But I can't work out how to get the "Transcript" layout to actually display the right Course codes for each student... I have a "Transcript" table with a direct relationship to my Students table (via a StudentID field) as each student has a single transcript, within which I have established fields for each course slot, but I don't seem to be able to work out how to get these fields to display the right Course codes... I'm sure I have all the data organized sufficiently...  It's a matter of implementation that I am not understanding... 

           I hope that makes sense. Any help would be greatly appreciated.

        • 1. Re: Working with relationships...

               First, the basics. This won't be the full answer as there are details in your post that will affect the final design and which are not 100% understandable (slot number?)  by me at this point in the process. But the following should serve as a foundation on which to build the complete solution:

               You have a many to many relationship. Any given student will take multiple courses and any given course will be taken by multiple students. This is best implemented by adding a third, "join" table to your basic design of tables and relationships:

               Start with these relationships:


               Students::__pkStudentID = Student_Course::_fkStudentID
               Courses::__pkCourseID = Student_Course::_fkCourseID

               You can place a portal to Student_Course on the Students layout to list and select  Courses records for each given Students record. Fields from Courses can be included in the Portal to show additional info about each selected Courses record and the _fkCourseID field can be set up with a value list for selecting Courses records by their ID field.

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

          • 2. Re: Working with relationships...

                 Thanks for your answer PhilModJunk. Sorry for the unclear info in the initial post.

                 I think I understand the notion of join tables. My <Transcript> table is essentially the equivalent of your <Student_Course> table. However, I was thinking in terms of making my "transcript" layout actually dependent on the Transcript table itself...  You seem to be talking in terms of making it an alternative layout dependent upon my <Students> table, and displaying Student_Course records in a portal...  I think that is where my confusion is coming in. I will try making my transcript layout dependent on the Students table and use portals and see if that works better!


            • 3. Re: Working with relationships...

                   Reports can be set up that work from the Join table. The portal to the join table is most often used for data entry with the report done from the Join table as this can be much more flexible and option. Related fields from students and courses can be included in such a layout.