2 Replies Latest reply on Dec 3, 2010 10:33 AM by AnnaSchmid

    Script for setting up a date alert

    AnnaSchmid

      Title

      Script for setting up a date alert

      Post

      Hi! I'm trying to set up a script that will send an email alert notification when the date in a certain field (DOB) is equal to the current date. I'm new to FM and would be grateful for any help :)

      Anna

        • 1. Re: Script for setting up a date alert
          philmodjunk

          Do you know how to write scripts?

          You can write a script set to run each time the file is opened to find any records where this email needs to be sent. This script can be made sophisticated enough that you can open and close the file several times in one day but only send one email out.

          Will the date of birth field ever be equal to to today's date? I think you want the month and day to match the month and day of today's date here.

          For relatively small numbers of records, you might define a "Birthday" field:

          Date (Month ( DOB ) ; Day ( DOB ) ; Year (Get ( CurrentDate ) ) )

          This calculation must be set as an Unstored calculation.

          Define an extra date field, eMailSent to keep from sending the same email twice.

          Your script might look like this:

          Enter Find Mode []
          Set Field [YourTable::Birthday ; Get ( CurrentDate ) ]
          Set Field [YourTable::eMailSent ; "< " & Get ( CurrentDate ) ]
          Set Error Capture [on]
          Perform Find[]
          If [ Get ( FoundCount ) > 0 /* records were found */ ]
              Send Mail // set up your email here, there's an option that lets you send the same email to all the records in your found set
              Replace Field Contents [no dialog ; YourTable::eMailSent ; Get ( CurrentDate ) ]
          End If

          For faster searching of larger numbers of records, you can define two number fields in place of the Birthday calculation:

          cMonth   Month ( DOB )
          cDay     Day ( DOB )

          These should be stored, indexed fields

          Replace step 2 with these two steps:

          Set Field [YourTable::cMonth ; Month ( Get ( CurrentDate ) ) ]
          Set Field [YourTable::cDay ; Day ( Get ( CurrentDate ) ) ]

          Since these two fields are indexed, your finds will perform more quickly--especially if you have a large number of records in the table being searched.

          • 2. Re: Script for setting up a date alert
            AnnaSchmid

            Thanks for the fast response!!