AnsweredAssumed Answered

Show only most recent matching record in portal

Question asked by BenSachs-Hamilton on Jul 3, 2014
Latest reply on Jul 3, 2014 by philmodjunk


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!