another portal filtering question

[poetnabotl]

Been looking for a thread that addresses this but haven't found one.

 

Four tables: Students, Classes, Enrollment (join), Attendance

 

Enrollment has one record for each kid for each class in which they are currently enrolled (This layout also has a global date field related to Attendance, allowing me to mark present/absent, thereby creating one attendance record for each "present/absent" date for each student for each class [thankyouforthissolutionahunter].) Make sense so far?

All I need is to be able to see, on the Student record, each class in which the kid has EVER been enrolled. The way it's set up now, if I change the data in Students::ClassSelect, it changes the data in Enrollment::ClassSelect, rather than creating a new record.

I was thinking, perhaps, of having a second TO of Students related to Attendance by studentID, and then a portal. But then I need to figure out a way to filter that relationship so that it only shows one record per kid per class.

Alternately, I was trying to figure out a way to create one record in StudentsCurrentClass each time a kid is assigned to a class (fine, increase the number of portal rows), but then to somehow distinguish on the Students Layout UI the difference between a current class and a class the kid used-to-be-but-is-no-longer enrolled in.

I intended to somehow work Terminators into this post but failed.

Hope that was clear.

Jason.

[poetnabotl]

Hm. Too long-winded?

How 'bout this:

 

Students

studentID

 

Attendance

classTitle

classDate

studentID

 

How do I filter the portal on the Students table to show only one record for each classTitle?

 

Example:

Show "Basket Weaving", "McCain Bashing", and "How to Prepare Yourself For the Next Indy Movie to be Bad"

 

But NOT "Basket Weaving 10/13/07", "Basket Weaving 10/14/07", "McCain Bashing 10/4/07", "McCain Bashing 10/5/08", "How to Prepare... 5/16/08", "How to Prepare... 5/17/08"

[Josh Ormond]

Since there are any other replies...my guess is the answer is very simple or very very complicated.

 

If it were me...I would add a table for Class Titles. Then use the portal to show the records from the Class Title table. Assuming you are using it to select the class the student is taking. Relate Students::studentID to Attendance::studentID, Classes::classID to Attendence::classID.

 

Students

studentID

 

Attendance

classID

classDate

studentID

 

Classes

classID

classTitle