Single cell colour change depending on condition 2

[Steve-vfp9user]

All,

I have a grid containing rows of table records.

I use the following code within the Object: Grid1

Procedure: AfterRowColChange

LPARAMETERS nColIndex
grno = RECNO()

thisform.grid1.Refresh

Procedure: Init

PUBLIC grno
grno = RECNO()

this.setall("Dynamicbackcolor", ;
  "IIF(RECNO()=grno,RGB(0,0,160),RGB(255,255,255))","Column")

this.setall("DynamicForecolor", ;
  "IIF(RECNO()=grno,RGB(255,255,255),RGB(0,0,0))","Column")

When the grid on the form is started, the highlighted line is blue with white text which works perfectly and each changes accordingly as you scroll though.

Some of the records in a column with the table name JOBSTATUS have the letter "R" (without the quotes) to show it is a refund.

What I am trying to achieve is that when the form is loaded, any records with the condition JOBSTATUS="R" that only the cell within the grid will be shown as the back colour being red and the fore colour in white.

There are several FAQ's in the forum but none that give any information about changing a single cell linked to a condition.

Any guidance or suggestion would be appreciated.

Thank you

Steve Williams

 

[Olaf Doschke]

Well, there is no setting for a single cell, but for a single column, simply don't use setall but only set a certain column1,2,3,4.DynamicBackcolor and DynamicForecolor.

To only do that for rows with "R" in a certain table column, that is your IIF condition.

So after

this.setall("Dynamicbackcolor", ;
  "IIF(RECNO()=grno,RGB(0,0,160),RGB(255,255,255))","Column")

this.setall("DynamicForecolor", ;
  "IIF(RECNO()=grno,RGB(255,255,255),RGB(0,0,0))","Column")

you add

This.column3.Dynamicbackcolor="IIF(field='R',RGB(160,0,0),RGB(255,255,255))"
This.column3.DynamicForecolor="IIF(field='R',RGB(255,255,255),RGB(0,0,0))"

Notice within double quotes the use of double quotes is not possible, therefore 'R'.
Of course Column3 is merely a random choice, you know best which column you want changed.

Also notice, that column will differ in the coloring of every other column, there is no easy waay to combine all coloring expressions, so you get the overlap coloring conditions. Either you live with that or you introduce a more complex matrix of expressions and colors via ICase statements covering all cases of one or the other or both conditions being true with all the mixed colors needed to display that.

Bye, Olaf.

 

[Olaf Doschke]

If your conditions get much more complex it might pay to use the oleboundcontrol and bind an Excel spreadsheet, in which you can have conditional formatting and coloring per Cell. Especially if you have knowledge about how to use that already. You then of course also can make use of all other Excel/spreadsheet features like formulas and automatic computations.

Bye, Olaf.

 

[Steve-vfp9user]

Hi Olaf

Just tried your suggestion and it's given us the desired effect.

I appreciate your prompt and valued post.

Thank you

Steve Williams

 

[Mike Lewis]

Steve,

I see that Olaf has given you a good answer. I've nothing to add to that. However, if you need any further information about conditional formatting in a grid, I recommend that you peruse Conditional formatting in a Visual FoxPro grid, which explains the whole thing in some considerable detail.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Steve-vfp9user]

That link looks very interesting Mike

Also appreciate your post.

Thank you

Steve Williams

 

[Steve-vfp9user]

Following on from Mike's post, I viewed the web page link and found a line of code which I added in the Init procedure of the grid:

This.column13.DynamicForeColor="IIF(JOBSTATUS<>'R', 0, 255)"

This shows the "R" in red with a white background instead of the background being red. Having tried both options, the latter appears to be more pleasing to the eye however one slight problem I have is that at the moment, the highlighted line for each record still, as expected, shows a blue background with a white fore colour for the exception of column13 which, when highlighted has a black fore colour, blue back background and the fore colour remains black (where it should be white when highlighted).

I hope the above makes sense.

This isn't a problem for the "R" condition records but it would be better (cosmetically) if the records <>"R" could remain blue back colour, white fore colour when highlighted.

Any suggestion would be appreciated and if further clarification is required, please let me know.

Thank you

Steve Williams

 

[Olaf Doschke]

It's the same as "IIF(field='R',RGB(255,255,255),RGB(0,0,0))", just <>'R' is the inverse condition and so black comes before red.

You have the problem I already described, you can't combine all possible combinations of conditionsthis way. For you JOBSTATUS column the other coloring doesn't apply, unless you would have a more complex ICASE with 4 conditions 1. JOBSTATUS = 'R' AND RECNO()=grno => color1, 2. JOBSTATUS = 'R' only => color2, 3. RECNO()=grno only => color3 and 4. otherwise => color4

Bye, Olaf.

 

[Steve-vfp9user]

Appreciate the post Olaf. It's not something that will hold us back so I'll look into what you have suggested and post back when I have a sollution.

Thank you

Steve Williams

 

[Olaf Doschke]

If you can't wrap your head around IIF por ICASE you can also write a verbose form method returning a color and simply call it from the DynamicBackColor property, setting it to the call as in Grid.ColumnX.DynamicForeColor = "Thisform.GridForecolor(param1,param2)".

Obviously more ideal to add this method to the grid, but that's not possible after you adde3d the native grid to a form, that will require first creating a grid class to then use in forms.

Bye, Olaf.

 

[Steve-vfp9user]

Hey Olaf

Your last post is a bit complex for my knowledge but I have found something to do with ICASE.

I will post back when I have a sollution.

Thank you

Steve Williams

 

[Olaf Doschke]

My last post is just suggesting you can write a normal IF or case, long multiline code, in a form or grid method. This will be easier than the necessety to cramp all the necessary logic into a single code line. ICASE is what I suggested earlier, as it's capable to do more cleanly, what IIF only can do as ugly unreadable nested IIFs.

If you don't know yet how to add a form mehthod, you simply edit your form and choose the menu item Form->NewMethod. The rest follows.

Bye, Olaf.

 

[Steve-vfp9user]

Appreciate the update Olaf

Thank you

Steve Williams

 

[Steve-vfp9user]

Just to update this thread for closure, we used Olaf's suggestion as below as the "R" relates to refunds and as there are not many of those (thankfully), the view in the grid is more than acceptable.

This.column3.Dynamicbackcolor="IIF(field='R',RGB(160,0,0),RGB(255,255,255))"
This.column3.DynamicForecolor="IIF(field='R',RGB(255,255,255),RGB(0,0,0))"

I appreciate the other posts on this thread.

Thank you

Steve Williams