5 Replies Latest reply on Oct 20, 2016 2:28 PM by doughemi

    Months left till expiry date

    lee423

      I have not used Filemaker for a few years and quite rusty with databases in general.

      I have offered to help a sailing charity by writing a small database that will flag up crews qualifications that are due to expire.

      What they would like to see is the course name, issue date, expiry date and months left till expiry.

      What I thought might be nice to is if the letting can turn orange if there is 6 months left and red if there are 3months left.

      I'm not sure if you can get email notifications on this or is there is any other smart solution.

       

      Seeing as this is a charity, I would really like to help the best I can, so any help will be very well received.

       

      Lee

        • 1. Re: Months left till expiry date
          keywords

          The following calc will give an approximation to 2 decimal places of the time left till expiry date:

          Let (

            [

             expiry = GetAsDate ( "20/1/2017" ) //substitute your field name here

            ; now = Get ( CurrentDate )

            ; expMonth = Month ( expiry )

            ; currentMonth = Month ( now )

            ; daysLeft = expiry - now

            ; monthsLeft = Round ( daysLeft / 30 ; 2 )

            ] ;

          monthsLeft

          )

           

          This could be used in a field called, say, timeToExpiry.

          You could reference this calc to address both questions you raise—

          1.     In a series of Conditional Formatting conditions, use the result to specify the colour of the field

          2.     In a script, you could build a routine that finds records where timeToExpiry is less than 3 and then sends them a reminder email.

          • 2. Re: Months left till expiry date
            lee423

            Thank you so much, I will give it a go

            • 3. Re: Months left till expiry date
              taylorsharpe

              Keywords calculation generally is pretty acceptable, but there can be with one particular caveat that months are not all 30 days.  Depending on how you want to do things, you could make the two dates into a month number such as ( YYYY * 12 ) + ( MM ) and then subtract one from the other.   You have to decide on the rounding up or down for how many months are left, etc.  It really depends on what result you are looking for (e.g., if its less than a month, are there 0 months less left or 1 as in you're in the last month?). 

               

              Dates can always be an interesting challenge in databases.  But a few good examples of FM date calculations can be found at:  Date Calculations in Filemaker - BurnIgnorance   Sometimes seeing examples helps me figure out like here. 

              • 4. Re: Months left till expiry date
                lee423

                Thank you so much for  your help, I will take a the information that you have kindly sent me.

                Regards

                Lee

                • 5. Re: Months left till expiry date
                  doughemi

                  Make MonthsLeft = Month(ExpDate - Get(CurrentDate)) - 1 (result: number)

                   

                  Use conditional formatting to change the background color. Set the conditions to:

                   

                  Value ≤ 6 // set fill color orange

                  Value ≤ 3 // set fill color red

                   

                  You can write a script to send an email. In File->File Options->Script triggers, check OnFirstWindowOpen.  Pseudocode for the script would look something like:

                  # create fields 6moWarningFlag and 3moWarningFlag

                  if (MonthsLeft <7 and isEmpty(6moWarningFlag)

                  Send Mail // set message to "only 6 months left!"

                  Set field [6moWarningFlag; 1]

                  End if

                  if (MonthsLeft <4 and isEmpty(3moWarningFlag)

                  Send Mail // set message to "Getting Really Close!"

                  Set field [3moWarningFlag; 1]

                  End if