3 Replies Latest reply on Apr 15, 2009 7:35 AM by comment_1

    Calculation or Script?



      Calculation or Script?


      I am a new user to FMP 10 and I'm having so difficulty with a particular part of my project. Can you please advise me on how to complete the following problem:

      I have a date field i.e.. 04/15/2009 I would like it to email me with a message when it becomes more than 7 days in the future, and keep emailing me daily until I stop it. The question is it a script? Calculation? How do I stop it, and what is the syntax that I must use to complete this? Thanks. 

        • 1. Re: Calculation or Script?

          Hi miker85085. The answer is, you need both.


          You need fields to hold in the database the actual dates, and calculations to "Flag" out who and when to start notifying and when to stop.


          You also need the scripts to update some date fields in every record when you start up and the actual process of "Selection" and notification.


          It's easy, first you need a date field to hold the "Today's Date", Filemaker 6 had a function called like this, but after 6 it was eliminated and you need to create it. Make a Date Field called something like "Today_date", index it and make sure is not Global.  You will have to create a "Start Up" Script ( A normal script that it's called from the Document's preferences) That will do the following:


          Show All Records

          Go to Record/Request/Page [First]

          Set Field ["Today's Date", "Status(CurrentDate)"]

          Replace Contents [No dialog, "Today's Date", "Current contents"]

           Exit Record/Request


          This will tell all the records the actual date. Now, make two calculations, the first is called Notification_date and make it add "Your_date_field" + 7 this will calculate the date when you should start notifying. The second will be a "Notify_flag" that will compare the Notification_date with the Today_date and will determine which records will need to be notified, Notify_flag= If ( Notification_date > Today_date, "Yes", "No") and make it an indexed:text field. Finally, we need a last piece of information, we need a field that will tell the database when to stop sending the emails and this will be your desicion if you want to use just a text field in which  you write or select a pull down option saying "Stop" or a date field to enter when to stop. Let's use a text field called Stop_field and make it an indexed:text field. Now modify the Notify_flag:


          Notify_flag = If ( (Notification_date > Today_date) and Stop_field ≠ "Stop", "Yes", "No")


          That is all you need in the fields part. Now you need to modify the Start up script if you want the process to be automatic  or create a button with a script assigned if you want to manually process the notification, any way make the script, commit any changes, select the flag records and start the notification process.


          Exit Record/Request

          Enter Find Mode [ ]

          Set Field ["Notify_flag", "Yes"]

          Perform Find [Replace Found Set]


          Selection made, continue from here...


          I hope all this helps you..

          • 2. Re: Calculation or Script?
               Ja. The winking faces were a funny mistake. The system replaced the characters " ) , into ").
            • 3. Re: Calculation or Script?
                 A calculation calculates, it does not "do" anything. If you want an e-mail to be sent, you need a script - and you need something to trigger the script. You could include this as part of your startup script (see File > File Options…) that would be run every time the file is opened.*

              You don't need any calculation fields for this. The script could be something like:

              If [ YourDateField + 7 > Get (CurrentDate) and IsEmpty ( YourStopField ) ]
              Send Mail
              End If

              It's not clear where your date field is and whether this applies to multiple records. If so, you would want to find the applicable records first, then send mail for all/each record in the found set.

              (*) This assumes that the file is opened every day, and that the file is not being served/shared (or you would get a mail every time a user logs into the file)