Problems creating a relationship that will display a weekly schedule
I'm working on a db to track attendance, among other things, for a small tutoring business. I need to be able to print out a weekly schedule (roll call) so attendance can be taken and students rescheduled since the computer is usually unavailable. At the end of each day, the database is to be updated from the printed schedule.
In my system, I have a table named SESSIONS that has one record for each session that a student has scheduled (whether they attend the session or not). It has fields such as student_ID, session_date, session_time, and attendance_status. There are 3 possible sessions a day (3PM, 5PM, & 7PM, Monday thru Friday), and students generally attend the same session every week, but that can vary. We usually schedule students for an entire month in advance. Class sizes fluctuate, but if, for example, I had 5 students in every class for one month, there would be about 300 records in the table; for a year, it would be about 3,900 records.
I created a layout with 15 portals (10 rows showing in each): there's one portal for each of the 3 sessions on each of the 5 weekdays (5 columns representing weekdays, 3 rows representing each daily session). Each portal has a filter applied so only the names of the students signed up for THAT session of THAT day of the week should display in that particular portal.
I would like the schedule to only display records for sessions taking place during the current week so I created a calculation field, cur_week_sessions, that shows "1" for all records where the value of session_date is within the current week range and is empty for the rest of the records.
My problem is that I can't figure out how to create a relationship to display the current week of records nor which relationship to assign to the layout and which to the portals. I've made dozens of attempts: usually the portals are blank, other times they repeat one student's name and nobody else. It's been horribly frustrating because I DID get it to work a couple months ago, then accidentally destroyed the relationships and never realized it until it was too late to restore it!
Can anyone point me in the right direction for creating this relationship? I hope I have described the relevant parts of the system adequately because, unfortunately, I cannot post any files, but I can post some screen shots, if that helps. BTW: I suspect I may also be doing something wrong with my index setting options since I don't fully understand their purpose, so please specify where that matters as well.
PS: I'm using FMP 11, Advanced