Script to omit records based on calculation among multiple fields
Filemaker Pro Advanced 8.5
I am working with two tables related by ID# in an education database. Table 1 includes fields such as kp_ID#, GRADUATION TERM, and ADMISSION TERM. This table also has a summary field, COUNT_ID# to count the number of students who meet specified criteria in a subsummary report. Table 2 includes fields such as kf_ID# and COURSE TERM.
My problem is that I am unable to figure out how to write a script that will return the number of admitted students who took a course during a given COURSE TERM, but also omits students whose GRADUATION TERM occurs earlier than the COURSE TERM and students whose ADMISSION TERM occurs later than the COURSE TERM.
For example, if we are searching for the number of students enrolled in courses in COURSE TERM 2008, we want to
- include students whose ADMISSION TERM occurred during or prior to 2008
- include students whose GRADUATION TERM occurred during or after 2008
- omit students whose ADMISSION TERM occurred after 2008
- omit students whose GRADUATION TERM occurred before 2008
I have written the script to the point where it will do everything I need except the bulleted items listed above. At the end of my current script, I can constrain the found set by omitting records with ADMISSION TERMs that occur after the COURSE TERM and GRADUATION TERMs that occur before the COURSE TERM, but I would like to add the process to the script if possible. It seems that I would need to constrain the found set based on a calculation involving multiple fields in two related tables, but I’m just not sure. A screenshot of my current script is attached