2 Replies Latest reply on Jul 3, 2014 1:35 PM by philmodjunk

    Show only most recent matching record in portal

    BenSachs-Hamilton

      Title

      Show only most recent matching record in portal

      Post

           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!