Expiration Date

[scarletjake89]

I need to have a field display whether a record is expired or not (i.e. Y or N). I have a field that shows the expiration date. I want the expiration field to display "N" if todays date is before or the same as the expiration date. When todays date is after the expiration date I want the expiration field to display "Y". I am new to FM and databases in general. Appreciate any help.

[Lee Smith]

Case(expiration field <= Status(CurrentDate), "No", "Yes")

[scarletjake89]

Thank you. It worked exactly how I wanted it.

[Vaughan]

Make sure the calc field is defined as "unstored" so it recalculates each time it's needed.

[Lee Smith]

I'm the one that usually catches that.

[scarletjake89]

Thanks guys, I just noticed that the fields didn't update the status as they became expired. I was going to post the question of how to do this. You beat me to it. Much appreciated.

[scarletjake89]

Well, guys my first expiration didn't automatically occur today. I was hoping the field would change from "NO" to "YES", when I logged onto the database this morning. To my disappointment it still read "YES". Is there a way to have the calculation checked automatically?

[Lee Smith]

It will not change until tomorrow.

Equal to or less than your Current Date = no

Greater than your Current Date = Yes

HTH

Lee

[Lee Smith]

If you need to have your current date included in the "Yes" category, then modify it by putting -1 behind the Status(CurrentDate).

Case(expiration field <= Status(CurrentDate) -1, "No", "Yes")

HTH

Lee

[scarletjake89]

I tried this and seems to work. During the launch of the database I have it running the following script:

Enter Browse Mode[]

Go to Layout [Refresh window, "Layout"]

Print Setup [Restore, No dialog]

If["Case(Expiration Date < Status(CurrentDate), Expired1 = "YES", "NO")

End If

Show All Records

I teseted it by changing the date on my computer back and forth and it worked.

I am hopeful, I have finally solved this problem so I can move on to the next.

Thanks for your help.

[Lee Smith]

Oops,

Yes, it is better to use the < instead.

I would make Expire1 a calculation field instead of setting it by script.

That way you don't have to remember to run it your script

Expire1 = Calculation, Text Result =

Case(Expiration Date < Status(CurrentDate), "NO", "YES")

HTH

Lee

[scarletjake89]

I have both.

For the When Opening "DATABASE" script I have:

Enter Browse Mode[]

Go to Layout [Refresh window, "Layout"]

Print Setup [Restore, No dialog]

If["Case(Expiration Date < Status(CurrentDate), Expired1 = "YES", "NO")

End If

Show All Records

And for the Expired1 field, I have made it a calcualtion field, using the formula you provided:

Case(Expiration Date < Status(CurrentDate), "NO", "YES")

I don't know if this is redundant. But the database is automatically updated when it is launched using the When opening script. If a previous NO, should now be a YES, the script automatically changes the No to a YES when database is launched.

Then if someone enters a new record and inputs the new expiration date, the calculation field then lets you know it's expiration status, the YES or NO immediately.

I hope I haven't made this more confusing than it should be.

[Lee Smith]

Redundant? - No, the script shouldn't be working at ALL.

You can't set a calculation field using a script, or manually. In other words, you should get a warning that "This Field Can't be Altered" and Beep).

Remove the script.

Lee

[scarletjake89]

It is working, perfectly in fact, so far. No warnig signs or beeps. I'm not sure why, if it is true what you are saying. I no doubt it is. I don't believe I forgot to explain or add steps to what I have done. But it is working.

[Lee Smith]

Okay, you are correct - it doesn't give a warning when you use the If statement. I guess that's because the If Statement gives it an out if the condition isn't met. If you used the Set Field instead, you will see what I mean. Easier yet, try to type something in the field.

Trust me, the Script isn't doing anything.

Lee

[scarletjake89]

I believe you. I am trying different things to see. And I am quite sure you are right. But, can't learn unless you try different things.

[Lee Smith]

I agree that one of the best ways to learn, is by trying different things and seeing how the work. I just noticed that you have used both the "If" and the "Case" statement in your script step. The "IF" has two separate meanings in FileMaker, which are explained with examples in FileMaker's Online Help. Simply type "IF" in the Index Box and you will see what I mean.

As used in a Calculation Field, the If Statement (Function) is described as: "Returns one of two possible results depending on the value of test. If test is True (any nonzero result), FileMaker Pro returns result one. If test is False(0), result two is returned. Test must be an expression that returns either a numeric or Boolean (True, False) result.

Note If you have more than two possible results, consider using the Case function."

Example:

If(expiration field < Status(CurrentDate), "NO", "YES")

As used in a Script, the If script step is described as: "Evaluates a Boolean calculation and performs a conditional action based on that evaluation. If the calculation result is not zero, the calculation evaluates to True and the subsequent script steps are executed. If the calculation result is zero, the calculation evaluates to False and the subsequent script steps are not executed.

Every If step must have a corresponding End If step somewhere after the If step and at the same indentation level. Whenever you use an If script step, ScriptMaker enters an End If step automatically.

You can also add an additional condition by using the Else step.

Note If you do not specify a calculation or if the calculation is unsuccessful, it will evaluate as True.".

As an example

Allow User Abort [On]

Go to Layout [Refresh window,

[John Caballero]

I'm curious as to what, if anything, is supposed to happen between your If and EndIf statement?

[Lee Smith]

Sorry, I don't understand your question.

[dbruggmann]

Lee

I think John asks not you, but osubuckeye (about his post 88846):

osubuckeye said:

For the When Opening "DATABASE" script I have:

Enter Browse Mode[]

Go to Layout [Refresh window, "Layout"]

Print Setup [Restore, No dialog]

If["Case(Expiration Date < Status(CurrentDate), Expired1 = "YES", "NO")

End If

Show All Records

There's nothing happening inbetween the If and End if

[John Caballero]

Eggslactly.

[scarletjake89]

Sorry guys, I had to drive home from work. Well I was trying to get the automatic update to the Expired1 field to occur, which wasn't happening. I wrote that script thinking (grasping) it would do the trick. Upon further review, I am agreeing with Lee that nothing was happening. Through all the attempts at trying to get it to work, it is working. As to why it wasn't working before I am not sure. It is now. I want to thank all for helping out. I am, like I said in the beginning, brand new to the world of databases and haven't had to deal with logic statements since college, ages ago.

[kenneth2k1]

So, you got rid of the script, right?

[scarletjake89]

Yes