Expiry Date Confusion

[h123]

Ok here we go again

In one of my tables I have a date field that has a drop down calendar attached to it to enter a date. The field is named consent_expiry

I want to add some hidden text or similar next to the field on the layout that will show the following messages when the dates are nearly or have expired.

When the consent_expiry date has 30 days left to go the hidden text will show

“The consent for this record will expire in “however many days are left”.

Or if the consent_expiry date has passed then the hidden text will show

“The consent for this record has expired and needs to be renewed”

Can this be easy achieved? Advice much appreciated.

Thanks in advance.

[mr_vodka]

Let ( d = consent_expiry - Get (CurrentDate );

Case (d ≤ 0; "The consent for this record has expired and needs to be renewed"; d ≤ 30; "The consent for this record will expire in " & d & " day"& Case (d>1; "s") )

)

[jrr316]

A calculation with a case statement should do it.

Case (Get ( CurrentDate) ≤ consent_expiry ; "The consent for this record will expire in " & DaysLeft_Expiry & " days.";

Get ( CurrentDate) ≥ consent_expiry ; "The consent for this record has expired and needs to be renewed" ;

"DEFAULT" )

I also created a calc field for DaysLeft_Expiry.

Attached is a demo. It is not pretty, but it works :)

Date_Calc.fp7.zip

[LaRetta]

The calculation must be unstored.

[jrr316]

Yes, sorry, forgot to check that.

[h123]

Thank you very much for the replys mr_vodka and jrr316.

I am very new to FM so please take no offence mr_vodka when I say I have decided to go with jrr316's solution. Much easy for me to understand. I am very gratefull for your reply.

That will do me very nice jr thank you very much.

h

[mr_vodka]

Let ( d = consent_expiry - Get (CurrentDate );

Case (d ≤ 0; "The consent for this record has expired and needs to be renewed"; d ≤ 30; "The consent for this record will expire in " & d & " day"& Case (d>1; "s") )

)

Well let me explain it to you then.

The Let just assigned the value of the difference from your consent_expiry field and the current date (consent_expiry - Get (CurrentDate ) ) to a temp variable I call "d".

Then the case statements evaluates in the ORDER that it is in the formula. So it first check if (d) the difference is less than or equal to zero. If it is it displays: "The consent for this record has expired and needs to be renewed"

If its not true, then it next checks to see if (d) the difference is less than or equal to 30. If so, then it will display: "The consent for this record will expire in ...

But since you can not say, record will expire in 1 DAYS, you cant just put in "days" but rather you need to put in a check to see if (d) the difference is only one. If it is not only one, then its a plural and it puts in the "s". So we have, Case (d>1; "s"). So, if there is only 1 record it will say, "The consent for this record will expire in 1 DAY"

In aything is greater than 30, then it doesnt display anything.

[h123]

Thank you for the explanation mr_vodka.

I got it to work and it does what I asked. Great.

Another example to add to my little folder to help me out again in the future.

Much appreciated

h

[Sipa]

A calculation with a case statement should do it.

Case (Get ( CurrentDate) ≤ consent_expiry ; "The consent for this record will expire in " & DaysLeft_Expiry & " days.";

Get ( CurrentDate) ≥ consent_expiry ; "The consent for this record has expired and needs to be renewed" ;

"DEFAULT" )

I also created a calc field for DaysLeft_Expiry.

Attached is a demo. It is not pretty, but it works

Thanks for this remarkable calculation which works with my solution too. I am using this calculation on my Driver's license and renewal purpose were they have more then ten different license to keep track of which is got different time period and some of them are in Years.

Question for you:

1) How do I calculate yearly and monthly based using the same calculation?

2) is their any way I might give colour to the text or date itself which is expiring in due course.

[jrr316]

You could do a calc field with the following:

If ( GetAsNumber (Year ( consent_expiry )) - GetAsNumber (Year ( Get ( CurrentDate ) )) ≥ "1" ; GetAsNumber (Year ( consent_expiry )) - GetAsNumber (Year ( Get ( CurrentDate ) )) & " Years " ; "" ) &

If ( GetAsNumber (Month ( consent_expiry )) - GetAsNumber (Month ( Get ( CurrentDate ) )) ≥ "1" ; GetAsNumber (Month ( consent_expiry )) - GetAsNumber (Month ( Get ( CurrentDate ) )) & " Months " ; "" ) &

If ( GetAsNumber (Day ( consent_expiry )) - GetAsNumber (Day ( Get ( CurrentDate ) )) ≥ "1" ; GetAsNumber (Day ( consent_expiry )) - GetAsNumber (Day ( Get ( CurrentDate ) )) & " Days" ; "" )

It is rough and would need some finer tweaking for dates that have expired, but it seemed to work.

For the color coding, you could stack fields similar the the Filemaker BPK Solution.

Date_Calc.fp7.zip

[comment]

So, if today is December 31, and the record is set to expire on the following day, according to your calc there's still 1 year left...

[jrr316]

Like I said not perfect, math needs to be worked out a little more.

[comment]

I am afraid I haven't made my point quite clear - see here.

[jrr316]

I see your point and don't really care....

Just trying to help someone out...

[LaRetta]

jrr316, you don't really care if your calculation is a YEAR OFF? No problem ... anyone can respond on the Forums and we can all make mistakes. But to NOT care, considering such an error can damage, is not okay. A person posting may use one of your responses to determine what an employee gets paid or when someone can retire! This is not a game. Do you think Sipa will like getting a ticket and THEN finding out his Driver's License expired a year ago? Thank you, Comment, for stepping forward on this one.

I assume you were just being defensive about your error and that you DO care. But someone that DOES care may not always be here to catch your mistakes. Or did you make that statement in jest? If so, it would help if you told us you were joking because, as it stands, it doesn't reflect well.

LaRetta

[jrr316]

I do care and I was kidding in part. When I posted the calculation I stated:

"It is rough and would need some finer tweaking for dates that have expired, but it seemed to work."

So if comment wants an ego stroke for calling me out on my calculation (which I already said was not done) consider it stroked.

And Sipa, I am sorry I was not able to solve this for you but hope I gave you at least some place to start.

Best,

Jrr

Edited January 24, 2007 by Guest

[LaRetta]

So if comment wants an ego stroke for calling me out on my calculation (which I already said was not done) consider it stroked.

WHOA. We get no ego strokes for correcting someone. Got that? You should thank him. I will bet that Sipa thanks him as well. Why post a calculation that you know does not work?

[comment]

Now I know for sure you have missed my point: this calculation CANNOT be done. It is not a matter of tweaking - the entire concept is meaningless. You may not care about this, but others might.

I would prefer you'd refrain from personal remarks. In any case, if you think my ego can be stroked by pointing out a fault in your calculation, you may be flattering yourself a bit too much.

[LaRetta]

would prefer you'd refrain from personal remarks.

As Moderator, that should have been my line. My apology for not stating it right off the bat. Personal attacks are NOT okay on FM Forums, jrr, and it is clearly stated in the rules. You may wish to read them.

[jrr316]

Apologies all around, I have read the rules and my comment was out of line.

Sorry to offend,

Humbly

JRR