Inverse Conditional Formatting

[innodat]

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?

[comment]

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.

[innodat]

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.

[Fitch]

Have a look at the Position function.

[innodat]

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

[innodat]

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

[innodat]

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

[comment]

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.

[Fitch]

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. :

[innodat]

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.