Hi All
I have a base with 3 tables: Routes, Passengers, and Absences.
The Routes table has multiple passenger fields, which are linked record fields to the Passengers table.
The Absences table has a passenger field, which is a linked record field to the Passengers table and has a lookup field to the associated route field - as well as a date of absence. When an absence is added to the Absence table, it pulls the route the passenger is on.
If a passenger changes route, the linked record lookup field for the route in Absences will change for past absences. This means the data in Absences is no longer historically representative.
What is the best way to preserve the original route that is pulled through onto records in the Absences table?
Preferably I would avoid: a) having the route name in an extra text field or b) creating a linked record field directly to Routes rather than using a lookup field, but open to all solutions.
Thanks in advance for your help!
