A simpler way to maintain formatting conditions

Wednesday 15 February 2017 @ 12:32 am

I have written before about the advantage of using CurrentFieldValue when applying a formatting condition. This is especially true when applying a similar format to multiple fields, because it allows you to ‘paint’ the format properties from one field to another using the format painter.  Related to this method is a way to make it easy to update all of these formulas at once, without having to change them individually.

For instance, say you have 12 columns and the font color formula for all of them is:

if CurrentFieldValue > 90
then CrGreen
else CrYellow

When someone wants to change the value from 90 to 95 or tweak one or both of the colors it would require updating one and repainting all the others. Instead you could create some feeder formulas for the different literal values. I would create three formula fields:

{@target} which contains the number 90.
{@LowColor} which contains the function CRYellow
{@HighColor} which contains the function CRGreen

If you have those three formulas, your formatting formula would be:

If current field value > {@target}
then {@HighColor}
else {@LowColor}

You can apply this formula to all 12 fields.  Then when someone needs to change yellow to orange you just change the {@LowColor} formula from CrYellow to Color(255 , 165 , 0). This way the change affects all the formatting formulas in one stroke.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro