6 Replies Latest reply on Aug 17, 2015 4:09 AM by erolst

    Conditional Formatting of a calculation field

    brianj77

      Hi

      This sounds a really dumba__ question for  really basic facility which I can’t seem to get to work

       

      I have a ‘Time Sheet’ type application

       

      The time sheet  has two fields with number (hours) and a third calculating the difference between the two (positive or negative).

       

      The calculation field simply subtracts field B from field A to display an integer

       

      I need the calculation field to simply display the calculation result (number) but also with some conditional formatting with the following logic

       

      If A=B - then no action: display the number in default state

      If A-B is positive then display the number in colour green and bold

      If A-B is negative then display the result in colour red and bold

       

      I cant get a way to make this simple requirement work and its driving me crazy: such a simple facility.

       

      Can anyone please point me in the right direction

       

      Thanks for your help. I appreciate you taking the time to read this and answer my question

      Brian

        • 1. Re: Conditional Formatting of a calculation field
          raybaudi

          You'll need to set two conditions:

           

          Formula is: Self < 0 [ bold and red ]

          Formula is: Self > 0 [ bold and green ]

          • 2. Re: Conditional Formatting of a calculation field
            ucharles

            I think the Case function is a much better function than the if, in that it does not need to be nested like the IF function. The Case statement on the other hands goes to on to evaluate conditions and apply results as needed.

             

            ie, Case ( test1 ; defaultResult {; test2 ; result2 ; ... ; defaultResult} )

             

            Case ( A=B ;result1 {;

                      A-B>0; Green and Bold ;

                      A-B<0; Colour red and bold} )

            • 3. Re: Conditional Formatting of a calculation field
              erolst

              ucharles wrote:

               

              I think the Case function is a much better function than the if, in that it does not need to be nested like the IF function.

              I only use Case(), but - to play Devil's Advocate – if you have a simple one-line predicate, then If() is two characters shorter …

               

              That being said: neither Case() nor If() is appropriate here, because you cannot calculate the formatting* (like in your example) – you need two distinct Boolean results to trigger hardcoded format settings, as shown by Daniele.

               

              *which would be very neat!

              • 4. Re: Conditional Formatting of a calculation field
                electon

                If it's the field that has to be formatted then you should have access to the Value is greater than, equal to, contains etc... settings.

                There you can specify one or more conditions with respective formatting.

                I don't think you need to write a formula, just choose from drop downs.

                One format per line, you can add as many formats as you need.

                For default where conditions are not met, format the field in the inspector as you want.

                • 5. Re: Conditional Formatting of a calculation field
                  brianj77

                  Hi Erolst

                  Thanks for your response. Very helpful.

                  I do get the logic - pretty straightforward.

                   

                  I guess my issue is just as you wrote: the fine detail in Filemaker about formatting the fields.

                   

                  I have used Filemaker a lot but managed (incredulously!) without using Functions so I struggle with their use and application. My logic is OK but my technique is poor.

                   

                  At the risk of being a total numpty could you please explain the solution in a wee bit more detail ie the formatting of the fields too

                  Thanks for your patience

                  Brian

                  • 6. Re: Conditional Formatting of a calculation field
                    erolst

                    Understand the difference between

                     

                    1. using functions to calculate a field format – which then becomes part of your data

                     

                    2. Use functions to calculate if a condition is true, and, if so, apply a set of hard-coded, pre-selected formatting options; that is what Conditional Formatting does, and is only applies to that field object.

                     

                    Step by step you'd have to:

                    • Right-click the field and select Conditional Formatting

                    • Add one condition per … well, condition that you want to trigger a change in formatting

                    For simple comparisons concerning only that field itself, you can use “Value is”; for anything more complex, select “Calculation is”

                    • in your case, you could get along with two “Values is” conditions:

                    1. less than 0, 2. greater than 0

                    For each condition, select and specify the formatting options to be applied.

                    1 of 1 people found this helpful