4 Replies Latest reply on May 17, 2012 4:00 AM by GuyStevens

    search for 6 months either side of a date

    clairebushell

      Title

      search for 6 months either side of a date

      Post

      i have a feild contract expiry date and i want to write a script to search for records eipiry 6 months before or after the current (todays) date. how do i do this? 

       

      then ideally i want to generate an email alert when a contract exiry date in with in 6 months of the current date and the email be sent once a month automatically - can that be done??

      i'm using Filemaker 12 but am quite new to it. 

      thanks in advance 

        • 1. Re: search for 6 months either side of a date
          philmodjunk

          i'm using Filemaker 12 but am quite new to it.

          Do you know how to create scripts in FileMaker yet?

          I'll give you the manual method for finding records in a date range, but once you learn how to create scripts, you can use a script for this to save time and avoid mistakes in setting up the find criteria. Such scripts could also automate the email alert process so that you need only click a button to send out the emails to all the individuals with expiry dates in the specified range.

          Enter find mode.

          In your expiry date field enter a date range such as 12/1/2011...11/30/2012

          Perform your find.

          You can do a similar find for your email task and then use Send Mail from the File Menu to send out an email for every record in your found set. There's a "multiple emails (one for every record in found set)" option you will find useful for this.

          • 2. Re: search for 6 months either side of a date
            clairebushell

            Thanks, yes i am getting to grips with scripts

            is it possible though to add it to the scrit to search 6 months before and after the currrent date so you dont have to adjust the range every time? 

            • 3. Re: search for 6 months either side of a date
              GuyStevens

              Sure, you can calculate the dates.

              In your find script you would do a "set field" script step that sets the date range.
              As a calculation for this script step use:

              Date ( Month (Get (CurrentDate)  ) - 6 ; Day ( Get (CurrentDate) ) ; Year ( Get (CurrentDate) ) ) & "..." & Date ( Month (Get (CurrentDate) ) + 6  ; Day ( Get (CurrentDate) ) ; Year ( Get (CurrentDate) ) )

               

              That should set the current date - 6 months as the first date. Then it adds tne "..." sign to search a date range untill the current date + 6 months.

              • 4. Re: search for 6 months either side of a date
                GuyStevens

                So your script would be:

                Enter Find Mode // uncheck the pauze button
                Set Field (here you set the calculation to the field of the expiration date)
                Perform Find

                 

                And voila, should work.

                Add additional steps to the script if needed (like sorting)