Show only most recent matching record in portal
I use a Filemaker database to help run a boarding house, and there are several instances where I would like a portal field to display only the most recent matching record (i.e., the one with the highest ID number), in cases where there are multiple matches.
For example, I have a Contact List layout that displays contact information for each resident, and I would like it to also display their current room. I keep track of room occupancy in a table (Occupancies), which relates by ID number both to my main People table and to my Rooms table. When I try adding a field to the Contact List layout that displays Rooms:ID, it displays the earliest matching record for each resident (i.e., the one with the lowest ID number in the Occupancies table). For residents who are now living in a different room, that's a problem.
I've also tried creating a CurrentRoom calculated field in the People table, defined as "People:CurrentRoom = If ( IsEmpty ( Occupancies:EndDate ) ; Rooms:ID )" but this has the same result.
I've also tried both of those methods using the Occupancies:RoomID field instead of the Rooms:ID field (these are linked), but the result is the same.
I don't know if I've described this in way that makes sense, but if it does, and anyone has any suggestions, I would love to hear them!