5 Replies Latest reply on Jul 10, 2009 11:50 AM by TSGal

    updating calculations

    kaz_1

      Title

      updating calculations

      Post

      Hey,

       

      I wanted a way to calculate the amount of time passed between two entered dates, one of which is "current date". I used the following instructions and they worked very well. However, they only calculate the difference in date when you enter the second date. Is there any way that I can set this calculation to run every day so the difference remains accurate as time progresses.

       

      Thanks

       

      FileMaker Pro has several powerful date functions, which make it possible to determine the number of years, months and days that have elapsed between two dates. This is useful in many ways. For instance, if you had a database that you kept track of your customers. Given a date that a person became a Customer and today's date, we can determine the length of time that they have been a Customer.

      The following calculation fields are required for the final Calculation. These calculations compare the value in an existing date field called "Customer Since" with the value obtained using the Status(CurrentDate) function.

      Create the following fields:

      Customer_Since (Date Field. Auto-enter creation date)

      Number_of_Days (calculation, number result):
      Get(CurrentDate) - Customer_Since

      Month_Length (calculation, text result):
      Case(Month(Customer_Since) = "1", "31", Month(Customer_Since) = "2" , "28" , Month(Customer_Since) = "3" , "31" , Month(Customer_Since) = "4" , "30" , Month(Customer_Since) = "5" , "31" , Month(Customer_Since) = "6" , "30" , Month(Customer_Since) = "7" , "31" , Month(Customer_Since) = "8" , "31" , Month(Customer_Since) = "9" , "30" , Month(Customer_Since) = "10" , "31" , Month(Customer_Since) = "11" , "30" , Month(Customer_Since) = "12" , "31" )

      Using the calculations above we can know calculate the length of time someone has been a customer by Years, Months, and Days. Please note that the calculations listed above are not meant to be used on the layout. It is only necessary for these fields to exist in the Define Fields. The following uses the calculated values above to display the length of time the person has been a customer in the text format of "yy Years, mm Months, dd Days":

      Length_as_a_Customer (calculation, number result):
      (Int( Number_of_Days / 365.25 )) & " Years " & If((Round( Mod( Number_of_Days , 30.4375 ) , 0 )) <= Month_Length and ((Int(Number_of_Days / 30.4375))) < "1" , "0" , ((Int(Number_of_Days / 30.4375) - (12 * (Int( Number_of_Days / 365.25 ))))))
      & " Months " & (Round( Mod( Number_of_Days , 30.4375 ) , 0 )) & " Days"

      Dividing by 365.25 in the year calculation takes into account leap year. When calculating the months we are first checking to see if the number of days is greater than the number of days in the current month. If the number of days is less than the number of days in the current month and If the number of months is also less than 1, then months is equal 0. The last section of the month's calculation calculates the total number of months and subtracts 12 months for every year. We divide number of days by 30.4375 as an average number of days in a month (365.25/12 = 30.4375).










        • 1. Re: updating calculations
          comment_1
            

          Any calculation that uses Get(CurrentDate) must be UNSTORED. Unstored calculations recalculate whenever the window is refreshed.

           

          ---

          NOTE: I haven't got a clue what your calculation does, and I believe it deserves second thought.

          • 2. Re: updating calculations
            kaz_1
               I have the values for the amount of time passed between the two dates displayed in a portal on another table. will making the calculation unstored allow the portal to display the correct time passed day to day, or would the specific record have to be viewed for the value to change?
            • 3. Re: updating calculations
              comment_1
                 Unstored calculation can be viewed in a portal without problem. However, they cannot be used as matchfields when defining the relationship.
              • 4. Re: updating calculations
                etripoli
                   If you want the current date updated everyday, just setup a server side script to update a date global field, or add the date global field update to the login script.
                • 5. Re: updating calculations
                  TSGal

                  kaz:

                   

                  Thank you for your posts.

                   

                  The Month_Length calculation can be reduced to:

                   

                  Day ( Date ( Month ( Customer_Since ) + 1 ; 1 ; Year ( Get (CurrentDate) ) ) - 1 )

                   

                  That is, go to the first day of the following month and subtract one day.  This puts you at the last day of the desired month, and it takes into account leap years.

                   

                  TSGal

                  FileMaker, Inc.