Can't get to show most current record on a Summary Layout
I have a layout that shows a summary of teachers at the school I work at, and they are sorted by grade level. Each teacher line also shows how many students they have in their classroom. The layout also has a subtotal part that shows the total number of students per grade level, and a footer that shows the total number of students per school.
Now, I have some students that unfortunately are being changed from one classroom teacher to another after the school year has started. I need to keep track of these changes, so one student can end up with 2 classroom records for the same year.
The problem: On the summary layout the classroom changes are not showing up. Only the first classroom teacher info is shown, but not replaced with the most current classroom info.
I've played with the timestamp in a ceiling function to get the most current changed classroom record, but from there to having the layout showing the most current data on classroom assignments for the students seems very far from being reached. I'm stuck.
Hoping this scenario makes sense, does anyone know how I can solve my problem?