I've looked through many Discussion posts and some of them "dance around" my particular issue, but I haven't seen a solution that matches closely enough. I think I must be missing something but I haven't been able to figure this one out.
I've tried to simplify and analogize my problem as much as I think I'm able, and have built a basic test database. My analogy is a set of Students who may take classes in 1-N classrooms over a particular period of time. I'm tracking current and historic data.
Students (unique-ID + name)
Rooms (unique-ID + location)
Student-Rooms (unique-ID; student-ID; room-ID; start-date; end-date; status: Active or Historical, based on date)
My need is to be able to display (portal, I figured) Current and/or Historical data for a given student, showing what Room(s) they are/were in.
I've created a Status field in the Student-Rooms table that checks the end-date against current-date to set a flag indicating "A" (for currently Active) or "H" (for Historic).
I have a key-field (on which relationship is built) in Student-Rooms table using the student-ID + Status; and another in the Student table using student-ID + "A" (to force looking for Active-only rooms). The relationship has Student as the parent, and Student-Room as the child.
That leads to the dilemma … If I calculate the Status using the Get (Current Date) fct, it never recalculates, so the status at creation of a given record remains its status.
I tried using a single-record Common table, with a startup script to update a date field there with Get (Current Date), which works in terms of forcing the Status field to recalculate, but then the Status cannot be indexed (due to the relationship to Common), so I can no longer use it as a key.
I *think* I've explained everything clearly, but please ask questions if you're able to assist. I'm attaching the relationship diagram here in case that helps clarify, along with the field definitions for Student and Student-Room, all from my test database.
I can't imagine there isn't a way to accomplish what I'm after, nor that anyone else hasn't encountered this type of situation in the past, so hopefully this is just me experiencing a brain-f@rt.
Thanks in advance for any assistance!