I am working on a database used by an academic department at a university. This DB has a parent table for STUDENTS and a child table for student STATUS. Each STATUS record has a date associated with it. These dates can be any time past, present, or future. End users need to be able to view a list of students whose current status = "Active" as opposed to other statuses like "Applicant" "Graduate" etc.
My approach to this was to:
1. Find which STATUS is "Current" for each student out of all of their statuses.
2. Look for Current STATUSES that have a value of "Active" in the field "Status" in the STATUS table
To find the current status, I first calculated the difference between the current data - Status date. When I do this, any status that has a positive result means the status date is in the past. Any result that is negative means the status date in the future.
Next, I copied the date difference variable described above but only had the field perform the calculation if the status date was in the past.
My next step after this was to create a summary variable to find the "Minimum Status Days", i.e. the minimum value for current date - status date. the result is the most recent, AKA current, status. This of course finds the most current status out of all status records.
Since I want to find the minimum value for current date - status date for each student, my next step was to create a calculated field called "Minimum Days by Student" that uses GetSummary to find the minimum value in days for each student when the file is sorted by Student.
Once I had the value in "Minimum Days by Student" I can subtract the number of days since the status began from it. When the result of this calculation = 0, the record is the current STATUS. I have the current record for each student = "Yes" for the field Status Is Current.
The final step was to created a calculated field that sets the value to "Yes" when Status = "Active" and Current Status = "Yes". I named this field "Current Status is Active."
So.... attached is a screen capture of a list of STATUS records. As you can see, the list is sorted by student and then by status date in ascending chronological order. It works exactly as I need it to in the STATUS table.
The problem: I need to display a list of records in the parent table STUDENTS when the value in "Current Status is Active" in the STATUS table = 'Yes." Of course, this value is unstored because it is based off of a field in statuses that uses GetSummary. In my ideal world, I would like to create a field in STUDENTS that counts the number of STATUS records per student where "Current Status is Active" = "Yes". But since it is unstored, I cannot use this approach.
Any solutions people can think of? I will add that the list of students really does need to display STUDENT records, not a portal of STATUS records that display STUDENT information. The people in the department need to view a list of active students and click a for each student to go to a layout that displays information on that current student.
If there is a way to create a portal showing only Current Active STATUS records that would send end users to a layout in the student table for the particular Student that appears in the portal of STATUS records that would suffice.
Good Karma for helping me is promised.