Due Date Calculation

[AlanP]

I have a heck of a problem here:

I need a calculation that makes a test due by the 30th of a calender month. But it has to be before the employees certification expires.

For example:

- Employee was last recertified on 5/22/2004 and needs to be recertified by 5/22/2006

- We issue him a new test 90 days prior to his certification date expiring

- We need the due date to be at least 1 day prior to the expiry date, however it cannot be past the 1st (when we run all our reports on different systems, databases, etc.). I'll be happy with anything that will be due the 30th on the month prior (or something similar).

So If the employee must be recertified by 5/22/2006, we will need their test by 4/20/2006 (etc) and not go past the 1st (5/1/2006) when we run reports (otherwise the next time we run a report it will be 6/1/2006 and the employee will have already expired).

*any* ideas? I'm hoping it can just be some sort of calculation. Currently I'm using the calculation but realized it will not work because it will go past the 1st in many cases:

Last Certification Date + 715 )

Thanks in advance everyone!

[comment]

I got a little bit lost in your explanation, but this:

Date ( Month (DueDate) ; 0 ; Year (DueDate) )

will return the last day of the month before the month of DueDate.

Similarly,

Date ( Month (DueDate) - 3 ; 0 ; Year (DueDate) )

will return the last day of the month, 3 months earlier.

[AlanP]

I am always fascinated on how well you know this stuff comment. Thank you very much for your help! The first calculation was exactly what I was looking for!

[AlanP]

Ooops...I have one more question on this...if I wanted

Date (Month(DueDate);0;Year(DueDate))

to return a date 10 days prior to the last day of the month...what would I add to this calc? Would it be

Date ( Month (Date Certification Expires); -10 ; Year (Date Certification Expires) )

?

[comment]

Actually, -9 (since 0 is already 1 day before), but the principle is correct.

[John Mark Osborne]

While entering a zero (0) as the month may work, it clearly states in the online help that months must be a digit from 1 to 12. It is possible in a future version of FileMaker that a different result will be returned. To be safe, use a calculation like the following to locate the last day of the previous month:

Date(Month(date); 1; Year(date)) - 1

Similarly, use the following calculation to locate the last day of the current month:

Date(Month(DueDate) + 1; 1; Year(DueDate)) - 1

[Raybaudi]

Hi JMO

comment's calc don't "touch" the month... it "touch" the day and, since date are numbers,

Date (Month(DueDate)+1;0;Year(DueDate))

is perfectly the same of:

Date(Month(DueDate) + 1; 1; Year(DueDate)) - 1

[John Mark Osborne]

Yup, I meant to say Day. Still the same thing though. Days need to be a number from 1 to 31 as stated in the online help. I agree that it works and I think it is a nifty calculation. However, I would be concerned about recommending this calculation to people since the results returned could change in a future version of FileMaker since the values he is using fall outside the parameters for the Date function.

[comment]

It's true that the help clearly states that month is a number from 1 to 12 and day is a number from 1 to 31.

It's also true that on the same page there's a note:

"If you type a month greater than 12 or a day greater than the number of days in a month, FileMaker Pro adds the extra days or months to the result. For example, Date(13;1;2004) returns 1/1/2005."

So there's a contradiction right there - surely 13 is NOT "a number from 1 to 12". Granted, the note does not say anything about numbers smaller than 1. But it has worked this way ever since version 3 at least.

Moreover, the same formula using 0 as the day parameter in Date(), is recommended by FMI themselves in their knowledge base, with the following explanation:

"0" in the Day parameter calculates the "zeroeth" day of the month

Of course, in a future version FMI may decide to pull the rug from under ANY calculation. It has happened before: in any pre-7 version, Mod ( -7, 5 ) returns -2. The parameters for the Mod() function are described as "numeric expression or field containing a numeric expression".

There's nothing to suggest that computing the modulo of a negative number falls outside the parameters of the function. There was absolutely no reason to be "concerned about recommending this calculation to people since the results returned could change in a future version of FileMaker".

Nevertheless, starting from version 7, the result of Mod ( -7 ; 5 ) is 3 (mercifully), and all previous calculations broke. So what should we conclude from this?

[John Mark Osborne]

Good points! Thanks for the excellent discussion.

This sounds similar to the debate regarding IsValid versus IsEmpty when checking for child records from a parent. FileMaker, Inc. representatives say not to use IsValid because it may not work in the future (yet, it has worked since version 3). They say IsEmpty is a better choice. But, IsEmpty has more chance of failing than IsValid. For example, if you happen to specify a field that is empty on a related record, IsEmpty will not see the related record but IsValid will. So, I have decided to follow the advice of FMI and use IsEmpty, always making sure I check a primary or foreign key.

I still believe zero to be an invalid value for any of the parameters of the Date function. I see that your formula has returned the same result for many versions. But, if this is not an intended behavior based on the requirements for the Date function, it could change in the future. This is my only concern. I don't want people to use a calculation that could be a problem in the future when they can use a slightly longer formula and be sure it will work as long as the Date function persists.

In reference to the KnowledgeBase article at the filemaker.com web site, there are many mistakes. I should know. I am still the largest contributor to the KnowledgeBase from my days working in Claris technical support. I've made mistakes and have corrected many mistakes. Engineers don't review the articles, just the legal department and other technical support agents. If we really want a true answer, we need Shaun Flisakowski to repond to this thread since he is the lead engineer for the calculation engine. This is unlikely to happen though but let me try anyhow.

Edited May 24, 2006 by Guest

[comment]

Well, a word from the horse's mouth would be best. But I am convinced that the help page is poorly worded, and that this is indeed the intended behavior. FMI didn't invent the wheel here: it works in a similar fashion in Excel, and IIRC in JavaScript and other progamming/scripting languages too. In fact, this works so well that FMI would be out of their mind to mess with it.

Besides, if that article passed legal, then we're covered, no? If they ruin our calc, WE CAN SUE! :

[John Mark Osborne]

Yes, the entire online help is poorly worded and doesn't go far enough with examples to really help people understand what can be done with scripts and calculations.

After sleeping on it, I think you might be right about the intended behavior, although I would like someone at FMI to say so. I emailed Shaun but apparently he doesn't work there anymore. That's too bad because he introduced some of the great new calculations functions in FileMaker 7. Oh well.

Let me try calling Andy Lecates and see what he says.

[John Mark Osborne]

Amazingly, I got Andy on the phone and he says this is intended behavior because the Date function is designed to handle any integer value. Even though I was wrong, I'm glad we had this debate because now I have a deeper understanding of how the Date function works. Hopefully, this discussion helped others as well.

[comment]

Thanks for taking the time to put this to rest.

BTW, I wouldn't say the entire online help is poorly worded. At least when it comes to functions, the description is usually very precise. This may be a bit hard to read for someone who is not accustomed to definitions. But that is the only way to know what the function does (and what it doesn't). The Date() function is actually an exception - someone tried to dumb it down for the masses, and this is the result.

(The examples are horrible overall - there's no argument about that).

[John Mark Osborne]

Yes, the definitions are good but most of the examples in the online help don't really show you how you would use the function in a real-life solution. Even though this would take a lot of extra work to construct better examples, I think it would raise the bar of FileMaker knowledge. But, I shouldn't complain since I make my living from training materials.