Item Formatting
When not editing a cell (when the cell is highlighted), any formatting is said to be applied to the item. By default it is applied to the entire row. You can use the formatting toolbar buttons normally (and they appy to the item).
You can choose to just have certain columns get the item formatting using Grid>Properties>Options>>"Item Font applies to field defines which fields".
Setting an Item's Backcolor (row highlighting)
You can highlight an item with any color via it's right-click menu in the grid (Item Color).
First we see a list of default colors, then more colors & then an option to custom choose/create (even) more colors.
Using the Item Forecolor and Backcolor Fields
Setting an Item's color using the context menu actually just sets its ItemBackColor field. You can change that field and its color will update automatically next time you close/open the grid. You can also change its fore color using the field ItemForeColor. Both of these values are set using either VB Hex Color Codes or Named Colors (see below). You can also change an Item's font by using its ItemFont field.
Changing the Item forecolor and backcolor fields
It is possible to have multiple item fore/backcolors defined for an item (e.g. one for printing, one for screen). By default colors are defined in ItemForeColor and ItemBackColor fields. But you can change this at the database level (perhaps just while you print). To do so:
- Create a new text field: AlternateItemColor
- In Tools>>Options>>This Database>>General>>Item backcolor field: replace the default item color field (ItemColor) with the field AlternateItemColor
- Press F5 to refresh the grid. You can also open a new instance of the grid (right-click on the tab or shift+click on the grid name)
In the this example, the User Manual item as ItemColor=Yellow and AlternateItemColor=Red. Using Tools>>Options, one can toggle between the two colors.
Conditional Formatting
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.
(There are important rules to follow for conditional formats to work properly are to be found at the end of this page -- please make sure to read them)
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, or boolean 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 |
|
"=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"
How can one field condition the format of another field ?
As shown in step 2, if you want the formating to be conditioned by another field (here a value in the InfoQube field will affect the Notes field formating), you can add a specific field name before the "<>0" or "=0"
So, lets say the "Date" field changes colour 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 (Field names are case sensitive):-
ItemHasHTML<> 0
ItemHasHTML = 0
b- and then add formatting - color, etc. as appropriate. Example :
BackColor=&hFF9BAF
Other formatting options
Conditional formats criteria can use number or yes/no fields only. 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
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"
- Operators need to be in lower case (e.g.: and, not AND)
- Fields need to be enclosed in "[ ]" (e.g.: [Field] )
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
3- Starting v0.9.25U, the criteria can include more than 1 field, such as [Amount] >100 and [PaidBy] = "Credit"
Color Codes and Names
Color Codes
InfoQube uses VB hex numbers (e.g. the Red above is
&HAAAAFF ) for its color codes. These are in BGR format while the rest of the web uses RGB. To add a new color you have to find it's VB hex number, one good tool for this is
Bullseye Color Picker. You can use other sites and tools also, but be sure to swap the B and R if required.
Anywhere you can use a VB Hex Code you can also use a name if IQ knows it. These are called Named Colors. You can change or add new Named Colors in the database options at Tools > Options | This file > General > Named Colors (see 3.10.20.70 This file>General).
As you can see there is a list of colors there
Red|&HAAAAFF|Orange|&H99CCFF|Yellow|&HAAFFFF|Blue|&HFFFFAA| etc.
- First comes the color name which shows via item context menu (and can be used in equations / auto-assign rules).
- Then a separator '|'
- The comes the code for the color
- Then another separator before the name of the next color
It's important to remember that you need to restart to see any Named Color changes and that Named Colors only apply to the current file only.
Errata/Issues
Re: 3.2.30 Conditional Formatting
I tried this - without success. I used a yes/no field which was the source for the grid, copied and pasted the formula as given, saved, closed and opened the grid, tried refresh - but no success. Then I tried the same field in another grid where it was not the source but just another column I added. Still no result.
Also - where does the conditionformat2.png related to the instructions?
sorry if I am missing something obvious.
David
Re: 3.2.30 Conditional Formatting
Re: 3.2.30 Conditional Formatting
Oops, I missed something obvious - I pasted the whole line (criteria AND formatting) into the criteria field!
No wonder it didn't work. I have corrected it and now it does.
My apologies for wasting people's time.
David