Creating a visual representation of a schedule
I may be completely on the wrong track with this, but here goes.
I am using Filemaker Pro 11 in Windows Vista.
I am working on a database of faculty members at a university and hoping to add in a way to create a visual representation of each faculty member's weekly class schedule.
The database has quite a few tables, but for the purposes of this schedule, the relevant tables are "faculty_members," "courses," "schedule," and "week."
faculty_members and courses contain information about the individual teachers (name, birth date, etc.) and courses (course code, title, credits, etc.). "schedule" links faculty_members with courses (via serial numbers) and contains additional fields about the start and end time of each class on each day (monday_start, monday_finish, tuesday_start, tuesday_finish, etc.)
"week" is where it starts to feel very jury-rigged and might be where I am getting mixed up. I apologize if the explanation is unclear. It has a field called "time" which contains 45 records in 15 minute increments from 9:00 to 20:00. There is a global field for the faculty serial number, and then calculation fields mon_course, tue_course, wed_course, etc.
I would like for there to be vertical columns for each day of the week with times running down the left-hand side of the page. So the calculation fields (mon_course, etc.) are set to equal the name of the course, if the course's start time is greater than or equal to week::time and the finish time is less than or equal to week::time.
Case ( schedule::monday_start ≤ time and schedule::monday_finish ≥ time ; courses::course_name; "" )
Then when I view "week" in list mode and use conditional formatting to color non-empty cells, the schedule appears visually (as shown in the attached image). The problem is that it is only displaying one class per teacher right now (I assume whichever one appears first in the "schedule" table).
I have tried relating the week::faculty_serial to the serial numbers in both faculty_members and schedule. Is there some way to make sure that my calculation captures all the instances in schedule where the faculty serial matches?
If this isn't making sense, please let me know and I am happy to clarify anything. Being able to generate these schedules automatically would drastically reduce work load.
Thanks for any input.