condtitional formatting problem with multiple conditions

[aswi]

I've been running into a problem with conditional formatting that I am hoping someone can help me with.

I have various fields that I want to turn different colors based on formulas. What I've found is that if there are too many different colors and if the formulas are too complex, then the conditional formatting gets stuck somewhere and won't work as I want it to.

Has anybody else run into this problem and if so what did you do about it? I'd really appreciate the help.

I should probably add that I'm pulling information from a lot of different tables to create the formulas.

Let me give an example. Let's say I have a date field that I want to turn:

- red if it's empty and is withing 1 week of another date

- yellow if it's outside a certain time frame, and

- gray if there is a change in status of a patient (e.g. has moved and so cannot make it to appointments anymore)

If I do any one of these conditions separately they work fine but once I add all of them together, it gets stuck on gray.

Any ideas?

[The Shadow]

A simple example seems to work okay.

See attached.

condFmt.fp7.zip

[comment]

The order of conditions is important: conditions are evaluated from top to bottom. If you have multiple conditions for the same parameter, the last condition to return true will determine the result.

[LaRetta]

...the last condition to return true will determine the result.

This feels opposite of short circuiting behavior and reminds me of calculation evaluations before vs. 7. I realize these are separate calcs and I suppose that's why, but it feels backwards.

Can it be explained a bit further?

[Søren Dyhr]

reminds me of calculation evaluations before vs. 7.

Yes and no, back then did we not have any control on which part of the Case( statement which actually was evaluated, it was all done, sometimes in vain. Here can they be published by the formate painter and then could some of the branches be taken out of the progress ... but the flow is counter intuitive indeed!

----------

Correction! you can't lift a comprehensive formatting scheme with format painter and reuse it in other fields and then omit the needless - what a shame!

--sd

[comment]

It has to be this way, because conditions are also cumulative. For example, you can have:

• Value is > 10 = Text Color (Red);

• Value is > 50 = Fill Color (Yellow)

This results in a value of 25 being displayed as [color:red]25, and a value of 75 displayed as [color:red] 75 (BOTH attributes applied). Therefore the process cannot exit at the first condition to return true. It must continue and check the following conditions, and apply any of them that returns true. Any true condition is applied, so if a condition overrides a previous one, it's the last true condition that prevails.

[LaRetta]

I had forgotten about reading this in Help way back! Thank you, Michael.

This would be kinda cool as a function, Cumulative(). :wink2:

[Søren Dyhr]

When we're there what about C++'s switch for ordinary Case( statements.

http://www-numi.fnal.gov/offline_software/srt_public_context/WebDocs/Companion/cxx_crib/switch.html

More or less the same!

--sd

[comment]

This would be kinda cool as a function, Cumulative().

What would it do?

[Søren Dyhr]

LookupNextHigher( provide, some of these features as well!

--sd

[aswi]

That's very useful information to know. I'll see if I can retool my formulas so only one can be true at a time. Thanks!

[LaRetta]

What would it do?

I was envisioning a recursive ability like:

Cumulative [ longTextField ; [ apply this ] ; [ then apply this to THAT result ] ; stopWhen ) ... and so forth :laugh2:

[comment]

I'll see if I can retool my formulas so only one can be true at a time.

That is not required. You only need to reorder them by reverse priority, so that when the last true condition overrides the previous true one, it produces the result you want.

the

[comment]

Isn't that a custom function? I think 'cumulative' is usually applied to the result. The process itself is ''nested' or 'chained'.

[LaRetta]

Right. But with Cumulative() as a function, one wouldn't need custom function for it. I was just speculationg that having a function for it which applied prior results (like the conditional formatting does) would work nicely. Oh, nevermind ... :smirk:

Edited January 9, 2008 by Guest