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

    Date Calculation

    Ben_B94

      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.

       

      Thanks

        • 1. Re: Date Calculation
          taylorsharpe

          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
            ninja

            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
              Ben_B94

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

              • 4. Re: Date Calculation
                schamblee

                GetAsDate(Month(examDate)&"/"&Day(examDate)&"/"&Year(examDate)+1)

                • 5. Re: Date Calculation
                  Magnus Fransson

                  Hi,

                   

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

                   

                  Best regards Magnus Fransson.

                  • 6. Re: Date Calculation
                    mgeerdes

                    Hi,

                     

                    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
                      ninja

                      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
                        Ben_B94

                        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
                          Ben_B94

                          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

                           

                          loop

                          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
                            siplus

                            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
                              ninja

                              just sketching out logic...shortcutting syntax:

                               

                              go to layout

                              show all records

                              go to first record

                               

                              Loop

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

                                If (OptOut field = "No")

                                  Send mail

                                  Set (emailsentfield ; Get(CurrentDate) )

                                EndIf

                              EndIf

                              Got to next record, exit after last

                              EndLoop

                               

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

                              • 12. Re: Date Calculation
                                erolst

                                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
                                  erolst

                                  schamblee wrote:

                                  GetAsDate(Month(examDate)&"/"&Day(examDate)&"/"&Year(examDate)+1)

                                  See above.

                                  • 14. Re: Date Calculation
                                    Ben_B94

                                    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?

                                     

                                    Thanks

                                    1 2 Previous Next