From Dwayne Wright - Certified FileMaker 9 Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
A READER ASKS
I need to create a calculation for creating a warning 6 months before a warranty expiry date is due. I have a field for the date of warranty expiry and have a merged field as the trigger for indicating the warning I just can't create the calculation to trigger the warning. Can you advise
-------
DWAYNE RESPONDS
Well, lets look at what our needed list of items before we start. We are going to need to b be able to capture the current date and compare it to the warranty date plus six months. Then we are going to need a warning field for when that date is passed. We are going to also need a way to turn the warning off or we will be in warning overload. After that, you might want to have some sort of scripted warning system.
BUILDING THE WARNING
Our warning field is going to need to deduct six months from the warranty date. For that, we are going to use the Date function and within that we will be using the month, day and year functions. The Date function allows you to take 3 parameters ( which can be a field, a calculated value, literal text or a combination thereof ) and make a proper date out of them. So we are going to subtract 6 from the month parameter and leave the others the same. Your three parameters are for the month, the day and the year.
So our calculation is going to look something like ... Date ( month ; day ; year )
will become
Date ( month(warrantydate) - 6 ; day(warrantydate) ; year(warrantydate) )
Here are some links to other posts that might be of interest in regards to this topic...
The Date Function
The Day Function
The Month Function
The Year Function
So now we can simply compare if the warning date is greater than or equal to the current date ... and ... our bypass field is not checked. Our bypass field can be used to say we have already sent the reminder, the warranty was extended, the warranty date has passed, etc... So that calculation may look something like ...
Case(
WarningBypass = "Yes", "N/A"
WarningCalculation ≥ Get(CurrentDate), "Warning",
"")
Here we test to see if the bypass is active and put a N/A in our warning field and the calculation stops. Otherwise we evaluate the next line in the Case statement to see if our warning calculation is greater than or equal to the current date. If this is the case, then our field will display the text of “warning”. If our bypass is not active and the warning date is still in the future, the field is empty.
SCRIPT TRIGGERS
Well, you can do any number of automatic, semi-automatic, manual and visual indicator processes from here. To have a script fire automatically, you will need a third party plug-in to enable FileMaker to fire off a script on its own. You can have a script that pull up the warning related records as part of the startup or shutdown process. Of course, you can have a regular button run the script as well. As far as visual indicators, conditional formatting can be use to alert the user of the impending warranty date condition.
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.
© 2008 - Dwayne Wright - dwaynewright.com
The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.
====================== ADVERTISEMENT ==============================
For more information on the Virtual One On One Training, please visit http://www.dwaynewright.com/training.html
===================================================================
Saturday, July 26, 2008 at 8:31AM 
