If function with dates

[wally buch]

I am trying to do the following: have a number appear in a field if a date is greater than a particular day. I have tried a calculation formula with e.g. =50 if Date>1/1/2007 but keep getting the message that an operator is required. Can anyone help?

[LaRetta]

A bit more information would help. You mean a date that you have in a date field? Or do you mean the current date? It appears that you want any date with a year greater than or equal to the current year? Is that correct? I hate to see you hard-code a specific date into a calculation. Anyway, you want a Case() statement, something like:

Case ( YourDateField :great: [color:blue]Date ( 1 ; 1 ; 2007) ; 50 )

But please provide more information so we can eliminate the portion in blue and turn your calc into something more generic and flexible.

By the way, welcome to FM Forums! You'll meet a lot of great people here! :laugh2:

Edited December 27, 2007 by Guest
Changed greater than to greater than or equal to

[wally buch]

Laretta;

Thanks for the rapid response. What I am trying to do is to determine if a late fee applies. For example, if the postmarked date for returning a form is greater than 8/11/08, then a 50 dollar late fee will apply. So, I am trying to create a "Late Fee" field that will return 50.00 if the field "Confirmation Date" is greater than 8/11/08.

Thanks so much again.

[LaRetta]

"Confirmation Date" is greater than 8/11/08. "

But how is that 8/11/08 determined? Is it x number of days past a shipping date? Order date? What is the criteria for determining that exact date? We want to use that criteria in the calculation instead of a straight date. So how many days late and according to which date field?

Edited December 27, 2007 by Guest

[wally buch]

No, the date is a date certain. Any application later than that date is assessed a late fee. So...you already solved it. I used the case function and it works like a charm! Having suffered trying to do this myself for a couple hours, it is humbling to realize I wasn't even close. I never would have thought of using the case function

[LaRetta]

Well actually, Wally, you WERE close by using the If() function which, except for a single limitation, functions exactly like Case(). In other words, the calc could have been written as:

If ( YourDateField ... just as well

The only difference is that If() is limited to two possible results (this or that) whereas Case() is unlimited. So we usually use Case() always because then, if we change our minds and want to add further results, we don't have to change the calc itself.

Welcome aboard. :wink2:

[jstaphse]

LaRetta, with the way you are answering questions, you should change your name to "Helpdesk" B)

[wally buch]

I wondered if one needed to have a second argument to make it work. I see the difference.

I may need more help in the future so it's nice to know there are knowledgeable people enthusiastic about helping.

Many thanks

wally

[LaRetta]

Nah, jstaphse, I'm a light-weight around here! There are people with three times the number of helpful posts!

Wally, I want to stress again that a specific date shouldn't be held within a calculation. Why? Because when that date rolls around, you will need a Developer to unlock the solution and change that date in your field definitions! And what if someone forgets to change it? Either change the date to Date ( 8 ; 11 ; Year ( Get ( CurrentDate ) + 1 ) ) ... or place that date in a field for Management to update from Browse mode. Hard-coding a specific date within a calculation is probably the number one gotcha in this business (maybe only second to flat-file structures or using repetitions for data).

[wally buch]

I guess I still dont fully understand. My calculation now looks like Late Fee =Case(Confirmation Date>Date(8;11;2008);50

How should it look to avoid the troubles you mentioned?

wally

[LaRetta]

Create a table (maybe called Globals or Preferences); lets call it Preferences. Have ONE record in it. Create a date field with name of LateFeeDate (standard field). Then create a calculation (result is date) called [color:blue]gLateFeeDate and just put this field (the LateFeeDate field) in the calculation box. Go to Storage Options and change this calculation to global. Place this standard date (LateFeeDate) somewhere where Management can change it. Insert your 8/11/2008 into it now. Then change your Late Fee calculation to:

Case(Confirmation Date > [color:blue]Preferences::gLateFeeDate ;5 0 )

This Preferences table doesn't need to EVER be joined to the rest of your tables but any fields in here (structured the same way as this Late Fee date) can be accessed from anywhere (because globals are cool and can be used anywhere). What this does is allows Management to change that date next year without having to call the Developer. All things within your solution which you might want to hard-code, put in here so they are easier to change. Because you (or whomever is the Developer) won't want to open up field definitions to the Business Manager ...

Edited December 27, 2007 by Guest

[wally buch]

Just a bit too complicated for my use. I am the "developer" so to speak. i think I will revert to a more manual approach until I understand it better. Your previous post using case date(8;11;Year (get(currentDate)+1));50) doesnt appear to work. It returns a positive argument no matter what the date is.

but thanks for your help anyway.

[LaRetta]

Yep, it would be:

Case ( LateFeeDate > Date ( 8 ; 11 ; Year ( Get ( CurrentDate ) ) + 1 ) ; 50 )

The odds of changing the calculation will lessen a bit that way at least. If you use Get ( CurrentDate ) within a calculation, be sure and make the results UNSTORED in your storage options or it won't update for you.

[comment]

I think LaRetta is asking what is so special about the date 8/11/2008 (I know I would). If this is for a one-time thing, then hard-coding the date into a calculation could work fine. But if there will be more events(?), each with its own deadline, you don't want to have that in the formula.

[wally buch]

It really is for a one time thing. The following year, the date will change, but that should be easy to do. I am unclear now as to whether the earlier " case" solution, which worked fine, is going to be a problem after the date arrives! I dont see why it would, but no way to test the condition.

[comment]

If there is a "following year", then it's not really a one time thing. And it may seem easy to do (to do what? change the formula?), but it will ruin your records for the by-now previous year: all your late fee charges will disappear.

[wally buch]

Good point, but the following year will be an entirely different event. However, I see the potential for conflict.

[comment]

the following year will be an entirely different event

But if the formula is the same, how can the solution tell them apart? That's the entire point here. You should have a table of Events, where each event has a ConfirmByDate field. Each Application should be related to a specific Event. Then your calculation can be:

Case ( ConfirmationDate > Events::ConfirmByDate ; 50 )

EDIT:

Actually, with inflation and stuff, the fee itself should also be an attribute of Event, so the calc becomes:

Case ( ConfirmationDate > Events::ConfirmByDate ; Events::LateFee )

Edited December 27, 2007 by Guest

[wally buch]

I will try and create a table.

[LaRetta]

I am not disagreeing with the approach at all but fees should be written as records against each customer because, what about forgiveness transactions? I mean ... that's when the Manager talks to that customer and the customer pleads their case and the sales manager says they will waive the fee for them? Are you then going to add another calculation field with flag (1 - for forgive)?

I could sum up my mistake here by not asking HOW the calculation would be used. It should be used to create transaction records against accounts. Just my opinion, of course ... that I hope there is a transactions table with LineItems in which these fees are written. :wink2:

[comment]

Are you then going to add another calculation field with flag (1 - for forgive)?

I might, if there's not much more to the solution (of which we know next to nothing). Your approach would be more appropriate if there are other types of transactions besides just application fee + optional late charge.

BTW, if there is no forgiveness mechanism, the manager can tell customer "look, I'm sorry - but once it's in the computer there's nothing I can do about it...". Then the developer gets a bonus!

[LaRetta]

the manager can tell customer "look, I'm sorry - but once it's in the computer there's nothing I can do about it...". Then the developer get a bonus!

Oh now THAT'S a great way to look at it! It's the computer - what to do? Ha ha! What's in YOUR wallet? :borg:

[LaRetta]

You got me laughing so hard that my accounting mind went out the window. Whatever method is used, run it by your Business Manager. They may care about when those fees are Posted against a GL. FM and accounting (AR) buck heads many times.

[comment]

Of course, if the applicant is attractive enough, the 'creative' manager will simply backdate the application. Then he gets found out by the developer who was smart enough to put an audit log in place (another bonus!). I feel there's a movie in this somewhere.

[LaRetta]

Sure, it'd make a good movie except the swooned Manager would first get caught by the bookkeeper when he 'backdated' the app because that would CERTAINLY put the 'attractive' customer past due! Three stars for the Manager for trying, though.

Heck, we don't even know if Wally HAS a bookkeeper so my Comment's may be moot (smile). I've no doubt that the book would be better anyway.

[Leather Knight]

Ok, I am following you...to a point. Mine is similar. I have a field called Purchase Date and one called Warranty Expires. Then the final field is Warranty Expired. I need to make it so "Warranty Expired" shown "Expired" in the field if the "Warranty Expires" is equal to or greater than the current date. If not equal to or greater than, it is blank. Any advice??

[LaRetta]

Hi Kevin,

I think you might mean that, if Warranty Expires is less than or equal to the current date, display Expired? If the Warranty Expires date is 12/22/2007 and the current date is 12/28/2007 then it is expired, right? If so, this calculation (result is number) for Warranty Expired would work:

Warranty Expires ≤ Get ( CurrentDate )

Then, at the layout level, select this calculation field and change its number format to boolean, show non-zero as Expired and leave zero or empty value blank. Also be sure to set this calculation (under Storage Options) as UNSTORED by checking the checkbox 'Do not store calculation results ...'

If that doesn't help you, let us know and we can tweak it for you.

UPDATE: Okay, okay, some people might want to use the calcultion itself for the word. If so here's the Case() alternative:

Case ( Warranty Expires ≤ Get ( CurrentDate ) ; "Expired" )

... but (just a personal impression), I think the boolean number would be faster AND would also provide non-equijoin possibilities (and probably other things which just don't come to mind at the moment) but text 'expired' would take more resources than a simple 1.

UPDATE 2: And it probably wouldn't expire until midnight of the current day so you might just want 'less than' instead of 'less than or equal to.'

UPDATE 3: Yes, there are many other reasons to use boolean and a 1 but I overtalk and I'm trying to change my ways.

LaRetta

Edited December 29, 2007 by Guest
Many updates added

[Leather Knight]

Ahh, I see what you are doing now. I did this:

If ( Get(CurrentDate) ≥ Warranty Expires ; "" ; "Warranty Expired" )

And that way if the warranty has not expired, it shows nothing at all, then when it does, it shows "Warranty Expired" in red letters on the layout. Wow, I am actually learning...go figure...LOL

Thanks

[Lee Smith]

You don't need the second result.

Case ( Warranty Expires ≤ Get ( CurrentDate ) ; "Warranty Expired" )

[Leather Knight]

Thanks Lee. it makes it cleaner, I like that.

[jadenguy]

i'd use 0 for forgive and then simply multiply the result by forgive, assuming the other choice is 1. or if(forgive) if it's not 1.

dunno if that makes sense to everyone.

[comment]

There could be a range of choices between 0 and 1.

[jadenguy]

wouldn't that allow for .5? i don't think there is a circumstance where .5 would somehow get entered, and am assuming there is would be script or procedure to create these forgivenesses, so if(forgive) seems like the safest choice.

after a bit of thought, perhaps expressing forgiveness as a percent bound between 0 and 1 would be even better! then you can partially forgive somebody. you know, "this is the second time this happened but i get that this time it was under more serious circumstances, so i'll knock half off." dunno if that ever comes up?

Edited January 4, 2008 by Guest

[comment]

i'll knock half off.

That's what I meant. Like allowing a salesperson to enter a discount on the total.

[LaRetta]

Like allowing a salesperson to enter a discount on the total.

I'm just being fussy. There is big difference between discount and Service Fee (or Late Fee). And mixing the two shouldn't happen (in MY book). Why? Because the Late Fee is an accounting charge/expense and a discount can directly affect salesperson' commissions. Things like this can seem small but, over the course of a year or even a quarter, small decisions such as this can make BIG differences!!

Now ... repeat after me ... "God I'm glad THAT woman isn't in OUR office." :giggle: