Jump to content
Sign in to follow this  
MattHal

Calculations with CurrentDate

Recommended Posts

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 by Guest
Don't change the topic like that.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by Guest
fixed a typo

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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:

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.