Filter Report by Dropdown

Discussion created by clayhendrix on Jun 1, 2017
Latest reply on Jun 2, 2017 by clayhendrix

There is another discussion about this topic, but I cannot understand exactly what the author was trying to accomplish and therefore if the solutions offered would be helpful to me.


I have a Student Information System solution for a school. The report I am working on is class rosters. The report works wells. It uses data to put each teachers students one page with his/her name at the top and the school term for which the roster applies.


When users need to generate rosters (by running this report) for a term other than the one that I have coded in the script, they click find, type the school term into the field at the top of the report, perform the find, and then they have to resort to get the students in alpha order and sorted by teacher, so the pagination works. However, that simple find they can perform on this report does not give them the option to exclude dropped students (which is indicated in a field NOT printed on the report). The script that runs when the report opens, filters out dropped/inactive students, but they loose that filtering when the perform a find for a term other than the one I have in the script.


What I would prefer NOT to do is write a script for every school term (into perpetuity) and create a separate button on the reports layout for each term.


I think, with help, I can write a script that is triggered by changing a drop down box at the top of the roster report that will find students currently enrolled (not dropped/inactive) for that school term, sort by teacher name (for pagination of the rosters), and then sort by student last name.


The field that is displayed at the top of the report is the actual field that is displayed elsewhere in the system for the registrar to indicated what school terms the student is enrolled, so at the top of the report, I have that field set for Find, but not browse, because I fear a user could change the values in the term field for multiple (or all) of the student records that the registrar maintains. That would be a mistake. That would lead to restoring from a backup.


Should I create another table occurrence of the table occurrence for the table and also create a global field for the school term field and link the two tables in some way and display the global school term field at the top of the report?


Other information:

-school terms are controlled by a value list

-the roster report is based upon the Enrollment table.
-individual students have multiple records in the Enrollment table: one for each term in which they have been enrolled, are currently enrolled, and are enrolled for the future. The Enrollment table is linked to other tables that contain student demographic information, etc.


Thanks, in advance, for your help.