Jump to content

Date calc giving me trouble


This topic is 5690 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 5690 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.