The following calc will give an approximation to 2 decimal places of the time left till expiry date:
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 )
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.
Thank you so much, I will give it a go
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.
Thank you so much for your help, I will take a the information that you have kindly sent me.
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]
if (MonthsLeft <4 and isEmpty(3moWarningFlag)
Send Mail // set message to "Getting Really Close!"
Set field [3moWarningFlag; 1]