Grid color banding 3

[bsutton55]

I want to display a grid with alternate color rows. The usual way I would do this is using code such as:

[pre]SetAll("DynamicBackColor","IIF(MOD(RECNO(),2)=0,16777215,8454143)")
[/pre]
​

However the grid in question is showing records displayed in an indexed order - so RECNO() is not the same as line number - and consequently the banding does not always work as it should.

Is there a way to achieve this?
Thanks
Barry Sutton

 

[Mike Lewis]

Hello Barry,

This is a familiar problem. The usual way to deal with it is to add an extra field to the underlying table or cursor (the one that is used to populate the grid). After you have set the index order, loop through the table, setting the extra field to .T. or .F. alternatively. Then, in your [tt]SetAll("DynamicBackColor", .... )[/tt], test the value of the field, rather than [tt]MOD(RECNO(),2)[/tt].

If you can't add an extra field to the table, consider generating a new cursor to populate the grid. The cursor would have all the fields of the original table, plus the extra one. But, unfortunately, it would be difficult to do that if you also want the grid to update the original table.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

There is a way to create thta extra Cursor Mike suggest, but only containing the extra alternating boolean value field. All you need is RECCOUNT(maintable) records in that cursor and then SETE RLEATION TO that cursor and populate a field with alternating values you check in your DynamicBackColor expression.

Example:

Open Database (_samples)+"northwind\northwind.dbc" Shared

Use northwind!Suppliers
Set Order To POSTALCODE

Create Cursor curIndexRowNum (iRow I)
Append From Dbf("Suppliers") && simple trick to create as many records, there is no iRow field in Suppliers, therefore all rows are blank

Local lnRowNum
lnRowNum = 0
Select Suppliers
Set Relation To Recno() Into curIndexRowNum
Scan 
   lnRowNum = lnRowNum + 1 
   Replace iRow With lnRowNum In curIndexRowNum
EndScan 

Select Suppliers
Go Top
Browse NAME loGrid NOWAIT
loGrid.SetAll("DynamicBackColor","IIF(MOD(curIndexRowNum.iRow,2)=0,16777215,8454143)")

Instead of a row number sequence you might simply store the alternating colors or whatever color pattern you'd like in the additional cursor and simply make that field the DynamicBackcolor itself.
If you set a different index order, you also have to repeat the update of the cursor via the SCAN..ENDSCAN of course. The data is working for one index only, and when data changes index order also changes. That will always bite you, so you better only do this for grids not editable.

Bye, Olaf.

 

[bsutton55]

Mike/Olaf
Thanks - I had been doing pretty much what you suggest, creating an extra field and populating it accordingly with values reflecting the required color banding. It just seemed a bit clunky and, as Olaf says, could become maintenance heavy if other indexes get involved. At least I now know I can leave it as it is!

Thanks again
Barry.

 

[Dan Freeman]

The other way to do this, of course, is with a local view.

The view's ORDER BY clause will be the same as your index expression, and the resulting cursor's rows will be in the physical order needed for your DynamicBackColor. And, of course, make it an updateable view and it's just like using your original table ... only it works without kludging an extra field.

 

[Olaf Doschke]

Using views will work well in most cases, I also don't work with tables directly, but mostly because nowadays the backend is SQL Server and I use data access classes based on CursorAdapter, for which the same applies.
Nevertheless I index the CA cursor for sorting and that way also couldn't rely on RECNO%2.

It's even simpler for a report, you generate a counter as a report variable and use a gray shape you send to back and set it's PrintWhen to rowcount%2.

Bye, Olaf.

 

[neculai]

Hi, experts

I've tried to achieve the alternate coloring, and something is obviously wrong for me. Here is my code in the Init of the Grid.

*--- Create the index for sorting on columns
Select (This.RecordSource)
Index on accenumb tag accenumb of (This.RecordSource)
Index on genus tag genus of (This.RecordSource)
Index on species tag species of (This.RecordSource)
Index on no tag no of (This.RecordSource)
Set Order To 5 ASCE In (This.RecordSource)

*--- curColor
Create Cursor curColor(iRow I)
Index on iRow tag iRow

*--- Set the relationship
Select (This.RecordSource)
Go Top
Set Relation To Recno() Into curColor

*--- Populate curColor
Select (This.RecordSource)
Go Top
Scan
Insert Into curColor values (Recno('curColor'))
EndScan

*--- Alternate white and blue records
Select (This.RecordSource)
Go Top
This.SetAll('DynamicBackColor', Alltrim(str(Iif(Mod(curColor.iRow,2)==0, RGB(128,255,255),RGB(255,255,255)))), 'Column')

What am I missing? Please, give me a hint.
All the very best,
Neculai

 

[Mike Lewis]

This.SetAll('DynamicBackColor', Alltrim(str(Iif(Mod(curColor.iRow,2)==0, RGB(128,255,255),RGB(255,255,255)))), 'Column')

This is where you are going wrong.

You are trying to set DynamicBackColor to a numeric RGB value, converted to a string. What you should be doing is setting it to an expression, that is, a character string that can be evaluated to a numeric RGB value.

The expression you want is:

"IIF(MOD(curColor.iRow,2)==0, RGB(128,255,255),RGB(255,255,255))"

Note the quotes. Remember, you are setting the property to a string.

So the entire line should read:

This.SetAll("DynamicBackColor", "IIF(MOD(curColor.iRow,2)==0, RGB(128,255,255),RGB(255,255,255))", "Column")

I have't tested this, but I think it's correct. Give it a try.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Well, in short, you didn't copy my code.

1. No index on curColor, I use a relation based on Recno, not on an index. There is a recno based mode of SET RELATION less known. Take a close at the help topic.
2. No Recno() value stored into iRow, that'll just reproduce the same problem you have with the recno of the grid table.

The way it works is simply:
No matter what recno order the main table has, each record will point to one record in curColor and only one record.
Scanning through the main grid table you also scan through the curColor cursor table in some way, what doesn't matter is the recno, it just matters you also go through this cursor in a reproducable repeating way. And then you simply store a sequence you generate in a counter variable or even the colors you want in there.

If you simply execute my example, which uses sample data you also must have installed, unless you only have VFP7, and then browse curColor, you'll see curColor.iRow <> RecNo('curColor'). That's making it work. iRow is the rownumber in index order, the index used on the grid table, there is none on curColor.

Bye, Olaf.

 

[neculai]

Thanks, Mike and Olaf. I've followed your advises and here it's my code in the Init of the Grid.

*--- curColor
Create Cursor curColor(iColor I)

*--- Set the relationship
Select (This.RecordSource)
Go Top
Set Relation To Recno() Into curColor

*--- Populate curColor
Select (This.RecordSource)
Go Top
Local lnRow, lnColor
lnRow = 0
lnColor = 0
Scan
lnRow = lnRow + 1
lnColor = Iif( Mod(lnRow, 2)==0, RGB(128,255,255), RGB(255,255,255) )
Insert Into curColor values (lnColor)
EndScan

*--- Alternate white and blue records
Select (This.RecordSource)
Go Top
This.SetAll('DynamicBackColor', 'curColor.iColor', 'Column')

So far, when I first display the Grid, all is good. When I try using different columns for sorting, I use the next code in a custom method:

*--- Zap curColor
Set Safety Off
Zap in curColor
Set Safety On

*--- Order for the table
Select (This.Grid1.RecordSource)
Set Order To (sortField) &sortOrder In (This.Grid1.RecordSource)
Go Top In (This.Grid1.RecordSource)

*--- Populate curColor (again), so the SET RELATION TO applies to the newly main sorted table.
Select (This.Grid1.RecordSource)
Go Top
Local lnRow, lnColor
lnRow = 0
lnColor = 0
Scan
lnRow = lnRow + 1
lnColor = Iif( Mod(lnRow, 2)==0, RGB(128,255,255), RGB(255,255,255) )
Insert Into curColor values (lnColor)
EndScan

This.Grid1.SetFocus()

***This.Grid1.SetAll('DynamicBackColor', 'curColor.iColor', 'Column')

This.Grid1.Refresh

Sorting works, but the colours are assigned the same like in the Init method (the records have the same colouring, this time not alternately, obviously). I tried issuing again the SetAll() (commented here), but no effect. Any hints?

Thanks.
Neculai

 

[Olaf Doschke]

You can't populate the curColor cursor with INSERTs. All records have to exist since the recno is used for relation and the first row in index order could be the last recno in the grid cursor. So a recno N most probably must exist before you populate the Nth record of curColor.

You jump forth and back in regard of recno() in the main table because of the index and you jump firth and back in regard of recno("curColor") in exactly that same way and that's what's making it work, but to be able to do so as many records have to exist in curColor.

In fact you won't ever need to ZAP curColor, you onle change the values. Once you have Reccount(maintable) records in curColor you will stay with that number of records.

Bye, Olaf.