Comparing dates and auto filling another field based on the result

[James50]

Hi !
I hope I explain this well enough for someone to help me. I have a field (MembExpDate) that contains a membership date - such as 02/20/2010. I would like to check the membership date against the current date to see if it is plus or minus 30 days and enter $30 into another field (Dues_Amount) if it is and leave it blank if it isn't. This is what I've tried but it doesn't work. This is probably obvious to everyone except me!

Dues_Amount
Case ( Get (CurrentDate) - MembExpDate < 1 ; "" ;
Get (CurrentDate) - MembExpDate > 1 ; 30 )

Any help would be greatly appreciated.
Thanks,
Jim

[AHunter3]

I don't see anything wrong with your formula; the one thing you need to know about calc fields that reference "status" functions such as Get (CurrentDate) or Get (SystemPlatform) and so forth is that you have to set the storage (via the storage tab in the field definition of that field) to "unstored — calculate as needed"; if you do not, it will not notice when the "current date" changes; it will not roll over and therefore will not "work".

This means you can't use such a field on the remote side (portal side, right hand side, etc) of a relationship because an unstored field cannot be indexed. But as long as that was not your intention (e.g., you intended to do a standard FIND for expired dates, or just to have them display onscreen), that change by itself should put you in business.

[James50]

AHunter3,
I didn't have it set to unstored but even when I make that change it doesn't work. The funny thing is I have 2 records that have the dates of:
11/03/2008 and
11/22/2009
These show the $30.00 amount in the proper field like I want. Others that have Feb. 7, 2010 and such show blank. I realize I'm missing something but I can't figure out what it is.
Thanks for your help. Any other suggestions?
Jim

[AHunter3]

I have 2 records that have the dates of:
11/03/2008 and
11/22/2009
These show the $30.00 amount in the proper field like I want.

Yes, as I would expect.

Others that have Feb. 7, 2010 and such show blank.

I would expect that as well.

I realize I'm missing something but I can't figure out what it is.

I am realizing that *I* am missing something: an example of a date that gives you a result that you did not expect or that you consider to be wrong?

[James50]

AHunter3,
I got it to work! I had to change the formula to this:
Case(MembExpDate - Get (CurrentDate) < 30; "30";
Get (CurrentDate)- MembExpDate > 30; "30")
I'm not sure I completely understand why but when I check it out using different data it works. If you see a major flaw in this please let me know.
Thanks a lot,
Jim