Alternating record color on sortable grid 2

[tamayok]

Hello to all,

I am sure many have travelled this road before and found an elegant way to handle it! I have a sortable grid (where the user clicks on the header and it either sets an index tag or it creates an .idx). Getting there was cumbersome debug and extremely verbose, but it works.

However, I want to add alternating colors to the grid (like in the typical "ledger" style but with other RGB color). Since the table's order will vary, I cannot rely on a logical scheme that checks for ODD/EVEN record numbers. Also, the table's structure will vary - it can be any; as opened interactively by the applications' user. Therefore, I foresee the logic would rely on grid properties.

Thanks in advance for your replies!

 

[craigsboyd]

You have to add another field to the grid's record source and then populate that with consecutive numbers. Then something like the following to create the visual effect (assumes that the field you added is named MyRecNo)...

Thisform.Grid1.SetAll("dynamicbackcolor", ;
"IIF(MyTbl.MyRecNo % 2 = 0,RGB(255,255,0), RGB(255,255,255))", "Column")

The problem with Recno() is (as you've already stated) it doesn't work for this sort of thing when the table is anything but physical order. Grid properties such as activerow and relativerow are of no help here either. The only way that I know of to reliably do this is to either create an additional field that you can plug consecutive numbers into (you will have to redo this any time the order changes) or use a cursor for the grid (where you have selected the records in the order in which you want them) so that Recno() can be used reliably for the dynamicbackcolor.

SweetPotato Software Website
My Blog

 

[tamayok]

Craig,

Thanks a lot for that reply! Sounds like the it's the best workaround for this idea; I had wished for some trick involving those very properties (Active/Relative row) but had trouble visualizing how to implement it... Perhaps that plus some BINDEVENT(), but again, probably your solution is THE alternative.

THANKS!

 

[Olaf Doschke]

Hi tamayok,

here is a tricky example. The idea is to bind an unsorted cursor to the grid and relate it to the sorted cursor, of which the fields are bound to the columns.

That way the unsorted cursor remains in Recno() order and can be used to define the dynamic back color.

Run the following example and set the order with the three buttons below the grid. It's a bit more complicated than an extra row to number the records, but you only need to create the additional cursor once, the order is simply set be setting an index as usual.

oForm = Createobject("altcol")
oForm.Show(1)

Define Class altcol As Form
   Top = 0
   Left = 0
   Height = 254
   Width = 349

   Add Object grid1 As Grid With ;
      MemberClass = "myColumn", ;
      ColumnCount = 2, ;
      Left = 12, ;
      RecordSource = "curColor", ;
      Top = 12, ;
      Name = "Grid1", ;
      Column1.ControlSource = "curtest.iorder1", ;
      Column1.Name = "Column1", ;
      Column2.ControlSource = "curtest.iorder2", ;
      Column2.Name = "Column2"

   Add Object command1 As CommandButton With ;
      Top = 216, ;
      Left = 12, ;
      Height = 27, ;
      Width = 84, ;
      Caption = "Order1", ;
      Default = .F., ;
      Name = "Command1"

   Add Object command2 As CommandButton With ;
      Top = 216, ;
      Left = 132, ;
      Height = 27, ;
      Width = 84, ;
      Caption = "Order2", ;
      Default = .F., ;
      Name = "Command2"

   Add Object command3 As CommandButton With ;
      Top = 216, ;
      Left = 252, ;
      Height = 27, ;
      Width = 84, ;
      Caption = "No Order", ;
      Default = .F., ;
      Name = "Command3"

   Procedure Load
      * the cursor we want to display
      Create Cursor curTest(iOrder1 I, iOrder2 I)
      Insert Into curTest Values (3,4)
      Insert Into curTest Values (4,2)
      Insert Into curTest Values (2,1)
      Insert Into curTest Values (1,3)
      Index On iOrder1 Tag xOrder1
      Index On iOrder2 Tag xOrder2 Additive
      Set Order To Tag xOrder1 In "curTest"

      * the grids recordsource cursor
      * important: identical number of records
      Create Cursor curColor(iColor I)
      Insert Into curColor Values (Rgb(255,255,255))
      Insert Into curColor Values (Rgb(240,240,240))
      Insert Into curColor Values (Rgb(255,255,255))
      Insert Into curColor Values (Rgb(240,240,240))

      Select curColor
      Set Relation To Recno() Into curTest
      Go Top
   Endproc

   Procedure grid1.Init
      This.SetAll("DynamicBackColor","curColor.iColor","Column")
   Endproc

   Procedure command1.Click
      Set Order To Tag xOrder1 In "curTest"
      Go Top In "curColor"
      Thisform.grid1.SetFocus()
   Endproc

   Procedure command2.Click
      Set Order To Tag xOrder2 In "curTest"
      Go Top In "curColor"
      Thisform.grid1.SetFocus()
   Endproc

   Procedure command3.Click
      Select curTest
      Set Order To
      Go Top In curColor
      Thisform.grid1.SetFocus()
   Endproc

Enddefine

Bye, Olaf.

 

[tamayok]

OlafDoschke,

THANK YOU!!!!

I will test this over the weekend - but I can see that in theory it will work... There are some issues that will require thought/work but with this concept, they can be worked out.

For instance, if the Grid control allows deletion and SET DELETED is ON. That is a "tough" one because the CurColor cursor will need to be in sync.

The way I envision it, the CurColor cursor should be created on the fly. For values, it probably could be populated inside LOOPing code - driven by the actual data-table. Instead of RGB values, it would contain a field to reference the RECNO() of the data-table. The RELATION statement would be driven by it.

There are performance concerns due to the creation/re-creation of the CurColor with every user-deletion or re-indexing.

Therefore, it is a "wishlist item" in my book for VFP's Grid to disclose a set of properties so that no such CurColor mechanism would need to be implemented.

Kenneth Tamayo
Puerto Rico, USA

 

[tamayok]

I made some changes to the code in order to support the logic described previously.

In INIT:

PROCEDURE grid1.Init
This.SetAll("DynamicBackColor","IIF(MOD(THIS.ActiveRow,2)<>0,RGB(254,254,210),RGB(255,255,255))","Column")
ENDPROC

In LOAD:

   Procedure Load
      * the cursor we want to display
      Create Cursor curTest(iOrder1 I, iOrder2 I)
      Insert Into curTest Values (3,4)
      Insert Into curTest Values (4,2)
      Insert Into curTest Values (5,2)
      Insert Into curTest Values (2,1)
      Insert Into curTest Values (1,3)

      Index On iOrder1 Tag xOrder1
      Index On iOrder2 Tag xOrder2 Additive
      Set Order To Tag xOrder1 In "curTest"

	THIS.CurColorCreate()
	SELECT curTest
   Endproc

In custom/new CurColorCreate:

	PROCEDURE CurColorCreate
		m.cSelect = ALIAS()
		USE IN SELECT("curColor")
		CREATE CURSOR curColor (dBRecNo N(12))

		SELECT (m.cSelect)
		m.cdBRecNo = RECNO()
		GO TOP
		DO WHILE .NOT. EOF()
			m.xRecNo = RECNO()
			INSERT INTO curColor (dBRecNo) VALUES(m.xRecNo)
			SKIP
		ENDDO
		GO (m.cdBRecNo)

		SELECT curColor
		SET RELATION TO dBRecNo INTO (m.cSelect)
	ENDPROC

 

[Olaf Doschke]

Hi tamayok,

Okay, nice idea. And thanks for the start.
As the grid driving alias can have many records, and I also identified the record deletion problem while playing around with my example, perhaps it would be better to keep the displayed cursor the one driving the grid, setting the relation from there to recno(.recordsource) into curColor.

Then curColor could initially be empty. We could make use of the Dynamic... properties of the grid and call a function in it, that would add a record to curColor in case of EOF("curColor") and insert the recno("curTest") to that record. So curColor would grow dynamically with the records really displayed in the grid. Delete a record or change order? Simply ZAP curColor, it will grow from there automatically with a grid.refresh()...

Bye, Olaf.

 

[tamayok]

Thanks Olaf!

I will give that a try to that strategy when time permits again but it sounds good.

For the moment, I made some small changes (due to performance) in order to implement the previous concept:

> SET TALK OFF (during CurColorCreate)
> Limited the functionality to tables with RECCOUNT()<100001

There one problem though... when one scrolls using the MOUSEWHEEL/SCROLL (rather than using the keyboard), the color effect is not triggered/visible.

 

[Olaf Doschke]

Yes, the problem is, the scrollbar does not really belong to the grid control and activerow then is 0, so no coloring. That's why my strategies make use of a cursor and not of activerow.

Bye, Olaf.

 

[tamayok]

Olaf,

Does your grid show alternating colors when you mouse-scroll (and "curTest" contains a couple hundred records)?

 

[Olaf Doschke]

Hi tamayok,

Yes, mousescroll does not hurt. But it still has a small problem: If you drag the slider down several pages and scroll back with the uparrow button, then two rows with the same color can be adjacent.

Run this and see for yourself:

oForm = Createobject("altcol")
oForm.Show(1)

Define Class altcol As Form
   Top = 0
   Left = 0
   Height = 254
   Width = 349
   
   Add Object grid1 As altcolGrid With ;
      ColumnCount = 2, ;
      Left = 12, ;
      DeleteMark = .F., ;
      Top = 12, ;
      Name = "Grid1", ;
      Column1.ControlSource = "curtest.cText", ;
      Column1.Name = "Column1", ;
      Column2.ControlSource = "curtest.cText2", ;
      Column2.Name = "Column2"

   Add Object command1 As CommandButton With ;
      Top = 216, ;
      Left = 12, ;
      Height = 27, ;
      Width = 84, ;
      Caption = "Order1", ;
      Default = .F., ;
      Name = "Command1"

   Add Object command2 As CommandButton With ;
      Top = 216, ;
      Left = 132, ;
      Height = 27, ;
      Width = 84, ;
      Caption = "Order2", ;
      Default = .F., ;
      Name = "Command2"

   Add Object command3 As CommandButton With ;
      Top = 216, ;
      Left = 252, ;
      Height = 27, ;
      Width = 84, ;
      Caption = "Delete", ;
      Default = .F., ;
      Name = "Command3"

   Procedure Load
      * the cursor we want to display
      Create Cursor curTest(cText C(1), cText2 C(1))
      Local lnCount
      For lnCount= 1 to 400
          Insert Into curTest Values (Chr(Rand()*26+65),Chr(Rand()*26+65))
      Next
      Index On cText  Tag xOrder1
      Index On cText2 Tag xOrder2 Additive
      Set Order To Tag xOrder1 In "curTest"
   Endproc

   Procedure Init()
      Thisform.Grid1.RecordSource = "curTest"
   EndProc 

   Procedure command1.Click
      Thisform.grid1.zapColorcursor()
      Set Order To Tag xOrder1 In "curTest"
      Go Top In curTest
      Thisform.grid1.SetFocus()
   Endproc

   Procedure command2.Click
      Thisform.grid1.zapColorcursor()
      Set Order To Tag xOrder2 In "curTest"
      Go Top In curTest
      Thisform.grid1.SetFocus()
   Endproc

   Procedure command3.Click
      Delete Next 1 in curTest
      Thisform.grid1.zapColorcursor()
      Thisform.grid1.SetFocus()
   EndProc
   
EndDefine

Define Class altcolGrid as Grid
   iGridbackcolor = 0

   Procedure Init()
      This.SetAll("DynamicBackColor","This.getGridbackcolor()","Column") 
   EndProc
   
   Procedure getGridbackcolor()
      #Define cnColor1 Rgb(254,254,210)
      #Define cnColor2 Rgb(255,255,255)
      
      If Eof("curColor")
         If This.iGridbackcolor = cnColor1
            This.iGridbackcolor = cnColor2
         Else
            This.iGridbackcolor = cnColor1
         EndIf 
         Insert Into curColor values (Recno("curTest"),This.iGridbackcolor)
         Return This.iGridbackcolor
      Else
         Return curColor.iColor
      EndIf
   EndProc
   
   Procedure Recordsource_assign()
      Lparameters cRecordsource
      This.RecordSource = cRecordsource

      * the related color cursor
      Create Cursor curColor(iRecno I, iColor I)
      Index on iRecno Tag xRecno

      Select (cRecordsource)
      Set Relation To Recno() Into curColor
      Go Top
   Endproc

   Procedure zapColorcursor()
      Set Safety Off
      Zap in curColor
      Set Safety On
   EndProc 
EndDefine

Bye, Olaf.

 

[tamayok]

Olaf,

THAT'S BEAUTIFUL MAN!!! It is FAQ-ready; great job!
Can't wait to implement it!!!

THANKS!!!

Kenneth

 

[Olaf Doschke]

Hi Kenneth,

As I said it still has it's problem. IT only colors the visible records, as the DynamicBackColor only has effect on the displayed records. So it's fast, as curColor can grow as you scroll through the data.

The problem occurs, if you scroll several pages at once, skipping these pages. Îf you skip an odd number of records then the coloring will fail, but you only see that failure, when you scroll up and two adjacent records are colored the same.

I think this problem can be minimized, if the grid displays an even number of records, as you skip a number of pages with the scrollbar slider, you'd always skip an even number of records then. But I wonder, what would happen, if the displayed cursor has an odd number of records.

While displaying records, the getGridbackcolor()-Method could also ensure returning alternating colors by comparing with the last returned color and if two adjacent records of curColor have the same color correct that problem at that moment.

Bye, Olaf.