Conditional Formatting

(There are important rules to follow for conditional formats to work properly; they are to be found at the end of this page.)

The conditional formatting feature allows you to apply formats (forecolor, backcolor, bold, etc) to a cell or range of cells, and have that formatting change depending on the value of the cell or the value of a formula. These get applied over the item-level colors.

Tutorial

As an example, you may want to highlight unchecked check boxes since they may require attention (tasks not done for example):


The InfoQube cell backcolor changes automatically as users check/un-check it. Also, note how the Notes cell also changes color.
To achieve this, a few simple steps:
1- Open the "Field management" dialog (View menu --> Manage Fields)
2- Select the InfoQube field (or any other Y/N fields),
3- add 2 conditional format rules as shown then click Save

4- Select the Notes field,
5- add the conditional format rule as shown below, then click Save.

 
6- Go to a grid where both fields/columns -- "notes" and "InfoQube" -- are present.
7- Close it and reopen it if it was already open.
8- Now, check (√) or un-check the InfoQube field. You should obtain what is shown in the first screenshot on this page.

 Explanations

What do all these codes mean

Criteria                                    Formats                                                      
<>0 BackColor=&H00FF00
=0 BackColor=&H0000FF
[InfoQube]=0      BackColor=&H00FFFF
=0 and [OK]=0  
<>"" Font=Courier New
 
"=0" means that the Yes/No field is "Null" --> "not checked".      Likewise: for other types of fields it means "Empty"
"<>0" means that the Yes/No field is "not Null" --> "checked".    Likewise: for other types of fields it means "Not Empty" 
<>"" means cell is not empty

How can one field condition the format of another field ?

As shown in step 2, if you want the formatting to be conditioned by another field (here a value in the InfoQube field will affect the Notes field formatting), you can add a specific field name (enclosed with square brackets) before the "<>0" or "=0"

So, lets say the "Date" field changes color based on whether the ItemHasHTML field is checked or not, you'd :
     a- put the details in the Criteria column in the Date field thus:
 
     [ItemHasHTML]<> 0
     [ItemHasHTML] = 0
 
     b- and then add formatting - color, etc. as appropriate. Example :
 
     BackColor=&hFF9BAF

Other formatting options


Formatting can be applied to any fields.
  • Bold=1. Bolds the cell
  • Italic=1.
  • StrikeOut=1
  • Underline=1
  • BackColor=&HBBGGRR: Changes the background color for cell
    (BBGGRR stands for hex values of Blue, Green, and Red colors)
  • ForeColor=&HBBGGRR: Changes the foreground color for cell
  • Font=Courier New,13. Format: FontName, FontSize. Can leave either one blank to use the grid default font

How can I have multiple conditional formats and/or associate multiple formatting with the same condition ?

 
      a- You can have as many conditional formats as you wish and activate/deactivate them using the "Use?" check-box.

      b- You can piggyback formatting using the | character:
      Bold=1 | Italic=1 | BackColor=&H00FFFF
              would bold, italic and change backcolor
 
       c- The criteria can include more than 1 field, such as:
      [Amount] >100 and [PaidBy] = "Credit"

Enabling conditional formatting for specific grids

There is now a 4th column, named Grids, which enables you to set the grids to apply the conditional format. Enter a | separated list of grid names

Important Notes Concerning Conditional Formatting

  1. Conditional format "Criteria" can be for any field types:  Y/N, Numeric, Text and Date Fields
    (If it's a Y/N (Boolean) Field, then  the condition is  =<>0 for Y and  =0 for N.)
  2. If a columns format is conditioned by another column, both need to be present in the grid for the formatting to happen. The column width can be set to 0 or even hidden if desired
  3. The criteria can include more than 1 field, such as [Amount] >100 and [PaidBy] = "Credit"
  4. Operators need to be in lower case (e.g.: and, not AND)
  5. Fields need to be enclosed in "[ ]" (e.g.:  [Field] )
  6. Text criteria are case sensitive. If you want case insensitive, use the "lower" function (e.g. lower([Notes]) = "work" will match both "Work" and "work"). Details here
  7. To match text anywhere in the text field, use the "contains" keyword instead of "=" (e.g.  lower([Notes]) contains "work" will also match "some work left to do")

Using an external editor

Editing the conditional format settings can be cumbersome. You can use Notepad to edit and function to load the text file back.
To do so, right-click on the conditional format settings column