14 Replies Latest reply on May 7, 2010 12:28 PM by aammondd

    Using Date Ranges to filter/find/exclude Date Ranges

    ptt555

      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

       

        • 1. Re: Using Date Ranges to filter/find/exclude Date Ranges
          comment_1

          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.

          • 2. Re: Using Date Ranges to filter/find/exclude Date Ranges
            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)

            • 3. Re: Using Date Ranges to filter/find/exclude Date Ranges
              comment_1

              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.




              • 4. Re: Using Date Ranges to filter/find/exclude Date Ranges
                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.

                 

                • 5. Re: Using Date Ranges to filter/find/exclude Date Ranges
                  comment_1

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

                  • 6. Re: Using Date Ranges to filter/find/exclude Date Ranges
                    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.

                     

                     

                     

                    • 7. Re: Using Date Ranges to filter/find/exclude Date Ranges
                      comment_1

                       


                      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.


                      • 8. Re: Using Date Ranges to filter/find/exclude Date Ranges
                        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.

                         

                         

                         

                         

                         

                         

                         

                        • 9. Re: Using Date Ranges to filter/find/exclude Date Ranges
                          comment_1

                          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).

                          • 10. Re: Using Date Ranges to filter/find/exclude Date Ranges
                            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.

                            • 11. Re: Using Date Ranges to filter/find/exclude Date Ranges
                              aammondd

                              Maybe you could try this 

                               

                              The Search Range is set in global fields

                               

                              and create calculations in the reservations table to check if the dates are in the search date range or not

                              and one that examines these to tell if the room is available

                              Then one in calculation in the rooms table that checks to see if reservation start and end dates are empty or the available calc is yes

                               

                              Then you can place in a list layout place the global fields and a button to perform scripted find for Is available (calc field) = Y

                               

                              my little test worked like a champ

                               

                              • 12. Re: Using Date Ranges to filter/find/exclude Date Ranges
                                aammondd

                                To explain my comment.

                                 

                                Reservations table

                                RoomID 

                                Start Date

                                End Date

                                Reserved in Range Start (Calc) Unstored = If ( StartDate  ≥ Glbl Search::First Date; (If (StartDate > Glbl Search::Last Date  ; "N" ; "Y" )) ; "N")

                                Reserved in Range End (Calc)  Unstored= If ( End Date  ≤ Glbl Search::Last Date; "Y" ; "N")

                                Is Available(calc)  Unstored= If ( Reserved In Range Start  = "N" and Reserved In Range End = "N"; "Y" ; "N" )

                                 

                                Rooms Table

                                RoomID

                                Desc

                                Is Available (calc)  Unstored = If ( (IsEmpty ( Reservations::StartDate )  and IsEmpty ( Reservations::End Date ))or Reservations::Is Available = "Y";"Y";"N")

                                 

                                Global Room Search

                                First Date

                                Last Date

                                 

                                You can put the search fields on a list layout with a search button to execute  the following script

                                 

                                Enter Find Mode

                                Set Field [Is Available;" = Y"]

                                Perform Find

                                 

                                Seems to work very well.

                                • 13. Re: Using Date Ranges to filter/find/exclude Date Ranges
                                  ptt555

                                  comment,

                                  i have your solution implemented & it's working well.

                                  you were right that i was searching on the wrong table to get the results i desired.

                                  i'm used to using "For each (select id from table1)..." type statements in this situation.   You clued me in to accomplish that in FM I need to control which table I'm searching on.  a lesson learned for the future and a 'duh' moment when i realized what you were telling me.

                                   

                                  aammondd,

                                  thanks as well. that looks like another solution that would accomplish what i need.

                                  that approach may come in handy some time in the future.

                                   

                                  Great advice & prompt help from you both really appreciated :)

                                   

                                  • 14. Re: Using Date Ranges to filter/find/exclude Date Ranges
                                    aammondd

                                    you are welcome

                                     

                                    Filemaker is incredibly flexible and creative solutions to problems is one of the reasons I really like it.