5 Replies Latest reply on Nov 5, 2012 12:33 PM by brian.curran

    Searching between StartDate/Time and EndDate/Time

    brian.curran

      Title

      Searching between StartDate/Time and EndDate/Time

      Post

           Hi,
           I'm trying to 'Find' a set of records between two dates including start and end times.

           I have an Events table which lists records with the following fields:
           Start Date
           Start Time
           End Date
           End Time

           I can retrieve records from Start Date to End Date but I want to narrow this found set down by time as well. For example, from 17:00 on the 05th of November to 05:00 on the 06th of November.

           Any suggestions? I've spent most of the day experimenting with Global fields, calculation Timestamps etc.

           Thanks
           Brian.

        • 1. Re: Searching between StartDate/Time and EndDate/Time
          davidanders

               Google "find timestamp range filemaker"   |   https://www.google.com/search?q=find+timestamp+range+filemaker

               Link #1   |   http://www.filemaker.com/11help/html/find_sort.5.8.html

          Home > Using FileMaker Pro > Finding, sorting, and replacing data > Finding records > Finding ranges of information

                                                                                                                                                                                                                                             
                              
                                   In the 3 o’clock hour today
                         
                              
          // 3 PM
                         
                              
                                   In the 7 o’clock hour in May 2007
                         
                              
          5/2007 7 PM
                         
                              
                                   That occur on a Monday in 2010
                         
                              
          =Mon 2010
                         
                              
                                   From the 10th through the 16th of November 2010 and from 3:00 PM to less than 6:00 PM
                         
                              
          11/{10..16}/2010 {3..5} PM
                         

                

          • 2. Re: Searching between StartDate/Time and EndDate/Time
            brian.curran

                 Hi David,
                 I saw that page earlier but didn't really understand it if I'm being honest blush I'm trying to perform the search on a field that is a calculation of the Date and Time fields, is that possible?

                 DateTimeStart = SampleEvents::DateStart + SampleEvents::TimeStart
                 Calculation result is 'Timestamp'

                 Thanks
                 Brian.

            • 3. Re: Searching between StartDate/Time and EndDate/Time
              brian.curran

                   Ok scratch that, my script now has:

                   Enter Find Mode []
                   Set Field [SampleEvents::DateTimeStart; "11/{11..12}/2012 {5..7} PM"]
                   Set Error Capture [On]
                   Perform Find []

                   This returns all records between the 11th and 12th of November 2012 with a start time between 17:00 and 19:59

                   So it works :) However, I would like to use my own parameters rather than hard code something into the script. I can create a Timestamp from two global fields for Date and Time:

                   gDate = 11/11/12
                   gTime = 19:00
                   cCalc = 11/11/2012 19:00

                   How would I convert this Timestamp to something that the Find Mode could use?

                   Thanks
                   Brian.

              • 4. Re: Searching between StartDate/Time and EndDate/Time
                brian.curran

                     Update, I tried this as a calculation in a Text field:

                     "{" & Month ( GlobalStartDate ) & ".." & Month ( GlobalEndDate ) & "}" & "/{" & Day ( GlobalStartDate ) & ".." & Day ( GlobalEndDate ) & "}/{" & Year ( GlobalStartDate ) & ".." & Year (GlobalEndDate) & "} {" & Hour ( GlobalStartTime ) & ".." & Hour ( GlobalEndTime ) & "}"

                     So if I type the following into my 4 global fields:

                     StartDate = 05/11/2012
                     StartTime = 17:00
                     EndDate = 06/01/2013
                     EndTime = 05:00

                     ... the calculation shown above generates:
                     {11..1}/{5..6}/{2012..2013} {17..5}

                     I then use this in my 'Find' script but it doesn't work, as all records are returned. Any suggestions?

                     Thanks
                     Brian.