5 Replies Latest reply on Jun 1, 2010 12:29 PM by LaRetta_1

    Trouble with a calculation field

    bate01

      Title

      Trouble with a calculation field

      Post

      I am a fairly new user so please bear with me.

      I am working in FileMaker Pro 10 on a Mac using OS X

       

      I am having problems getting a calculation to work.

       

      I have a field called Days delayed (number) that is a calc field, the fields it is to calc are Target date (date field) and Completion date (date field).

      What I need it to do is calc based on if there is a value in the Completion date field then it is to subtract from the Target Date field to get days delayed. If the Completion date field is empty then Target Date is to subtract from the Current Date to get days delayed. If it is not delayed then the result should be 0.

       

      I almost hasd it working but it was giving me a negative if the completion date - target date wasn't delayed so I went to adjust the calc and broke it.

       

      Thank you very much for your assistance.

       

       

        • 1. Re: Trouble with a calculation field
          comment_1

          Try =

          Max ( Case ( CompletionDate ; CompletionDate ; Get (CurrentDate) ) - TargetDate ; 0 )

          The calculation must be unstored.

          • 2. Re: Trouble with a calculation field
            bate01

            Thank you! That worked great!:smileyhappy:

             

            The one thing that it is doing is putting a number (733924) in the field Days Delayed if both the Target Date and the Completion Date fields are empty. How do I make it either a 0 or blank?

             

            Sometimes, the people that do the data entry don't always enter the dates right away. I am afraid this number will confuse them.

             

            Thank you!

            • 3. Re: Trouble with a calculation field
              philmodjunk

              Hmmm, one would think that such a calculation would not evalute if both date fields are empty and the check box "Do not evalute if all referenced fields are empty" is selected, but my tests with a sample file reveal that if this calculation is set to be Unstored as it must be, then the expression does evaluate.

               

              This small modification eliminates the problem:

               

              If ( not IsEmpty ( CompletionDate ) ; Max ( Case ( CompletionDate ; CompletionDate ; Get (CurrentDate) ) - TargetDate ; 0 ) ; "" )

               

              The last parameter of the IF function ( "" ) is optional and can be omitted if you prefer.

              • 4. Re: Trouble with a calculation field
                bate01

                Thank you very much! That worked. The first solution works fine as long as there is data in either field. I was surprised by the number in the days delayed field too.

                 

                • 5. Re: Trouble with a calculation field
                  LaRetta_1

                  I believe that is related to the bug posted as message 19 in Known Bugs which states:

                   

                  Aggregate functions force calc field to evaluate when it shouldn't

                   

                  Versions: Filemaker 10.x
                  Operating Systems: Mac, Windows

                  Risk: Moderate

                   

                  Description: Min ( InputField ; 100 )
                  returns 100 when InputField is empty - even though the option 'Do not evaluate if all referenced fields are empty' is turned on.

                   

                  http://forum-en.filemaker.com/fm/board/message?boa rd.id=aut&message.id=3432

                   

                  ... which STILL isn't fixed in vs. 11, it seems.

                   

                  :smileyhappy: