Add a calculation to your Job Positions table, called 'People UI Current' with the following formula;
People UI Current = Case( IsEmpty(End Date) ; People UI)
Set it to the same field type as People UI.
Set this field to calculate when no data exists (Use the checkbox at the bottom left of the window)
Then make a new relationship between People and Job Positions, using People UI in the People table and People UI Current in the Job Positions.
This relationship will only link to current Job Positions.
Best wishes - Alan Stirling, London UK.
In the relationship From People To Job Positions add a Sort based on the "Job Positions::End date" field, Ascending. This will cause the empty End Date record(s) to be the first record(s) of the sorted related records. Relationships always 'use' the first related record, including the sorting of the relationship.
You will have the problem of not having any empty End Date records. However, the "Case()" checking for empty isn't needed as the first record is the earliest date. If you need the latest date you need to get the last related record. There are ways to do this if its needed.
Alan, I can't thank you enough for this. It doesn't just solve this one, but several other issues I've had as well.