Date calc giving me trouble

[muzz]

I have a FM6 database which calculates a date 6 months from the date on which a client is referred ("refdate") and then warns whether or not the referral is currently active or expired. This calculation works for me:

[color:gray]Case(

DateToText(Status(CurrentDate) < Date(Month(refdate) + 6, Day(refdate)- 1, Year(refdate))),

DateToText(Date(Month(refdate) + 6, Day(refdate) - 1, Year(refdate))),

DateToText(Status(CurrentDate) = Date(Month(refdate) + 6, Day(refdate)- 1, Year(refdate))),

"TODAY",

"EXPIRED")

However from May 1st 2006 the referral remains active for 12 months rather than 6. I need to identify the expiry dates of currently active referrals pre-May 1 (i.e. 6 months from referral) as well as those after that date (i.e. 12 months from referral). So far I haven't been able to get things to work and hoped someone could give me a hand. It may be obvious tomorrow but it's been a long week and a late night.

Thanks for any help you can give.

Muzz

[comment]

You should remove all the DateToText() conversions.

First, a TEST returns a boolean result - either 0 or 1 - so there's no sense in converting it from a date (which it isn't) to text (which it shouldn't be).

Second, when the RESULT is a date, and the calculation result is specified to be Text, Filemaker will do the conversion for you implicitly.

So it should be:

Case(

Status(CurrentDate) < Date( Month(refdate) + 6, Day(refdate)- 1, Year(refdate) ),

Date( Month(refdate) + 6, Day(refdate) - 1, Year(refdate) ),

Status(CurrentDate) = Date( Month(refdate) + 6, Day(refdate)- 1, Year(refdate) ),

"TODAY",

"EXPIRED"

)

That said, I don't see any reason why your calc should turn bad precisely on May 1 2006 - or any other date.

[Søren Dyhr]

I gave you calc a whirl as well, and thought I would do something to the vast amount of almost identical calcs:

Choose(Sign(Status( CurrentDate ) - Date(Month(refdate) + 6, Day(refdate)- 1, Year(refdate)))+1, DateToText(Date(Month(refdate) + 6, Day(refdate)- 1, Year(refdate))) ,

"Today",

"Expired")

And believe me, it matters if you put a call to a plugin in the final branch of the Case( will it in your version still evaluate although the condition never is met...

Having said that, must it be stressed that this behaviour is changed from ver. 7.

--sd

[comment]

Yes, I've been spoiled by Let().

BTW, your DateToText() is also not required.

[muzz]

Thanks for your help so far with this, but I'm not sure you've answered my question.

However untidy this calc is (I have a lot of trouble with dates for some reason) it works, and it will still work after May 1st 2006, but from that date onwards any NEW referrals I receive should have their expiry date set to 12 months from the time I get them, while all the previous referrals up to that May 1st 2006 still need to have their expiry dates calculated as 6 months hence.

So I need a new calc which acts on or after May 1st 2006, as well as this one which continues to work on the active referrals that I received before May 1st 2006. I could do it with an If statement calc in another field, but I thought I would try to combine it all into one calc to "stretch" my capabilities, but so far I haven't been able to get it to work.

Thanks again for your help.

Cheers, Muzz

[LaRetta]

Well, trust Comment on date calcs. But you have hit a point where different Clients may have different referral lengths so you might consider a duration field per Client. Then you won't run into this again or won't be expanding the calc each time there are exceptions. Because, believe me, Eddie Murphy, Jr. is always waiting to step into our designs. :wink2:

[comment]

I need a new calc

Ah, now it's clearer. I believe this should give you what you want:

Choose(

Sign(Status(CurrentDate) - Date(Month(refdate) + 6 + 6 * (refdate ≥ Date(5, 1, 2006)), Day(refdate) - 1, Year(refdate))) + 1,

Date( Month(refdate) + 6 + 6 * (refdate ≥ Date(5, 1, 2006)), Day(refdate) - 1, Year(refdate) ) ,

"Today",

"Expired"

)

The calculation needs to be unstored, and the result should be set to Text.

LaRetta is right about hard-coding data into calculations, though.

[muzz]

Thanks heaps for that - I don't think I could have "stretched" myself that far. I really do need to get my head around the date issues I run into - as soon as I try to work with ranges or differences, I get pretty tied up.

I'll give it a go.

Cheers, Muzz