I believe I recognize this project...
When you need this of available rooms is should it be a list of all available rooms for a single date or could you be checking for a range of dates?
I think we might be able to set up a list of of rooms in inventory, specify a date or date range and see the list update with an availability flag that shows each room's availability. Then all you need is to perform a find to reduce the list to just those that are available.
Assuming a date range, we worked out that a room would be unavailable if any of the three following tests are true.
- Start date of a reservation records falls between specified date range.
- End date falls between specified date range.
- Reservation date range starts before start of specified range and reservation date range ends after end of specified date range.
It'd be nice if FileMaker allowed us to construct a single relationship that matched on all three cases, but I can't for the life of me figure out how to do that in FileMaker like you might with an SQL expression in other systems.
We can, however, construct thee different relationships and refer to all of them in one calculation to return an availability indicator.
Inventory::RoomID = ReservationsCheckStart::RoomID AND
Inventory::gStartDate > ReservationsCheckStart::StartDate AND
Inventory::gStartDate < ReservationsCheckStart::EndDate
Inventory::RoomID = ReservationsCheckEnd::RoomID AND
Inventory::gEndDate > ReservationsCheckEnd::StartDate AND
Inventory::gEndDate < ReservationsCheckEnd::EndDate
Inventory::RoomID = ReservationsCheckEnclose::RoomID AND
Inventory::gStartDate > ReservationsCheckEnclose::StartDate AND
Inventory::gEndDate < ReservationsCheckEnclose::EndDate
Then, if RoomID is of type number, the following expression will show each room as available only if no records matches on any of these relationships:
If ( ReservationsCheckStart::RoomID or ReservationsCheckEnd::RoomID or ReservationsCheckEnclose::RoomID ; "Not Available" ; "Available" )
Here is a general perspective of how to ask an external script to return to originating script ... have a step ... any step after the external so it could be even a simple Browse mode or refresh. If the external script is the last of the actions then that is where it will terminate. If the originating script performs the last step then you will be returned to the file where it resides to terminate.