I'm going to suggest an alternate approach: base the relationship on a global field that stores a list of room IDs. The portal would show the rooms related to this global field.
So, once the user selects the date range etc., she would click a button that does a scripted search, and then the script loops through the records and adds each ID to the global, e.g.:
Set Field[ gRoomIDList ; List( gRoomIDList ; id_room ) ]
Putting the logic in the script rather than the calculations or relationships gives the most flexible and maintainable solution IMHO.
I'm going to second Tom's suggestion. If I understand your situation correctly, you've a fairly complex setup here and trying to do it on the relationship graph will be difficult to visualize. I would be inclined to do this with a scripted find instead of trying to do it all on the graph as well.
Thank you very much for your suggestions. However, I would like to ask for some help on how such a search script would look like.
I tried to write such find, but was unsuccessfull. It seems that I am not skilled enough to complete this task.
I think I got the search params for finding the reservations that fall in the same time period, but have problems passing them to count to get the numbers, and to have something to compare to number of spots in given room.
In case you don’t already have it. Ray Cologon over at nightwing has a demo you might find helpful. Check it out at http://www.nightwing.com.au/FileMaker/demos7/demo705.html
It was written for FMP v7 but runs nicely under v11.
Hope it helps,
Re: constructing the Find, it seems to me this will need to be a multi-step process.
1. find the reservations that overlap the requested start/end dates
2. go to related rooms, using "match all records in current found set"
3. show omitted only
4. constrain by room type/number of beds/other criteria
IOW, you find the reservations for the rooms that ARE booked, then use that to get to the rooms that are available.