Calculations with CurrentDate

[MattHal]

I am creating a database for calibration of instruments. The instruments need to be calibrated every 1, 2 or 3 years depending on the instrument type. I want to have a field that will display "DUE within 60 days" when an instrument is due for calibration in the following 60 days. Another field would display OVERDUE if the instrument is not calibrated by its due date.

I have tried creating a field that contains the current date using Get (CurrentDate). Then using this field in calculations to prompt the given messages but this does not seem to work. Could anyone show what calculation I should be using or is their is a simpler way of achieving this operation?

Any help would be greatly appreciated.

Edited June 20, 2008 by Guest
Don't change the topic like that.

[Vaughan]

The calculation that uses a Get() function needs to be unstored (in the Storage tab) to make FMP re-calculate the value each time it's needed. Otherwise the "current date" will permanently remain the day the field was defined.

[MattHal]

In the Storage tab, Use Global Storage is not selected. I'm not sure what Indexing is (it is in the Storage tab also).

In Indexing, I have selected NONE and I have also selected AUTOMATICALLY CREATE INDEXES AS NEEDED.

Any other ideas?

[Vaughan]

The problem, then is that you're setting an auto-enter calculation into a date (or text) field.

Change the field type to calculation and specify the Get( CurrentDate ) function. Set the calculation field's storage to unstored.

[comment]

You don't need a field to hold the current date, because the Get (CurrentDate) function can be used directly in the calculation. However, any calculation that relies on current date must be in a CALCULATION field, and the field must be unstored. You get this option when you define the calculation field's formula, under Storage Options....

The formula itself could be something like =

Let ( [

nextDate = Date ( Month ( CalibrationDate ) ; Day ( CalibrationDate ) ; Year ( CalibrationDate ) + Period ) ;

today = Get (CurrentDate)

] ;

Case ( 

today > nextDate ; "OVERDUE" ;

nextDate - today ≤ 60 ; "Due within 60 days"

)

)

where CalibrationDate is the date of last calibration and Period is the required number of years between calibrations.

Edited June 20, 2008 by Guest
fixed a typo

[MattHal]

Wow, that was too simple (putting the fields as Calculations instead of Text or Number or Date).

In the code you show, what does period stand for?

I have a similar calculation done up, but Im just wondering why you added period?

[comment]

The instruments need to be calibrated every 1, 2 or 3 years depending on the instrument type.

Period is the required number of years between calibrations.

:qwery:

[MattHal]

Excellent. Thank you very much for the help. All works exactly as I wanted it to.