Hi there. I'm creating a database to reserve rooms (about 80) in various buildings for various people (about 200). I want to be able to note their requested room(s) as well as the room they are eventually assigned. For instance, Alice might request Bampton #1, Longborough #4, and Fieldtown #3 as her preferred rooms, and eventually be assigned Fieldtown #3. Betty might request Pinecones #5, Fieldtown #3, and Longborough #2, and eventually be assigned Fieldtown #3 with Alice.
This is pretty simple to keep track of with a many-to-many relationship through a join table: PEOPLE to ASSIGNMENTS to ROOMS. (Each person might have multiple requests and each room might have multiple people in it.) The complication arises when people request "anywhere" in a particular building—e.g. "any room in Longborough". I can go through and note each room in the building they want as a separate request, but some buildings have ten or more rooms, which is a lot of repetitive data entry. Is there any way to create multiple matches at once, so I could enter "Longborough" in Clara's requests portal and see Clara's name in the requests portal for every Longborough room, while still being able to enter "Longborough 4" in Alice's requests portal and only see her name in room 4?
I tried creating a concatenated field that was "Room & ¶ & Building" and using that for the relationship, which worked great for requests; I could select Fieldtown and it would match every room in Fieldtown, or Fieldtown 3 and it would match only room 3. Unfortunately, when it came time to assign rooms, an assignment to any room in Fieldtown would be an assignment to all rooms in Fieldtown, because every room in the building matched that relationship with that person. I also tried making another branch on the relationships tree linking PEOPLE to ASSIGNMENTS 2 to ROOMS 2, with that relationship based on building name instead of room, but that led to some weird behavior like each request showing up multiple times in the portal.