4 Replies Latest reply on Feb 7, 2012 1:18 PM by SB_1

    Find all records with a booking on a date x days in the future

    SB_1

      Title

      Find all records with a booking on a date x days in the future

      Post

      HI

      I have a database with many records each relating to a participant in a trial. My aim is to send them appointment reminders 3 days prior to their visits. All their booking dates are contained in one table. 

      I have found how to find people with a booking date either in the future, today or in the past. Also I can find those within a range of fixed dates. I cannot however figure out how to find only those subjects with a booking date 3 days from today as today is always changing as the year goes on.

      Todays date needs to be entered automatically to avoid user error. I have made a field in the same table so each record has a cell with todays date + 3 in it. This field is updated every day prior to the trying to find those who are due in 3 days. When I quick find for records where the Booking Date field = Todays Date + 3 field I get alot of records where those fields don't match. 

      There is probably some simple soloution I have missed. 

      Anyones help would be much appreciated.

      Cheers

        • 1. Re: Find all records with a booking on a date x days in the future
          philmodjunk

          Use a script:

          Enter Find Mode[]
          Set field [ Yourtable::YourDateField ; Get ( CurrentDate ) + 1 ]
          Set Error Capture [on]
          Perform Find []

          • 2. Re: Find all records with a booking on a date x days in the future
            SB_1

            In my current script I have:

            Enter Find Mode[]
            Set field [ Subjects::FutureDate ; Get ( CurrentDate ) + 3 ]
            Set Error Capture [on]
            Perform Find []

             

            What criteria do I use for the find?

            I am trying to find the records with the fields Subjects::BookingDate that is the same as Subjects::FutureDate

            I try the criteria FutureDate == BookingDate but it says the value of this field must be a valid date so I cannot have a date field.

            So what criteria would I use as the find?

             

            Cheers

             

            • 3. Re: Find all records with a booking on a date x days in the future
              philmodjunk

              You indicated that you wanted to use find records with a date exactly 3 days in the future from today in your original post. To do so, does not require using a future date field.

              Your script would be:

              Enter Find Mode[]
              Set field [ Subjects::BookingDate ; Get ( CurrentDate ) + 3 ]
              Set Error Capture [on]
              Perform Find []

              What criteria do I use for the find?

              Set field [ Subjects::BookingDate ; Get ( CurrentDate ) + 3 ]

              specifies teh criteria for the find. Get ( currentDate ) returns today's date as indicated by your computer's system clock. Adding three computes a date that is three days in the future. (Dates are really number fields that record the number of days elapsed from 12/31/0000 to the date specified in that field so you can use them as number fields in calculations.)

              • 4. Re: Find all records with a booking on a date x days in the future
                SB_1

                Thanks for your help. That makes sense now. I didnt really know how find mode worked with scripts and using the Get ( CurrentDate) feature. 

                I was previously under the assumtion I would need a field with the future date in it and somehow find all the records where the booking date fields matched the future date field.

                 

                Thanks for your help