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.