Hello,
I spent at least 12 h last week trying to homogenize formatting and colors in my DB and I haven't succeeded. I'm exhausted.
Sometimes conditional formats work, sometimes they don't.
I checked that all the necessary columns are there in the right grids, etc. and they are, etc.
I'm not an IQ newbie and so I think I should be able to have conditional formatting work properly by now.
I have a few questions :
1- Formatting seem to proceed as "IF" (e.g. : if notes<>0 then BackColor=&h6EECFF, if task<>0 then BackColor=&h00DDFF, etc.).
a-- Is that so ?
b-- And if yes, does that effectively mean that the first "met" condition (true) is superceded by the last "met" (or non-met) one ? (in other words : When several fields act on a conditional formatting, which one has priority... The last met one or the last non-met one ?)
2- Formatting also depends on columns/fields being present in the grids (and if they are not present, an error message appear, etc.).
a-- Does the column order have any impact on the formatting (e.g. the task column appears on the left of the item column, and the project column appears on the right of the item column and so it comes after the task column ) ?
b-- If it does have an impact, what's the rule ? (e.g. can the order of column affect the priority in which the various conditional formatting is applied ?)
3- As different grids display different sets of columns, the "invalid conditional format" error message appears almost for each grid I open.
Of course, I cannot have all columns needed for all the different formatting appear in all grid. That would be ridiculous. So I usually ignore it, and all is fine.**
So, question is :
a-- Can the "invalid conditional format" error message affect other conditional formats than the one mentioned in the error message (in other words : do other working conditional formatting cease to work because there's an "unrelated" error) ?
4- And, lastly... I have some fields that appeared filled with data thanks to some SQL requests in the options, such as : <source>SELECT ItemID, [ItemID]>0 AS WIKITagYN FROM ¯qWIKITag WHERE ( not isnull([WIKITag]) );</source>.
These fields seem to be able to be used for conditional formatting, however, they seem to be superceded by other "normal" fields (filled with data). (I'm saying so as they don't seem to alkways have an effect, depending on what field comes before them in the conditional formatting list)
a-- Is that the case ?
b-- If not, what,s the rule ?
Conditional formatting is an important aspect of IQ.
I'd really like to be able to use it properly but I fail to do so.
What am I doing wrong ? Is it me or is it bugs ?
Thanks for taking the time to clarify and / or fix this. I'm sure all users will benefit -- whether they do now or later, when they'll need conditional formatting.
==
P.S. : Like I said before : I don't mind the fact that a grid needs to have certain "columns /fields" displayed (the conditions) to be able to show certain formatting. And I actually like this as it allows to treat a field differently from one grid to another, etc.
Comments
Hi Pierre, I guess you're busy.
================
When you have the time, it would be good to know more about the "inners" of conditional formatting.
Fields in the conditional format section are All YN fields
The Yellow zone represents the zone which doesn't adversely affect any other conditions.
The green zone represents the zone where any conditions put there cancels the last condition, even if it is met.
The red zone represents the zone in which conditions are not working If other "met" conditions are located in the "green zone"
If I move any condition from the green zone to yellow zone, what wasn't working starts working. Weird. I tried with several configurations and it's always the same pattern
(You don’t need to understand all that. Just follow the steps after the table.)
What these conditional formats do is :
A- color the WikiTag cell into a dark yellow (almost orande by default (&h00DDFF) ,
B- and only when the field has something into it will it be coloured with a light yellow (&h6EECFF)
Appointments<>0
BackColor=&h00DDFF
Task<>0
BackColor=&h00DDFF
SoftwareRef<>0
BackColor=&h00DDFF
Étape<>0
BackColor=&h00DDFF
zzReference<>0
BackColor=&h00DDFF
Followup<>0
BackColor=&h00DDFF
NotesDivers<>0
BackColor=&h00DDFF
Besoin<>0
BackColor=&h00DDFF
Objectif<>0
BackColor=&h00DDFF
Reference<>0
BackColor=&h00DDFF
Projet<>0
BackColor=&h00DDFF
Tactique<>0
BackColor=&h00DDFF
DoAcYN<>0
BackColor=&h00DDFF
Souhait<>0
BackColor=&h00DDFF
WIKITagYN<>0
BackColor=&h6EECFF
==================
Steps to reproduce (not that long):
Preparation
1- Create all the fields shown in the above table. All YN fields. Simple.
2- In the WikitagYN field you created, add this in the options section:
<source>SELECT ItemID, [ItemID]>0 AS WIKITagYN FROM ¯qWIKITag WHERE ( not isnull([WIKITag]) );</source>
(This field will appear as checked when something is added to the WikiTag field.)
3- In the standard WikiTag text field : reproduce the config appearing in the above table (conditional format section).
It’s easier to do use exactly the same field names but if you don’t want to :
a- The important aspect is that their should be the SAME number of conditions, all YN fields.
b- you should be aware of their exact position in the table. (I suggest you reproduce a similar table and color the zones)
4- Create a grid :
a- All involved YN fields are displayed
b- the WikiTag text field should be there too (Note that filling the WikiTag text field will check the wikitagYN field, and proper conditional formatting will appear after a refresh)
5- Create 2 items : one with WikiTag field filled, one with nothing in it. The one with the WikiTag field filled should already appear with a pale yellow shade.
Process to reproduce
So this is WORKING :
-1-
a- Item with WikiTag field filled : appointment is checked
It appears with a LIGHTyellow shade, as expected.
b- Item with WikiTag field NOT filled : appointment is checked
It appears with a DARK yellow shade, as expected.
-2-
c- Item with WikiTag field filled : Reference is checked
It appears with a LIGHTyellow shade, as expected.
d- Item with WikiTag field NOT filled : Reference is checked
It appears with a DARK yellow shade, as expected.
NOT WORKING:
-1-
e- Item with WikiTag field filled : task is checked
It DOESN'T appears with a LIGHT yellow shade, but with a DARK SHADE.
f- Item with WikiTag field NOT filled : task is checked
It appears with a DARK yellow shade, as expected.
But, basically, the WikiTag field never changes color : it stays DARK YELLOW, even when it should be a light yellow.
-2-
g- Item with WikiTag field filled : task AND Project are checked
It DOESN'T appears with a LIGHT yellow shade, but with a DARK SHADE.
h- Item with WikiTag field NOT filled : task AND Project are checked
It appears with a DARK yellow shade, as expected.
So even if a field is inside the Yellow zones is checked, as soon as a field in the green zone is checked, it cancels the condition in the red zone.
Same thing for any item with a field in the green zone meeting the condition.
I’d be glad if someone has the patience to try that and report results.
AddressBook<>0
BackColor=&h00DDFF
Billing<>0
BackColor=&h00DDFF
Check<>0
BackColor=&h00DDFF
Finances<>0
BackColor=&h00DDFF
Invoices<>0
BackColor=&h00DDFF
MapViewDemo<>0
BackColor=&h00DDFF
MusicLibrary<>0
BackColor=&h00DDFF
NextWeek<>0
BackColor=&h00DDFF
OK<>0
BackColor=&h00DDFF
Paid<>0
BackColor=&h00DDFF
Projects<>0
BackColor=&h00DDFF
Reference<>0
BackColor=&h00DDFF
SampleGrids<>0
BackColor=&h00DDFF
ShoppingList<>0
BackColor=&h00DDFF
Amount > 0
BackColor=&h6EECFF
Process to reproduce the problem
So this is WORKING :
-1-
a- Item with amount field FILLED + AddressBook is checked
It appears with a LIGHTyellow shade, as expected.
b- d- Item with amount field EMPTY + AddressBook is checked
It appears with a DARK yellow shade, as expected.
-2-
c- Item with amount field FILLED + Reference is checked
It appears with a LIGHTyellow shade, as expected.
d- Item with amount field EMPTY+ Reference is checked
It appears with a DARK yellow shade, as expected.
NOT WORKING:
-1-
e- Item with amount field FILLED + billing is checked
It DOESN'T appears with a LIGHT yellow shade, but with a DARK SHADE.
f- Item with amount field EMPTY + billing is checked
-2-
g- Item with amount field FILLED + Billing AND Paid are checked
It DOESN'T appears with a LIGHT yellow shade, but with a DARK SHADE.
h- Item with amount field EMPTY + Billing AND Paid are checked
So even if a field is inside the Yellow zones is checked, as soon as a field in the green zone is checked, it cancels the condition in the red zone.
Same thing for any item with a field in the green zone meeting the condition.