Format conditionnel des colonnes

[Petitpainauchocolat]

Bonjour,

if fieldname < 2 then $008080FF clWhite clBrown;

met le champs fieldname en couleur chaque fois que sa valeur est plus petite que 2.

1. Est-il possible de mettre en forme une ligne complète selon la valeur d'un seul champs ?
2. Est-il possible d'ajouter plusieurs conditions dans l'éditeur sur des champs différents. ?
3. Est-il possible de mettre en forme autre chose que des nombres ?

[neuts-jl]

Réponse :
1. non, la condition agit pour le champ lui même, quand la condition est vrai
2. oui, on peut mettre autant de conditions que l'on veut, 1 ligne=1 condition
3. oui, "texte" aussi,

Syntaxe

if fieldname1=value1 then bgcolor color fontstyle
if fieldname1=value2 then bgcolor color fontstyle
if fieldname2=value  then bgcolor color fontstyle
   -- Opérators : =, <, >, <>, Contains, NotContain
   -- numeric value : 1.5 
   -- string value : "string"

Exemple

if age<10 then clGreen clWhite 1
if age>10 then clRed clWhite 2
if nom="bella" then clBlue clWhite 0
if nom contain  "erreur" then clRed clYellow  2

Quels sont vos besoins ?

[neuts-jl]

Je vois pour implémenter le cas 1 et faire évoluer cette fonctionnalité

[Petitpainauchocolat]

Oui, le cas 1 en plus du comportement original m'intéresse car cela permet d'afficher par exemple un enregistrement en couleur en tenant compte de la valeur d'un seul champs, numérique par exemple. De plus avec la propriété ColumnsWidths il est possible de cacher cette donnée pour n'afficher que les données lisibles par l'usager.

[neuts-jl]

C'est implementé pour la prochaine version

Syntax :
    if FieldName=Value then bgColor Color FontStyle [FieldNameToFormat]
    if ...

Opérators :
    =, <, >, <>, Contains, NotContain

Value :
    numeric : 1.5
    string : "my string"
    FieldName : FirstName

bgColor,Color :
    clWhite, $00FFFFF,...

FontStyle :
    0 : normal
    1 : bold
    2 : italic
    3 : bold+italic
    4 : underline
    5 : bold+underline...

FieldNameToFormat is optional
    fieldname
    empty = FieldName
        * =  all fieldnames (row)

[Freen]

Hi, I'm really new to this and still learning about all the functions. I would like to implement a cell color function in a form as well, but on a date field (date format is dd/mm/yyyy) vs today's date. If there are more than 30 days until the cell date = clDefault.
If there are 15 to 30 days left = clYellow
and less than 15 days left = clRed. This is to indicate visually in a list, when an item is due for testing.

I display the list in a form with a DBGrid (DBGrid -->Properties-->Cell ConditionalFormatting)

I got an error message first, without the the color code and font number:
"Error reading DBGrid1.ConditionalFormatting.Strings: DBGrid1 if Retest_Date=<((julianday('now) - (-15d)) then clRed

then exceptet after value"

As I'm loading the data for the DBGrid from a "View", where I gave the date field the Alias "Test Due", it won't recognise the Alias name in ConditionalFormatting. Only if I remove the Alias and leave it as the Field name 'Retest_Date', it tries to work (failing on the date format 'julianday' then). - How can I show an Alias for the column Retest_Date in the DBGrid, if an Alias can't be used in the View filter?

But the following is still utterly wrong >_<;

if Retest_Date=<((julianday('now') - (-15d)) then clRed, $000202FF, 0
if Retest_Date<((julianday('now') - (-30d)) then clYellow, $0000FFFF, 0
if Retest_Date>((julianday('now') - (-30d)) then clDefault

Any help with this is much appreciated. Thank you.

[neuts-jl]

Hi,
The interpreter used in the datagrid formatting conditions is very rudimentary. It cannot evaluate expressions.
https://sourceforge.net/p/nsbase/forum/howto/thread/55e2c84cb3/#aec1

Without analyzing your code, the solution is to make a view, containing 3 columns with your 3 expressions, and thus test the Boolean value in the formatting conditions.
You can then make these columns invisible by specifying the width at 0

select 
    ....
    Retest_Date=<((julianday('now') - (-15d)) as 'cond1', 
    Retest_Date<((julianday('now') - (-30d)) as 'cond2', 
    Retest_Date<((julianday('now') - (-30d)) as 'cond3'
 from
    ....

and in conditionnal formating property

if cond1="true" then clRed, $000202FF, 0
if cond2="true" then clYellow, $0000FFFF, 0
if cond3="true" then clDefault

Cordially

[Freen]

Hi surfvite,

Thank you very much for the code above. I didn't know I'm able to add a complete expression into the field, where you pick the table columns.

edit

The date format in the table is dd/mm/YYYY. The following formula worked for me

select 
    ....
    julianday(Retest_Date)<((julianday('now') - ('-15 day')) as 'cond1', 
    julianday(Retest_Date)<((julianday('now') - ('-30 day')) as 'cond2', 
    julianday(Retest_Date)<((julianday('now') - ('-30 day')) as 'cond3'
 from
    ....

However, now I need to work out how to "fix" 'cond2', as both, cond1 and cond2 return a value of '1' if the difference is less than 15 days.

[neuts-jl]

See https://www.techonthenet.com/sqlite/functions/date.php

[Freen]

How can I extract/send the code for one form? I'm fairly sure I've got it all correct now, but the color of the cells in the DBGrid does not change.

The code seems to work, at least I haven't received an error message - Form --> DBGrid --> Properties --> ConditionalFormatting

I believe I have sent you an email/message through the sourceforge site, but I'm not sure if you received it.

[neuts-jl]

No I did not receive anything

[Freen]

I have made a separate sample for the form and attached it here.

[neuts-jl]

sorry to answer you late, I'm on vacation.
The solution is simple, it is a peculiarity of the syntax, it is not necessary to put a comma between the values after the "then"

if cond1=1 then clRed $000202FF 0
if cond2=1 then clYellow $0000FFFF 0
if cond3=1 then clDefault $00FDFDFD 0

[Freen]

Hi surfvite, not to worry and enjoy your vacation!

The sample accepts the code, but the color is still not changing. I have also tried changing the colour formatting for the buttons, but to no avail. I'm not sure what the reason could be, maybe I'm missing an important prerequisite I have to set up first? I left some comments in the sql script of the form and the view.

[neuts-jl]

The file is ok

[neuts-jl]

Have you tried the corrected file ?

[Freen]

Hi survite, yes, the colouring on the line works now, thank you. I just have to work on the formula itself, as I'm getting (correctly) the logical result '1' on all entries 15 and 30, if the date is older than 30.

julianday(tblSystemService.RetestDate)<(julianday('now') - ('-0 day')) as "cond1",
julianday(tblSystemService.RetestDate)<(julianday('now') - ('-15 day')) as "cond2",
julianday(tblSystemService.RetestDate)<=(julianday('now') - ('-30 days')) as "cond3"
-- How to calculate "cond2" properly, if "cond1" applies too? (between 15 and 30 days) 

[Freen]

Sorry to keep asking silly questions, you may see it in that file too, in the form 'Service Dates', I tried to color the form [Close] button red (clRed), but changing the color of buttons doesn't seem to be working?

[neuts-jl]

Hi,
I like that we note any anomalies, or questions about NSBase, it allows the software to grow.
I had not seen this setting or the comment in your form
The "Color" property of the buttons does not work natively under windows, but under linux it is correct.
However, I have overloaded this component in version 1.7.1 so that it does not take into account the windows theme when the background color is different from "clDefault"

[Freen]

Hi surfvite, got the update, thank you for the quick fix.

Now I have to look where I went wrong with the glyphs, which are not showing in my file (your sample is displaying them fine)... -_-;

[Petitpainauchocolat]

Same, glyphs doesn't appear even if the bitmap is loaded

[neuts-jl]

I overloaded the component, to display the legend on a colored background when this color is different from clDefault.
To find all the functionalities of this component, in particular the management of glyphs, it is necessary to recatify the windows theme by setting the color to clDefault