4 Replies Latest reply on Feb 22, 2013 2:06 PM by flybynight

    Calculate Field Values on the Fly

    micinfo

      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

        • 1. Re: Calculate Field Values on the Fly
          MattLeach

          The reason being is due to the fact that nothing is changing in the Termination or Start date. The calculation will update once one of those fields has been modified.

           

          I could be completely off base here but a possibility would be to create a global calculation field and set the calculation as Get ( CurrentDate ). Lets call it CurrentDate. Your Elapsed Year calc can then reference that field:

           

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

           

          This way the Elapsed Years calculation should update every day as it is referencing a field that has been updated.

          • 2. Re: Calculate Field Values on the Fly
            DavidJondreau

            Phil, I suspect you field is not "unstored" but rather "unindexed". Under the "Storage Options..." make sure that "Do not store calculation results..." is checked.

             

            Matt's solution may actually produce the desired result, but in a very roundabout way...referencing a global field will force the calculation to be unstored. But you don't need the overhead of the global field or scripting it on start up.

            • 3. Re: Calculate Field Values on the Fly
              Vaughan

              micinfo wrote:

               

              Termination Date = "" 

               

              Use IsEmpty. Please.

               

              IsEmpty( Termination Date )

              • 4. Re: Calculate Field Values on the Fly
                flybynight

                Do these 2 methods not produce the same results? What is the advantage of using IsEmpty, rather than = ""?

                As a mostly self-taught FMP developer (now I'm working through the Training Series and some stuff on Lynda.com, hoping to get certified in the near future), always appreciate the opportunity to learn these little nuances… and why one method is the "right way" to do something.

                 

                Thanks!

                -Shawn