I have a field "XY" which collects values such as "A", "B" or "C". I'm looking for a conditional formatting formula which allows me to cover different parts of the layout in white.

What I have is this:

Transparent Layout Text 1 using Custom Formatting:

XY ≤ "A" and XY ≥ "A" (--> fill color white)

Transparent Layout Text 2 using Custom Formatting:

XY ≤ "B" and XY ≥ "B" (--> fill color white)

Transparent Layout Text 3 using Custom Formatting:

XY ≤ "C" and XY ≥ "C" (--> fill color white)

The problem is that this fills white if A, B or C IS part of field XY. How can I reverse that? I want the layout text to fill white when A, B, or C is NOT part of field XY.

Any ideas?

XY ≤ "A" and XY ≥ "A"

Isn't this a very long way to write XY = "A" ? If you want to reverse the condition, then either XY ≠ "A" or not ( XY = "A" ) should do.

Not quite, the idea is that

XY = A

-- > surface 2 and 3 covered

XY = A B

-- > surface 3 covered

XY = A C

-- > surface 2 covered

XY = A B C

-- > nothing covered

XY = ""

-- > everything covered

etc.

A simple equal doesn't work, nor does the "not" option... unless I'm using it wrong.

Have a look at the Position function.

Can you give me a hint how I would use it in this context? I've attached a sample file, I'm sure it will be much clearer what I'm trying to achieve.

Thank you for your help!

CondSample.fp7.zip

GOT IT, check this out:

Filter(CondSample::Field 1; "A") ≠ "A"

This simply checks if "A" is in field 1, regardless of its position. If not, the conditional formatting is set to fill white.

See sample file attached.

CondSample.fp7.zip

Also, if your formatting depends on more than one value, such as

Apple Pie

Orange Juice

you this will work:

IsValidExpression (FilterValues (Your Field ; "Apple Pie" ))

CondSample.fp7.zip

IsValidExpression (FilterValues (Your Field ; "Apple Pie" ))

I'd suggest you use:

IsEmpty ( FilterValues ( YourField ; "your value" ) )

The IsValidExpression() function is not appropriate for this use, and your test will fail if "your value" happens to be a valid expression, such as "true", for example.

You've got a good solution, but just FYI, I had in mind something like:

Position( CondSample::Field 1 ; "A" ; 1; 1 )

That would evaluate to a positive number (i.e. True) if it finds "A" or 0 (False) if it doesn't. Many people use the PatternCount function for this kind of test, but that function has to count every instance of the search string; we're only interested in whether the string exists, and Position will stop counting when we hit the first instance of it, resulting in a totally imperceptible performance gain. :

Even simpler... never thought of using the IsEmtpy function in this context - brilliant.

Thanks!

Fitch - I'm gonna go with comment's suggestion on this, but your input is very valuable. I know I can make use of it in many other situations.