AnsweredAssumed Answered

Calculate Field Values on the Fly

Question asked by micinfo on Feb 21, 2013
Latest reply on Feb 22, 2013 by flybynight

Question about FMPA11 on a Mac w/ OS 10.7?

 

We want to create a calculation field (data type is "number") that auto-updates the number of Elapsed Years that have transpired after the Start Date of an event. The calculation looks at two fields: "Start Date" and "Termination Date".

 

If the Termination Date is empty (i.e., the "event" is still commencing), then we subtract Start Date from the CurrentDate (divided by 365.25) to calculate the number of Elapsed Years according to the following simple Case statement:

 

Elapsed Years = Case ( Termination Date = "" ; (Get(CurrentDate) - Start Date) / 365.25 ; (Termination Date - Start Date) / 365.25)

 

The problem is that the value of Elapsed Years does not auto-update as time passes! This is true regardless of whether Termination Date is populated or empty.

 

So the question is, how does one structure a field def to automatically recalculate the passage of time on any given day without requiring any manual, or scripted, intervention? (e.g., is there another form of the Case statement that will force the calculation whenever that field is referenced?)

 

 

Please note the following:

 

1. The field definition is UNSTORED; no index is ever created.

 

2. The field does calculate correctly when it's first defined, but it does not continue to calculate as the days, weeks, and months go by. It just stays the exact number of years and never increments as time passes.

 

3. If I click in the Start Date or Termination Date field and simply retype what's in there, the field recalculates to the correct value (but Replace'ing a value with itself has no effect!). But I need it calculate on the fly so that whenever someone goes to that record they will see the correct Elapsed Time.

 

 

Thanks so much for your consideration!

 

Phil

Outcomes