2 Replies Latest reply on May 5, 2011 2:21 PM by LaRetta_1

    File loop question



      File loop question


      I have created a script which will go through the Inventory of rooms.  While looping, for each Room, it will call another script build on another file (Reservation/sales) this script will then take the room number passed and will search the sales/reservations within the Check-in and Check-out dates. If no conflict is found, it will pass back “OK” to the calling script.  Now, for each “OK” room, I am trying to create a Room Availability list which will then be used to select rooms from.  Main scripts build on the Inventory, calling the availability script works just fine. But the section of this main script (build over inventory) where it check to see if the return code is “OK” then creates a Room record using the layout “available Rooms”.

      All work for a single step.  When it creates the first available room, it quits and shows the layout with this first finding.  I don’t know how to continue with this loop only until all available rooms are found.

      It creates the first find and main script quits layout “available rooms” showing only the first record.

        • 1. Re: File loop question

          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.

          1. Start date of a reservation records falls between specified date range.
          2. End date falls between specified date range.
          3. 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" )


          • 2. Re: File loop question

            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.