Calculation to determine if record is active

[Conartist]

I am very new to FM and scripting so I'm sure this is a simple question. I thought I was writing this calculation correctly but it's not working so I guess I'm missing the big picture. My solution involves tracking conventions. What I want is a way to mark each convention record as either active or not active. A record would go to an inactive state 7 days after the end date of the convention. This allows time for final invoices to be created, etc. What I have done is create a field in the convention record that is called ActiveStatus. Then I've defined this field as a calculation. The calculation that I thought would work is this one:

If ( End Date + 7 < GetCurrentDate ) ; "Not Active" ; "Active" )

I'm thinking that my basic understanding of the If function is wacked and that's why this isn't working. I appreciate the help.

[CobaltSky]

Hello Conartist,

Is the calculation field you are referring to *unstored*?

If it isn't, try making it unstored and see if that improves matters. ???

[Raybaudi]

Also you have to change a little your calc...

the right one is:

If ( End Date + 7 < Get(CurrentDate) ; "Not Active" ; "Active" )

[Fenton]

Get( CurrentDate ) will only evaluate correctly in a calculation if you choose "Do not store" in the Storage options.

This means that it is kind of slow for Finds; it's really only useful for display. You should set the criteria directly for Finds. To Find "active":

Enter Find Mode []

Set Field [ End Date , ">=" & (Get( CurrentDate ) - 7) ]

Perform Find []

(P.S. You can use Set Field and dates this way in 7, not in 6.)

[Conartist]

Urggh. Sorry guys. I forgot to set my field as a calculation field. I got so focused on the scripting part of it. It's working great now.

[Conartist]

This realization brings up a point I guess I don't understand.

What is the difference between a field that is defined as "text" but has a calculated auto enter value and a field that is defined as a "calculation"?

Why don't they produce the same result?

[CobaltSky]

This realization brings up a point I guess I don't understand.

What is the difference between a field that is defined as "text" but has a calculated auto enter value and a field that is defined as a "calculation"?

Why don't they produce the same result?

Their behavior is different. Notably (in this case), a text field is always stored, whether it has an auto-enter calc inm place or not, whereas a calculation field can be unstored.

But there are other differences. Eg. a text field with an auto-enter calc can be manually edited (effectively over-riding the calc result) whereas a calc field is not user-editable.

[stanley]

Conartist:

To add to Ray's comments, a text field can be defined by a calculation on record creation, then can be altered by other calculations in future. A calc field is limited to one calculation (although, via the Case[] statement, that calculation can be quite complex.)

Also, if your calculation is changed in the future, a text field will maintain its original value, while an unstored calc field will change to reflect the new formula...

-Stanley

[-Queue-]

Furthermore, if 'do not replace existing value' is deselected, the text field with an auto-enter calculation will update when one of its referenced fields is changed, as long as it is local. An unstored calculation will always update, regardless of where its referenced fields are located.

Any calculation which uses a Get function must be unstored if you wish for the result to update automatically.

[CobaltSky]

Conartist:

To add to Ray's comments, a text field can be defined by a calculation on record creation, then can be altered by other calculations in future. A calc field is limited to one calculation...

Let's not confuse the issue here, Stanley.

Both a calc field and a text field with an auto-enter calc option will update automatically if any field that their formulae reference (in the same table) is changed. In this respect they are the same...

...unless the option labelled "do not replace existing value of field (if any)" is selected for the auto-enter calc - in which case it is the auto-enter calc which might be said to be 'limited to one calculation' and the regular calc field which will continue to evaluate automatically as the data changes. :