Conditional formatting formula does not work

[kirvis]

Hi all,

I am working with a system which needs quite some reporting, so I have decided to use conditional formatting to create a nice overview. I have five fields in a column on my layout, and a "total" field. What I want to do is give the five fields a color based in their value:

First I determine the minimum value and the maximum value. The fields that have a value within the highest 20% between those values are red, the second highest 20% are orange, etc.

I use the following formula:

Self ≥ (

(

( ,8 )

*

(

Max ( field1 ; field2 ; field3 ; field4 ; field5 )

-

Min ( field1 ; field2 ; field3 ; field4 ; field5 )

)

)

+

(

Min ( field1 ; field2 ; field3 ; field4 ; field5 )

)

)

This is for the red fields. For the other colors I change the value of 0,8 to 0,6, 0,4, etc.

So in total, I have five conditions formulated for the fields.

Unfortunately, it does not work. The formula is correct as far as I can see, but the fields do not get the intended colorfill.

Anyone that can help?

Edited January 8, 2010 by Guest

[LaRetta]

I can't quite figure your calculation but conditional formatting does not work like Case() statements, ie, it evaluates all lines and selects the last true result. So your order should be .4, .6, .8. You can use the arrows next to your calculations to switch them around.

And no, I don't think it says this in FM Help. :smile2:

[kirvis]

I have clarified the calculation somewhat in my original post.

The essence of the calculation is the following:

self ≥ ( 0,8* ( max value of all fields - min value of all fields ) + (min value of all fields ) )

If these conditions are met, the fill color is red.

Example:

• five fields with values: 6, 7, 8, 9, 10
  
• max value is 10
• min value is 6
  
• field with value 10 will be red according to the above formula: 10-6=4 ; 4*0,8=3,2 ; 6+3,2=9,2
  
• that because 10 is higher than 9,2

The aim of all this is that I want the color scheme to be dynamic based on the maximum and minimum field values. There are five colors, to a value within a certain 20% of the difference between the max and the min value get one color.

Hope this makes it more clear.

I tried re-arranging the lines in the box, but that did not work.

[comment]

Why don't you post a file? Going by your description, it should work (once you have re-arranged the conditions in ascending order, as LaRetta suggested).

[kirvis]

I have made a small example file and attached it to this post.

When playing with it by changing the numbers, it still does not completely work. I do not see what exactly it does when I change the values.

conditional_formatting.fp7.zip

[kirvis]

Mmm, now it works in my sample file..

I changed the field type to "number" and it works. I just have to figure out now why it does not work in my original file, maybe a parenthesis too much or too little.

I have included the updated, functionally working version of the sample file as an attachment.

Thanks for the help!

conditional_formatting_functional.fp7.zip

[kirvis]

One other short question related to this conditional formatting: is it possible to disregard the value "0" (zero)?

I currently have some fields that are not populated with a value yet, which now automatically become green. I would like the formatting formula to disregard the zero's and leave the fields blank.

Is that possible?

[comment]

why it does not work in my original file, maybe a parenthesis too much or too little.

It doesn't work because Self is text, so the comparison:

Self ≥ x

is performed alphabetically, rather than numerically. It would have worked this way:

GetAsNumber ( Self ) ≥ x

but changing the field's type is probably a better choice.

is it possible to disregard the value "0" (zero)?

Yes, make your condition:

Self and Self ≥ x

---

P.S. I know parentheses are cheap, but really…

[kirvis]

Thanks for the help, it works like a charm now.

And indeed, it does not say in the Filemaker manual that conditional formatting works subsequently, so that you have to start with the lowest values first.

[comment]

it does not say in the Filemaker manual that conditional formatting works subsequently

Actually, it does:

"When you set multiple formatting conditions on an object, all conditions are evaluated starting from the top of the list. Each condition that evaluates “true” is appended to the objects previous format settings."

What it neglects to clarify is that a condition that evaluates “true” will override any previous conditions that apply the same type of formatting.