6 Replies Latest reply on Sep 2, 2013 4:08 AM by RehmanAkram

    Calculation field question



      Calculation field question


      My question is: I have a calc field set up to calculate days delayed from the target due date. I need the calculation to stop calculating if there is a date in the completed field. How do I do this? I have tried some things but they don't work.

      My calc so far is set up like this and it works except for it doesn't stop calculating days delayed: 

      Days delayed field calc = If(GetAsNumber(Target Date) > 0 and GetAsNumber(Target Date) <= GetAsNumber(Get(CurrentDate));GetAsNumber(Get(CurrentDate))-Target Date; "")

      I am using FileMaker Pro 10 on a Mac. The database is located on FileMaker Server. 

        • 1. Re: Calculation field question

          Try this:

          If (  IsEmpty ( Completed Date ) and not IsEmpty ( Target Date ) and Target Date ≤ Get ( CurrentDate );  
           Get ( CurrentDate ) - Target Date; 
           "" )

          Do not store calculation results so that the current date updates correctly.

          There is no need to use the GetAsNumber function. FileMaker will handle the coercion of a date to a number for the calculations.

          TS_Oz, FileMaker Inc.

          • 2. Re: Calculation field question

            Thank you very much for you assistance.

            Is there any way to get it to not clear out the days delayed once you put a completion date in? I need the days delayed to still show how any days the work was overdue for reports even once it has been closed with a completion date. 

            I am still trying to learn the calculations and what is proper to use.

            • 3. Re: Calculation field question

              OK, so the logic will be clearer if you use a Let statement. I have commented (//) the end of each line to explain. These comments are not required but serve to document my/your logic.

              Try this:

              Let ([

                target_exists = not IsEmpty ( Target Date ); // Boolean/logical test

                refdate = If ( not IsEmpty ( Completed Date ); Completed Date; Get ( CurrentDate ) ); // uses the completed date if it exists, otherwise uses today's date

                overdue = ( Target Date ≤ refdate ) // Boolean test of whether the task is or was overdue


                If ( target_exists and overdue; refdate - Target Date; "" ) // simplified expression of the final logic


              See how that works for you.

              • 4. Re: Calculation field question

                Let's see if we understand using an example:

                Today is 10/29/2010
                Target date is 10/20/2010 ... meaning it is now 9 days overdue

                If there is not yet a Completed Date then DaysDelayed should equal 9 because it is still active and will continue to count upwards as days pass. 

                But if you then fill in Completed Date with 10/22, DaysDelayed should change to 2 since it ended up only being 2 days overdue (even though Completed Date wasn't entered until few days later) and the calculation should then stop accumulating days.  Is this correct?  If so, try:

                Case ( Target Date   ≤  Get ( CurrentDate ) ;  Case ( not Completed Date ; Get ( CurrentDate ) ; Completed Date ) - Target Date )

                If this is not correct, please revise the dates example so we can see the correlation and adjust the calc accordingly. :^)

                NB:  Boolean logic does not need the IsEmpty() test ... Boolean looks for any non-zero numeric value and since all dates have numbers, any date resolves to 1 (true).  So not Completed Date is Boolean and Target Date is Boolean ... all by themselves.

                UPDATE: I corrected the calc - changing 'today' to Get ( CurrentDate ).  In my date test file, I always use a global date called Today so I can test different 'today' dates for various date calculations.  Then when I present a date calc, I change all places of Today to Get ( CurrentDate).  I had neglected to change it in one place.  It is clear that bate01 recognized that and corrected it accordingly.

                • 5. Re: Calculation field question

                  Thank you both very much for the help!

                  LaRetta - you are correct in what you wrote. It is exactly what I need to happen. Your calc works perfectly too. 

                  Thanks again for the responses.

                  • 6. Re: Calculation field question

                         I have a same problem but i am using filemaker pro advanced, how can i solve that