This is a public Forum  publicRSS

Forum post

    ptt555
    Using Date Ranges to filter/find/exclude Date Ranges
    Forum post posted May 5, 2010 by ptt555 , last edited February 9, 2012
    919 Views, 14 Comments
    Title:
    Using Date Ranges to filter/find/exclude Date Ranges
    Post:


    Hi all,

    I have records containing date ranges (startdate, enddate).

    I need to perform a find using a date range, and have Filemaker return the ID's/records that DO NOT have any records within that date range.  Think similar to a reservation system.   Find all rooms (ID's) that are not reserved for any dates between 4/1/2010 & 4/15/2010.

     

    DATA LAYOUT/SAMPLE DATA  (all tables related via ID)

    Table1  (ID is unique)

    ID       ID_Description

    A        Room1

    D        Room2

    G        Room3

     

    Table2

    ID      StartDate        EndDate

    A       05/01/2010    05/03/2010

    G       05/16/2010    05/17/2010

    A       05/14/2010    05/16/2010

     

    Table3 (a table containing a seperate record for every ID in  Table2 from startdate to enddate inclusive)

    ID      Date

    A       05/01/2010

    A       05/02/2010

    A       05/03/2010

    G       05/16/2010

    G       05/17/2010

    A       05/14/2010

    A       05/15/2010

    A       05/16/2010

     

    FIND EXAMPLES & DESIRED RESULTS

    Enter a date range & have FM return all ID's that have NO dates within those ranges

    Range  04/29/2010 to 05/30/2010 would return nothing (each ID has some of the dates)

    Range  05/10/2010 to 05/15/2010 would return would return G (since for A, a record exists for dates 5/14 & 5/15/2010)

    Range  05/08/2010 to 05/12/2010 would return A & G (since no records exist for either in that date range)

     

    Does my data structure allow for a successful find using Filemaker?

    I realize I may not need both Table2 & Table3 to accomplish this.

    What would be the recommended approach?

     

    Advance thanks for all that take time to help.

     

     

     

    FM Pro Advanced 10 on MAC

     

    Answer

    • comment

      Well, one of the two tables 2 and 3 is certainly redundant - and you can search for a date range in any one of them.

    • ptt555

      It's not (at least for me) a simple date range search.

      Perhaps it's my power of explaining the situation that is lacking.

       

      I have items reserved with multiple date ranges.

      I need to find which items(ID's) are available for a given date range... to know if they are available for the entire period.

       

      A simple startdate >= xx/xx/xx and enddate <= zz/zz/zz doesn't do it (i don't think)

    • comment

      The following script (which you can also perform manually) should find items that do not have reservations overlapping the given range:

      Go to Layout [ Items ]
      Enter Find Mode [  ]
      Set Field [ Reservations::StartDate; "≤" & gRangeEnd ]
      Set Field [ Reservations::EndDate; "≥" & gRangeStart ]
      Omit Record
      Perform Find [  ]


      gRangeStart and gRangeEnd would be global fields where you enter the given range (these can be in any table).

       

       

      You could also show the available items directly through a relationship, without performing a find.

    • ptt555

      Comment,

      That's perhaps a start & I've tried some variations on the calculation, but for the sample data

       

      Table2

      ID      StartDate        EndDate

      A       05/01/2010    05/03/2010

      G       05/16/2010    05/17/2010

      A       05/14/2010    05/16/2010

       

      using your suggested formula with global values grangestart = 05/02/2010 grangeend = "05/04/2010"

      it returns A & G.   I need it to return only G.  

       

      The calculation needs to return the ID's with absolutely no records existing with even one date in the range grangestart to grangeend.   Once it "sees" that an ID (in this case ID#A) has a record within the range... I want it to 'forget' ID#A as a possible output.

       

    • comment

      I suggest you check your implementation - because when I try it, it does find only G.

    • ptt555

      I'm feeling dumber than I look... I still get both returned, it only omits the first ID#A record from the results.

       

      With your feedback, I just created that exact table & ran the find manually. (had run it earlier with script in my actual database with  live data).

       

      It returns:

      ID#    StartDate       EndDate

      G       05/16/2010    05/17/2010

      A       05/14/2010    05/16/2010

       

      Working through the logic 'on paper' Using

      Set Field [ Reservations::StartDate; "≤" & gRangeEnd ]
      Set Field [ Reservations::EndDate; "≥" & gRangeStart ]

      Omit Record

       

      Evaluating  the records above

      G  5/16/2010 ≤ 5/4/2010 returns false AND

      G  5/17/2010 ≥ 5/2/2010 returns true

       

      A  5/14/2010 ≤ 5/4/2010 returns false AND

      A  5/16/2010 ≥ 5/2/2010 returns true

       

      Neither would be in the found set.

      Using omit as a criteria... it returns them both as results.

       

      What am I missing?  It still finds the second A record whenever I try.

       

       

       

    • comment

       


      ptt555 wrote:

      G  5/16/2010 ≤ 5/4/2010 returns false AND

      G  5/17/2010 ≥ 5/2/2010 returns true

       

      A  5/14/2010 ≤ 5/4/2010 returns false AND

      A  5/16/2010 ≥ 5/2/2010 returns true

       

      Neither would be in the found set.


       

      'On paper', True AND False = False, and since the request is to omit, both records should be found.

       

      OTOH,

      G  5/1/2010 ≤ 5/4/2010 returns true AND
      G  5/3/2010 ≥ 5/2/2010 returns true

       

      True AND True = True, and this record will be omitted.

    • ptt555

      comment,

      in your reply above, i believe you meant to refer to the first row of A being omitted after your 'OTOH'.

       

      i really appreciate the time & effort you're putting in to help me and now we're in agreement on what the formula returns.

      it omits the first row of A while returning G and the second row of A.

      (your previous post said it finds only G which was what i couldn't replicate)

       

      unfortunately, that's not the logic i need implemented.  i actually need it to only return G.

       

      i appreciate your attempts to help & hope you can once the problem is understood.

      let me try again for what's needed for the end user.

       

      RoomA is reserved for use from 5/1 to 5/3

      RoomA is also reserved for use from 5/14 - 5/16

      RoomG is reserved from 5/16 - 5/17

       

      The user "find" would be asking "What rooms are available for May 2nd to May 4th?"

      The system can't return RoomA as it is in use on May 2nd & 3rd.  Otherwise would have a double booking.

      The system should return RoomG as it is not reserved for the dates of May 2nd to May 4th.

       

      I'm still hoping you have some thoughts & energy left to type it up once I've explained the situation well enough.

       

       

       

       

       

       

       

    • comment

      I suspect you are searching the wrong table. If you do your search in the Bookings table, you will find all bookings that do not overlap the range. Thus if room A has two bookings, one overlapping and one not, the non-overlapping booking of A will be found.

       

      You should do the search from a layout of the Rooms table (but still searching the related fields in Bookings).

    • ptt555

      Comment, a quick check running it that way on my data & I think you got it.

      I'm going to play with some scenarios before coming back to confirm for certain.

      Sometimes I can't see the forest for the trees.