This question potentially relates to "filtered relationships" or "filtered calculations". I not sure how to classify.
three tables: Reservation, Trip, Person
A reservation relates to one Trip and multiple Persons
A trip relates to multiple Reservations & Persons, and has a set of room assignments
I can assign a room to each person, per trip, and that field could be stored in either the Reservation table or the Trip table (not clear on which is better). However, that data is a room number.
What I'd like to do is show a "Room Assignment Layout" from a given Trip of the people listed by room #. In other words, how do I translate the room number to the name of the person to whom that belongs?
What I'm looking for is some sort of "filtered relationship lookup", but that phrase casts too wide of a net when searches in various Filemaker forums.
In the Trip DB, I've tried making each room field a "filtered calculation", meaning looking through the "TRIP ID" match, show me the person who has room assignment #1:
e.g. for field "room1"
If ( Reservations::room assignment=1 ; Reservations::fk_Person ID ; "not assigned" )
but the relationship never matches.
Also, this is a one-way street and not the ideal approach.
Also, how do I make this a "two-way street"? In other words, I want to be able to change the room # from a Reservation, OR the person's name (primary key actually) while looking at the Room layout. It seems I need some sort of "join table", but that concept is cloudy to me at the moment.
Can someone steer me in the right direction?