1 2 Previous Next 22 Replies Latest reply on May 26, 2016 6:39 AM by Ben_B94

    Date Calculation


      I have a date field for the date of the last exam done, its a drop down calendar. All I want is to work out is, if more than 1 year has passed since that date then do something else do nothing.


      if ( get(currentDate) >  exam date + 365 )?


      send mail to alert someone


      else do nothing


      Also how would you set this up so it only sends 1 email. I would want it to check each day(schedule run on filemaker server?) But if its more than 365 days I wouldn't want to send an email for every day after 1 year.



        • 1. Re: Date Calculation

          You can make a script trigger for On Field Exit from the last exam done that runs and has an IF test to see if it is more than a year and if so, send an email.  The send mail script step will require you to put in your SMTP server info.  You can also have an On Field Enter script trigger to record the date and the On Field Exit to test if the data has changed since you entered the field and only if it has changed to send an email reminder.  Or you can have another field that you store the date of whenever the script sends an email and you can have the script to test and see if an email has been sent today, then don't send the email a second time.  Basically can you script just about anything if you work out what you want it to do. 

          • 2. Re: Date Calculation

            Also note that 365 days is not always a year,,,,


            GetAsDate (Month(examdate) ; Day(examdate) ; Year(examdate) + 1) might be a better test...


            Picky, but a bit more accurate.

            • 3. Re: Date Calculation

              It complains of too many parameters when I try to use that date function

              • 4. Re: Date Calculation


                • 5. Re: Date Calculation
                  Magnus Fransson



                  I assume you meant to use the Date(month;day;year) function?


                  Best regards Magnus Fransson.

                  • 6. Re: Date Calculation



                    Yes, I would set this up as a scheduled script on server that runs in the morning. The other have give you alternatives to use for the year calculation. I would add a field and call it something like eMailSent. Then in the script that loops through your records, if it finds one that needs to send an email, have it set the eMailSent field to 1. At the beginning of the server script, do a find for anything that is not eMailSent.


                    Hope this helps.

                    • 7. Re: Date Calculation

                      Its a good approach if you want to limit to one email.  I would then add another script trigger on modification of the exam date field to clear the "1" if they retake the exam.


                      Another way to play it is to set a date in the "emailsent" field...instead of just a "1"

                      Then your check can see if the exam is out of date, and when the last email was sent, and maybe send another...every month? every quarter? etc.

                      • 8. Re: Date Calculation

                        Hmm I don't think I've fully thought this through, your right as what if we get an email and nothing is done. It would be good to send another reminder.

                        • 9. Re: Date Calculation

                          go to layout [Vehicle Details]

                          show all records

                          go to record/request[first]


                          set var [$list : Value: Get(TotalRecordCount) ]

                          set var [$counter ; value: 1 ]


                          if( get(currentdate) > 1month from $emailSent, then resend mail? not sure how to write this



                          if(GetAsDate (month( examdate)&"/"&day(examdate)&"/"&year(examdate) + 1) ---- this will check for over 1 year later?

                          send mail / smtp details etc etc

                          set var [$emailSent value: Get(currentdate); // when/where/how can i check if todays date > 1 month from the email sent date

                          $counter + 1

                          go to record request next:(exit after last? or no)

                          exit loop if [counter > $list]

                          • 10. Re: Date Calculation

                            You do need an EmailSent Field, autoenter 0 which becomes 1 on sending an email, but you also need to reset it to 0 if the person takes another exam, think about it.

                            • 11. Re: Date Calculation

                              just sketching out logic...shortcutting syntax:


                              go to layout

                              show all records

                              go to first record



                              If (Get Current Date > Test date + a year and Get(CurrentDate) > emailsentfield + a month)

                                If (OptOut field = "No")

                                  Send mail

                                  Set (emailsentfield ; Get(CurrentDate) )



                              Got to next record, exit after last



                              (I added an additional "OptOut" field for people who want to stop the spam....)

                              • 12. Re: Date Calculation

                                Eric Twiname wrote:


                                Also note that 365 days is not always a year,,,,


                                GetAsDate (Month(examdate) ; Day(examdate) ; Year(examdate) + 1) might be a better test...


                                Picky, but a bit more accurate.


                                Maybe more accurate semantically, but wrong syntactically; simply use


                                Date ( m ; d ; y + 1 )

                                • 13. Re: Date Calculation

                                  schamblee wrote:


                                  See above.

                                  • 14. Re: Date Calculation

                                    Hi Erolst,


                                    just to clarify, when you say Date( m ; d ; y +1), I have lastExam field as date format, I would simply put lastExam( m ; d ; y+1) to check for a year later from that date?



                                    1 2 Previous Next