I have a view - ScheduleView that lists student ID's and the Teacher's employee ID. I would like to filter multiple list pages based on that data without doing a join on each table. I have a session variable that captures the EmployeeID when they sign into the application.
ScheduleView Example:
StudentID EmployeeID
123456 1111111
123456 1111112
123456 1111113
567890 1111111
567890 1111113
A student can be in multiple classes and therefore have multiple teachers.
I would like to filter the List page: Before SQL Query in the table IEP_Info (and several others).
This is my current Before SQL Query (on the IEP_Info table) that is not working:
if Session("GroupODR")="TCHR" then
sql = "select StudentID from dbo.ScheduleView where dbo.ScheduleView.EmployeeID='" & Session("Employee_NumberODR") & "'"
set rs=CustomQuery(sql)
strWhereClause = whereAdd(strWhereClause,"StudentID='" & rs("StudentID") & "'")
end if
If there was an IEP_Info record for student 567890, Only teacher 1111111 or 1111113 should be able to see that on the list.
fyi:
The following query displays all of the appropriate students but not filtered for IEP_Info. My test teacher has 19 students in her class but only one of those students has an IEP_Info record. The following displays IEP_Info as if there were 19 records.
if Session("GroupODR")="TCHR" then
strSQL = "select StudentID from dbo.ScheduleView where dbo.ScheduleView.EmployeeID='" & Session("Employee_NumberODR") & "'"
end if