3 Replies Latest reply on Aug 13, 2009 8:28 AM by philmodjunk

    Formatting a Field

    Dr.D

      Title

      Formatting a Field

      Post

      I am running into difficulty to get a formula to work properly, please offer your wisdom to help !!!

       

      Field Name = Total Score

      Type = Calculation

       

      The formula works fine -> Sum CS + Sum FM + Sum LM + Sum C + Sum QC + Sum F

      It correctly sums up the Total Score...

       

       

      However when I tried to build one more test, by doing

       

      Case ( Sum CS + Sum FM + Sum LM + Sum C + Sum QC + Sum F ;

      Score C1 = "No" ; Total Score = 0 ; )

       

      This does not work...

       

      What I am trying to do is, Total Score is the sum of those fields, however if the data in Score C1 is select as " No " then, no matter what the score of the others, Total Score should result in 0...

       

      Could someone please offer a solution, please and thanks !!!!!!!

       

       

       

       

       

       

       

       

        • 1. Re: Formatting a Field
          philmodjunk
            

          You need to rearrange your case function:

           

          Case ( Score C1 = "No" ; 0  ; Sum CS + Sum FM + Sum LM + Sum C + Sum QC + Sum F )

          • 2. Re: Formatting a Field
            Dr.D
              

            Thanks for the suggestion, it works !!!

             

            May I understand why the order of the case function make such a different??   Thanks for teaching me !!!  

            • 3. Re: Formatting a Field
              philmodjunk
                

              The case function is set up so that you use pairs of parameters with the option of an "else" parameter at the end:

               

              Case (logical expression 1 ; Calculation 1; Logical expression 2 ; Calculation 2 ; .... Else Calculation )

               

              Case will evaluate each logical expression from left to right and return the value of the Calculation immediately after the first logical expression that evaluates to True.

               

              In your case statement, you only have one such logical expression: Score C1 = "No"   If this is true. then zero is returned. If it is not, then the second calculation (your "else" parameter) will be evaluated: Sum CS + Sum FM + Sum LM + Sum C + Sum QC + Sum F )

               

              Since you have only one logical expression, If ( Score C1 = "No" ; 0  ; Sum CS + Sum FM + Sum LM + Sum C + Sum QC + Sum F ), will return an identical result.

               

              For more on these functions and others, I suggest reading the help file that comes with filemaker.