1 Reply Latest reply on Feb 3, 2011 9:09 AM by philmodjunk

    Trying to use the "find" to select records withing a date range Product: FileMaker Pro...



      Trying to use the  "find" to select records withing a date range
      FileMaker Pro
      Operating system version:
      Description of the challenge:
      I have a scheduled events and each one is a multi or a single day event.

      If I have an event scheduled for;

      2/2/2011 - 2/5/2011 for Room 1002
      and if the user trys to schedule another event within the same date range using the same room, I should be able to give en error msg indicating that "room 1002" is not available for the selected date range. I have tried several "Find" logic with no luck.
      I would appreciate if I can gen an example of how to do this within a script.
      warmest regards
      Expected result:
      I should be able to warn user that ROOM selected within the date range is not available. I will then cancel the script and refresh the screen...

        • 1. Re: Trying to use the "find" to select records withing a date range Product: FileMaker Pro...

          If "2/2/2011 - 2/5/2011 for Room 1002" means that you have three fields, DateStart, DateEnd and RoomNumber, then this scripted find will work:

          Define global fields for the dates and room number for the pending event: gDateStart, gDateEnd, gRoomNumber that the user fills out before attempting to reserve the room.

          #gDateStart, gDateEnd and gRoomNumber must have global storage specified or this script will not work.
          Enter Find Mode[]
          Set Field [YourTable::DateStart ; "< " & YourTable::gDateStart]
          Set Field [YourTable::DateEnd ; "> " & YourTable::gDateStart]
          Set Field [YourTable::RoomNumber ; YourTable::gRoomNumber]
          New Record/Request
          Set Field [YourTable::DateStart ; "< " & YourTable::gDateEnd]
          Set Field [YourTable::DateEnd ; "> " & YourTable::gEnd]
          Set Field [YourTable::RoomNumber ; YourTable::gRoomNumber]
          Set Error Capture [on]
          Perform Find []
          If [Get (FoundCount ) > 0 /* The room is in use for at least part of this interval */]
             Show Custom Dialog ["This room is already reserved...."]
            //put your script steps to reserve the room here.
          End If

          How it works, the script generates two find requests. When there are multiple find requests, the logic between requests is "OR". The first request looks for records for the indicated room number where the start date falls within the start and end date of any other record for that room. The second request does the same with the requested End date. If either the start date OR the end date over laps the date range of another recod, that record will be found and your found count will be greater than zero.

          Note: with this approach--entering the requested reservation information in global fields--you have nothing to cancel because you don't actually reserve the room until you determine that there are no conflicts.