Contact US

Question

How to color row grid in alternate shade?

Mandy_crw (Programmer)

(OP)

28 Aug 23 05:02

Hi everyone.... I am designing a POS system currently... everything is almost working, but i wanted the row to change shade evertime an item is added...(as shown in the picture)

Would everyone teach me please and show me an example code for me to study and probably adapt to my system? Thanks in adavnce and God bless...

mjcmkrsr (TechnicalUser) · Answer 1 · 2023-08-28 06:24:00

Dearest Mandy,

Please have a look at DYNAMICBACKCOLOR()

Furthermore, there are quite a few code examples in this forum dealing with this subject, e.g.

CODE -->

*!*	grid_calculatedcolumn.prg
PUBLIC oform1

oform1=NEWOBJECT("form1")
oform1.Show
Read Events

Close all
Clear All

RETURN


**************************************************
DEFINE CLASS form1 AS form
	AutoCenter = .T.
	Caption = "Grid with calculated columns"
	Width = 570
	Height = 420
	MinHeight = This.Height
	MinWidth = This.Width
	MaxWidth = This.Width
 
	ADD OBJECT grid1 AS grid WITH ;
		ColumnCount = -1, ;
		Left = 10, ;
		Top = 36, ;
		Width = 300, ;
		Height = ThisForm.Height - 42, ;
		RecordSource = "csrSalesData", ;
		Anchor = 15, ;
		ReadOnly =.T.
 
		PROCEDURE grid1.Init
			 WITH This.Column1
				.Header1.Caption = "ID"
			 ENDWITH

			 WITH This.Column2
				.Header1.Caption = "Date"
			 ENDWITH

			 WITH This.Column3
				.Header1.Caption = "Amount"
			 ENDWITH
		 ENDPROC 
		 
	ADD OBJECT grdWeeklySales AS grid WITH ;
		Left = 324, ;
		Top = 36, ;
		Width = 168, ;
		Height = 126, ;
		DeleteMark = .F., ;
		RecordMark = .F., ;
		ScrollBars = 0, ;
		ReadOnly =.T., ;
		Visible = .F.
 
	ADD OBJECT lblDate as Label WITH ;
		Left = 324, Top = 9, Caption = "Enter date :", AutoSize = .T.

	ADD OBJECT txtDate as TextBox WITH ;
		Left = 408, Top = 6, Width = 84, Value = DATE()

	ADD OBJECT lblSalesMonth as Label WITH ;
		Left = 324, Top = 174, Caption = "Sales", Autosize = .T., FontBold = .T., Visible = .F.

 	ADD OBJECT cmdUndo AS CommandButton WITH ;
		Left = 120, Top = 6, Height = 24, Caption = "O-Data"
	
		PROCEDURE cmdUndo.Click()
			With ThisForm.Grid1
				.Visible = .T.
				.ColumnCount = -1
				.Recordsource = "csrSalesData"
				
				.Column1.Header1.Caption = "ID"

				.Column2.Header1.Caption = "Date"

				.Column3.Header1.Caption = "Amount"
			ENDWITH

			WITH ThisForm
				.lblSalesMonth.Caption = ""
				.grdWeeklySales.Visible = .F.
				.Refresh()	
			ENDWITH 
		ENDPROC

	ADD OBJECT cmdDoit AS CommandButton WITH ;
		Left = 10, Top = 6, Height = 24, Caption = "Calculate"
	
		PROCEDURE cmdDoit.Click()
			Local Array laSumMonth[1]
			
			Select dSalesDate, INT((DAY(dSalesDate) - 1)/7) + 1 as iWeek, ySalesAmount ;
				FROM csrSalesData ;
				WHERE YEAR(dSalesDate) = YEAR(ThisForm.txtDate.Value) AND MONTH(dSalesDate) = MONTH(ThisForm.txtDate.Value) ;
				ORDER by 1 INTO CURSOR csrTemp 
			
			LOCATE
		
			IF RECCOUNT("csrTemp") > 0
			
				SELECT iWeek, SUM(ySalesAmount) as yWeeklyTotal FROM csrTemp GROUP BY 1 INTO CURSOR csrWeeklyTotals
				SELECT SUM(ySalesAmount) FROM csrTemp INTO ARRAY laSumMonth

				With ThisForm.Grid1
					.ColumnCount = -1
					.Recordsource = "csrTemp"
					.SetAll("DynamicBackColor", "IIF(MOD(iWeek, 2) = 1, RGB(0,255,255), RGB(0, 255, 0))", "Column")
					.Column1.Header1.Caption = "Date"

					.Column2.Header1.Caption = "Week"
					.Column2.Text1.Inputmask = "9"

					.Column3.Header1.Caption = "Amount"
					.Column3.Text1.Inputmask = "999.9999"
					
				ENDWITH 
				
				WITH ThisForm.grdWeeklySales
					.ColumnCount = -1
					.RecordSource = "csrWeeklyTotals"
					.SetAll("DynamicBackColor", "IIF(MOD(iWeek, 2) = 1, RGB(0,255,255), RGB(0, 255, 0))", "Column")
					.Visible = .T.
									
					.Column1.Header1.Caption = "Week"

					.Column2.Sparse = .F.
					.Column2.Header1.Caption = "Total"
					.Column2.Text1.Inputmask = "999,999.9999"
				 ENDWITH  

				WITH ThisForm
					.lblSalesMonth.Visible = .T.
					.lblSalesMonth.Caption = "Sales in " + CMONTH(ThisForm.txtDate.Value) + " " + ALLTRIM(STR(YEAR(ThisForm.txtDate.Value))) + " : € " + ALLTRIM(TRANSFORM(laSumMonth[1], "999,999,999.9999"))
					.Refresh()
				ENDWITH 
			ELSE
				WITH ThisForm 
					.Grid1.Visible = .F.
					.grdWeeklySales.Visible = .F.
					.lblSalesMonth.Visible = .F.
				ENDWITH 
			
				= MESSAGEBOX("No records found!", 48, "Weekly totals", 3000)

				ThisForm.cmdUndo.Click()
			
			ENDIF
			
		ENDPROC

PROCEDURE Destroy
	Thisform.Release()
	CLOSE ALL
	Clear Events
ENDPROC

PROCEDURE Load
	LOCAL lnI
	
	CREATE CURSOR csrSalesData (Id I AUTOINC, ;
	   dSalesDate D DEFAULT DATE(YEAR(DATE()), 1, 1) + RAND() * 3600, ;
	   ySalesAmount Y DEFAULT Rand() * $500.00)
   
	For lnI = 1 To 7200
	   Append Blank
	EndFor
	
	LOCATE 
	
ENDPROC

ENDDEFINE
*********************************************

hth

MarK

Tore Bleken (Programmer) · Answer 2 · 2023-08-28 06:27:57

From the top of my head:

Yourgrid.Setall(DynamicBackColor,"IIf(recno('yourtable')%2=0,rgb(255,128,128),rgb(128,255,128))",'column')
Yourgrid.Setall('sparse',.F.,'column')

Mike Lewis (Programmer) · Answer 3 · 2023-08-28 08:38:15

Mandy,

As the others have indicated, the secret is to use the DynamicBackColor property. (Note that this is a property of the column, not of the grid.)

If the underlying table or cursor has no index in force, and if it does not contain any deleted records, and if there is no filter in force, then the code is easy:

CODE -->

this.SetAll("DynamicBackColor", ;
  "IIF(MOD(RECNO('MyTable'), 2) = 0, ;
    RGB(255, 255, 255), RGB(255, 255, 160))")

Put that code in the grid's Init. That's all you need.

But if the above conditions don't apply (and especially if the cursor is indexed), then it is slightly more complicated. In that case, you won't be able to rely on RECNO() in the above code, so you will need to add a field to the cursor that serves in its place. It should be a numeric field, and you will need to populate it with consecutive integers. Having done that, the code will be similar to the following:

CODE

this.SetAll("DynamicBackColor", ;
  "IIF(MOD(MyTable.CounterField, 2) = 0, ;
    RGB(255, 255, 255), RGB(255, 255, 160))")

where MyTable.CounterField is the field in question.

In both the above examples, the RGB() values determine the actual colours shown. You can experiment with these to find the combination of colours that you like best.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

Mike Lewis (Programmer) · Answer 4 · 2023-08-28 12:06:54

Quote (Me)
if the above conditions don't apply (and especially if the cursor is indexed), then it is slightly more complicated.

In that case, an easier solution might be to create a cursor specifically for populating the grid. Create the cursor like this: SELECT * FROM MyTable INTO CURSOR csrGrid - adding any ORDER BY or WHERE clause that is relevant. Then use the cursor as the RecordSource of the grid. And, finally, put this code in the grid's Init:

CODE

this.SetAll("DynamicBackColor", ;
  "IIF(MOD(RECNO('csrGrid'), 2) = 0, ;
    RGB(255, 255, 255), RGB(255, 255, 160))")

That should work even if the original table has an index set, or has deleted records, or a filter in force.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

Chris Miller (Programmer) · Answer 5 · 2023-08-28 12:11:14

Mike,
you pointed out a concern I also would point out.

You can only alternately color a grid based on recno(), if records are in record number order, otherwise this will look chaotic and randomly colored. As coloring is based on odd/even record numbers. but in other sort orders like ordered by product type, for example, odd and even record nummbers don't necessarily alternate anymore in that order.

Quote (Mandy)
everytime an item is added.

Mandy,

If I assume you have a cursor of items feeding the grid in the form and starting empty and you add items, they will stay in record number order and all the advice given will work out fine. But things can become out of alternate order, if you delete items. Let's look at an example. Say you have 3 items with record numbers 1-3, then deleting the second item the remaining records don't get renumbered, they keep their record number 1 and 3, both odd and both have the same color. If you delete the last record and the remaining 1 and 2 are still alternating in even/oddness and so also in their color, that's fine at first, but when you add the 4th item it will mean recno 4 follows recno 2 and has the same color. So alternation of colors can easily break.

There are some ways to keep control, for example as Mike suggests: Using your own counterfield.

But I suggest a very simple neat trick that doesn't require working with dynamicbackcolor. Put an image control on top of the grid in grid size with alternating colors that allow the grid rows to show through by using transparency. Say you use a mid to dark blue but with 80%-90% transparency and a 100% transparent stripe the grid then can be kept at its default white color and you generate the light blue by the transparency.

There's a big bonus to this approach, too: It'll color the rows no matter if it starts with no items or not. The only thing that should be prevented is scrolling pixelwise, you should never show the grid scrollbars but add buttons that scroll the items b full rows only. That's the only thing that makes this approach a bit more complicated than using dynamicbackcolor. But it's easy enough, as you have the doscroll method of the grid.

Do you use the gdiplusx library for anything in your application, Mandy? Then this could also be used to generate such a PNG image file on the fly and with the dimensions necessary to fit the grids rowheight and number of rows visible. It's worth doing, as it unbinds you about any headaches about the row numbering. You just have to react to resizing of the grid and the rowheight, though. But that may not be enabled aside from adapting to screen size once at start of the POS system. That only points out that it would be best to create such a PNG with the help of the gdiplusx library whenever you know the dimensions of the grid and its rowheight.

Here's GDIPlusX:
https://github.com/VFPX/GDIPlusX

Chriss

Chris Miller (Programmer) · Answer 6 · 2023-08-28 13:08:09

Here's how to create such a PNG with GDIPlusX:

1. start gdiplusx by doing System.app, usually somewhere early in main.prg, for example:

CODE

Do System.app

2. code to create a png with 12 rows, each 24 pixels height in white (100% transparency) and blue (75% transparency):

CODE

#Define ROW_HEIGHT 24
#Define ROW_WIDTH 800

With _Screen.System.Drawing As xfcDrawing

   loPNG = .Bitmap.New(ROW_WIDTH , 12*ROW_HEIGHT, .Imaging.PixelFormat.Format32bppARGB)
   loGfx = .Graphics.FromImage(loPNG)

   * Create array of rectangles.
   Local Array laRects(6)
   For lnRow = 0 To 5
      laRects(lnRow+1) = .Rectangle.New(0,  lnRow*2*ROW_HEIGHT, ROW_WIDTH , ROW_HEIGHT)
   Endfor

   bgColor = .Color.New(0) && RGBA=0 means alpha=0, which is fully transparent
   bgBrush = .SolidBrush.New(bgColor)
   loGfx.FillRectangles(bgBrush, @laRects)
   bgBrush.Dispose()

   Local Array laRects(6)
   For lnRow = 0 To 5
      laRects(lnRow+1) = .Rectangle.New(0,  (lnRow*2+1)*ROW_HEIGHT, ROW_WIDTH, ROW_HEIGHT)
   Endfor

   bgColor = .Color.FromARGB(64, 0, 0, 255) && 64 means 25% opaque or 75% transparent
   bgBrush = .SolidBrush.New(bgColor)
   loGfx.FillRectangles(bgBrush, @laRects)
   bgBrush.Dispose()

   loPNG.Save('alternateingrows.png')
Endwith

Here's that PNG:

Chriss

Mandy_crw (Programmer) · Answer 7 · 2023-08-28 13:16:11

Hi to everyone...
Thank you mjcmkrsr, Tore Bleken, Chris Miller... and Mike....
I really appreciate all your answers... and at the same time... i am learning... as i always do when you give answers... Ive gotten what Mike gave... my grid looks good and well pleasing to the eyes... Thanks again everyone.. God bless....

Chris Miller (Programmer) · Answer 8 · 2023-08-28 13:41:27

Mandy,

fine. Just watch out what it looks like when you delete an item from the list and then add a new one. As said, the coloring based on recno() can easily break and become non-alternating. Mikes recommendation of using an extra field MyTable.CounterField could solve that, if you renumber it every time the number of items change, just loop all items and sequentially number them. It would also work to alternate between the values 1 and 2 or 0 and 1. Or you make that a logical field, say LogicalField, alternating between .T. and .F. like this routine does:

CODE

Local llColorOrNot
Select MyTable
Scan
replace LogicalField with llColorOrNot
llColorOrNot = NOT llColorOrNot
Endscan

This would also work when sorting data by an index, as SCAN goes from first to last record in index order.

And change the dynamicbackcolor expression to

CODE

this.SetAll("DynamicBackColor", ;
  "IIF(MyTable.LogicalField, ;
    RGB(255, 255, 255), RGB(255, 255, 160))")

Chriss

Mandy_crw (Programmer) · Answer 9 · 2023-08-28 19:35:58

Oh i see… thank you so much chriss… ill try ur code… godbless…

Mandy_crw (Programmer) · Answer 10 · 2023-08-30 06:18:17

Hi everyone.... Im so glad that you have helped me... my appilcation is working now with alternate shade of the row... Thanks and God bless....

Mike Lewis (Programmer) · Answer 11 · 2023-08-30 07:52:28

Good to hearing it's working, Mandy. Just out of curiosity, which solution did you use?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

Chris Miller (Programmer) · Answer 12 · 2023-08-30 09:41:56

Mike,

Likely not my idea of using an image to color the grid rows. I can understand it's a bit overwhelming if you never used gdiplusx and also I just showed how to create the image file and only sketched how to use it in front of the grid. There are things you need to take into account like the offset of headers and recordmark and deletemark of the grid to position the image.

Here's an example derived from Marks using an Items cursor and the more usual dynamiccolor approach.

Remove the call to Thisform.AlternateColoring() in the PROCEDURE cmdDeleteItem.Click() code and you'll see how the alternate coloring breaks, if you don't refresh the lAlternate field of the items cursor.

CODE

PUBLIC oform1

oform1=NEWOBJECT("form1")
oform1.Show
Read Events
Clear All

RETURN

**************************************************
DEFINE CLASS form1 AS form
	AutoCenter = .T.
	Caption = "Grid with alternate coloring"
	Width = 570
	Height = 420
	MinHeight = This.Height
	MinWidth = This.Width
	MaxWidth = This.Width
 
	ADD OBJECT grid1 AS grid WITH ;
		ColumnCount = 2, ;
		Left = 10, ;
		Top = 36, ;
		Width = 180, ;
		DeleteMark=.F.,;
		Height = ThisForm.Height - 42, ;
		RecordSource = "crsItems", ;
		Anchor = 15, ;
		ReadOnly =.T.

 	ADD OBJECT cmdAddItem AS CommandButton WITH ;
		Left = 20, Top = 6, Height = 24, Caption = "Add Item"
	
		PROCEDURE cmdAddItem.Click()
		     Append Blank in crsItems
		     nRecno = Recno()
		     Thisform.AlternateColoring()
		     Locate
		     thisform.grid1.setfocus()
		     Go nRecno
		ENDPROC

 	ADD OBJECT cmdDeleteItem AS CommandButton WITH ;
		Left = 120, Top = 6, Height = 24, Caption = "Delete Item"
	
		PROCEDURE cmdDeleteItem.Click()
		     Delete in crsItems
		     nRecno = Recno()
		     Thisform.AlternateColoring()
		     Locate
		     thisform.grid1.setfocus()
		     Go nRecno
		ENDPROC
 
		PROCEDURE grid1.Init
			 WITH This.Column1
				.Header1.Caption = "ID"
			 ENDWITH

			 WITH This.Column2
				.Header1.Caption = "ItemName"
			 EndWith
			 
			this.SetAll("DynamicBackColor", "IIF(lAlternate,RGB(255, 255, 255), RGB(255, 255, 160))") 
		 ENDPROC 
		 
PROCEDURE Destroy
	Thisform.Release()
	Clear Events
ENDPROC

PROCEDURE Load
    Set Deleted on
	CREATE CURSOR crsItems (Id I AUTOINC, cItemName c(20) default "item "+Transform(id), lAlternate L)
EndProc

Procedure AlternateColoring()
    thisform.LockScreen=.t.
    Select crsItems 
    Local llColorOrNot
    Scan
       Replace lAlternate with llColorOrNot
       llColorOrNot = NOT llColorOrNot
    EndScan
    thisform.LockScreen=.f.
Endproc

ENDDEFINE
*********************************************

Also notice, how the grid is all white without any items and is always white in rows with no data, which is because coloring is only applied to rows with data, there is no dynamicbackcolor commputed for rows without data, not only because the IIF expression depends on a field, empty rows are always drawn with default backcolor and only gridlines.

The advantage of dynamicbackcolor is, that this coloring obviously scrolls with the data, whereas an image in front of the grid stays in position and that's getting obvious when you scroll - only the data scrolls, not the coloring.

It's a matter of taste what you want to have, using empty records to populate all grid rows with colors can be a solution to that, but has its con, too, as scrolling up still makes the lower portion of the grid white.

Chriss

Mandy_crw (Programmer) · Answer 13 · 2023-08-30 14:21:41

Hi Mike… I've what you have suggested… thanks Mike for always helping…

Chriss… this is another approach… i will atudy it… thanks to you… as always…

Chris Miller (Programmer) · Answer 14 · 2023-08-30 16:14:59

Mandy,

this is just a full example of the idea I suggested to use a logical field and a routine to refresh that whenever a record is added or removed, as otherwise, you risk breaking the alternate coloring.

There are some corner cases where deleting doesn't break the alternate coloring, if you delete the last or first item. And there's the even simpler corner case you always only add records and never delete them. Then just the new record needs the new logical opposite than the last or the next number in a numbering sequence as Mike suggested. But in the general case you simply at best repopulate the field that's responsible for the alternating colors, that's the easiest way to never let it break the look.

I asked you to test the case of deleting an item. Did you test that? Because unless it's just the last item you delete one row of a color between two rows of the same color and that means after the row is removed two rows with the same color remain. I thank you for always being thankful for all we post, but you rarely really react and give feedback to questions and recommendations, that's missing and I fear you only end up with something that somwhat works, but hasn't all the details necessary.

And in a POS system, if the item list is populated by scanning barcodes, the deletion of an item is a rarely used scenario, but you'll regret, if that happens and the alternating color look breaks. So in general, think about corner cases in your design and test whether they break even just the design you have in mind, not only by showing up errors. Otherwise you can live with that, it just will have quirks. In the long run, such things lead to the rejection of a system, if people find better designed systems. And POS and VFP is not a rare combination, others already did POS systems with VFP, too, so you have a big competition and a lot of contenders to replace your system, not only just the VFP based systems.

Chriss

Girish Solanki (Programmer) · Answer 15 · 2023-08-31 04:38:41

Incremental Search all string without space and colour H

ighlights

https://youtu.be/RGmrxxQNiDo?si=CWnzIjDYqte-DzJk

Spoiler:

Tore Bleken (Programmer) · Answer 16 · 2023-08-31 05:43:06

Girish,
please don't post the same message multiple times.

Mandy_crw (Programmer) · Answer 17 · 2023-09-01 12:09:01

Hi Chriss... Thank you giving ample explanations of these... yes Chriss I tested deleting an item from the grid... and for some reason it did not show desame row color... it maintained an alternate shading in the grid... ill show excerpt from my code in the part that used the ssugested code of Mike...

SELECT * from BINILI WHERE qty => 1 INTO CURSOR junk

SELECT BINILI
ZAP
APPEND FROM DBF("junk")
USE IN SELECT("junk")

thisform.Grid1.SetAll("DynamicBackColor", ;
"IIF(MOD(RECNO('Binili'), 2) = 0, ;
RGB(255, 255, 255), RGB(255, 255, 160))")

Thisform.grid1.refresh

this.Value = ""
thisform.text2.Value = 1
UNLOCK IN binili
RETURN 0

Thanks Chriss... God bless...

Mike Lewis (Programmer) · Answer 18 · 2023-09-01 12:32:35

Mandy,

The code you posted more or less matches what I had in mind. I'm pleased to see it was helpful.

Just one very minor point. Your code includes UNLOCK IN binili. Strictly speaking, that's unnecessary, because BINILI is, by necessity, opened exclusively. If it wasn't, you wouldn't be able to do the ZAP. This makes no difference to how your code works, but I thought I would point it out as it suggests a possible misunderstanding on your part.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

Chris Miller (Programmer) · Answer 19 · 2023-09-01 14:02:18

I see, you're complicating how to maintain your list of items, though.

CODE

SELECT * from BINILI WHERE qty => 1 INTO CURSOR junk

SELECT BINILI
ZAP
APPEND FROM DBF("junk")
USE IN SELECT("junk")

This part of your code ensures that you remove items from BINILI, if their qty was lowered to 0. The normal way to delete one record is just using the DELETE command.

I don't mind, it actually helps the coloring by recno to be stable and not break, so you "accidentally" have a solution that is stable for coloring. But lets think about extreme cases, if you work on data in a large table this way and remove items with qty<=0 by a Select of all items with qty=>1 and then a zap and append, you do a lot of moving data around, that could simply stay were it is.

Is it that you don't know how to suppress the display of deleted records? You simply SET DELETED ON. And then a delete makes a record invisible.

Chriss

Mike Lewis (Programmer) · Answer 20 · 2023-09-01 14:24:30

Chris, you're right, of course, about SET DELETED ON. But the point is that Mandy is using RECNO() to control the shading of alternating rows, and that method only works if there are no deleted records (regardless of the setting of SET DELETED).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

Chris Miller (Programmer) · Answer 21 · 2023-09-01 14:38:45

Well, or using an extra field, Mike, as you suggested yourself and as my demo shows, too. I find it a little bit more disturbing to zap an alias and fill it with all minus one record that was in it before, than having an extra field you set for the alternating of the colors.

Chriss

mjcmkrsr (TechnicalUser) · Answer 22 · 2023-09-02 06:23:56

Hi,

May I add my comments.

Why would you take this detour by filtering the data, ZAPping the underlying table and append the filtered data back to that table? What happens to the lost data? Isn't it more straightforward to filter the data into a cursor and use this cursor as RECORDSOURCE for the grid, since it is in natural order?

DYNAMICBACKCOLOR is a property of the column and not the grid - hence if you use SETALL at the grid level you have to add the COLUMN parameter

CODE -->

SELECT * from BINILI WHERE qty => 1 INTO CURSOR csrBinili
thisform.Grid1.SetAll("DynamicBackColor", "IIF(MOD(RECNO('csrBinili'), 2) = 0, RGB(255, 255, 255), RGB(255, 255, 160))","Column")

Finally please consider the warning about the ZAP command from the Hacker's Guide

Quote:

ZAP

This may be the single most dangerous command in all of FoxPro, but it's incredibly handy when you're working interactively. ZAP permanently removes all records from a table. The name supposedly stands for "Zero And Pack." ZAPped records cannot be recalled.

Usage

ZAP [ IN cAlias | nWorkArea ]

Although you can ZAP in a work area other than the current one, we really don't recommend it. Using this command by itself is like striking matches; using it in another work area is like striking matches in a gas station.

ZAP is not a good database citizen. It doesn't call the Delete trigger for a table in a database. Instead, it neatly avoids all the work you've done to make sure the integrity of your database is maintained. This is a major flaw.

Due to the above and for lots of other reasons, never use ZAP in a program. It's just too risky. See PACK for suggestions on alternatives. Actually, the one case where ZAP is acceptable is when you're working on a temporary file in the first place, so no permanent data is at risk. In particular, it's a great way to re-create a cursor without losing the grid that's based on it. Simply ZAP and APPEND FROM your data instead of doing a SELECT.

So why do we think it's incredibly handy? When you're manipulating data manually—perhaps parsing older data to create a normalized database—it's clean and simple to ZAP the target table between tests. Outside this kind of situation, we strongly recommend you avoid ZAP.

hth

MarK

Mike Lewis (Programmer) · Answer 23 · 2023-09-02 07:46:27

Mark,

The reason for the "detour" is as follows:

If you set the RecordSource to a specific cursor, and then regenerate the cursor (typically by issuing a SELECT with a WHERE clause), VFP first destroys the existing cursor, then builds a new one. So, for a brief moment, the cursor does not exist. Because the columns within the grid are bound to the cursor, destroying the cursor will rob the grid of the controls. The result is an empty rectangle: a grid with no columns.

With Mandy's solution, the cursor (or table) stays in existence throughout the process, so the problem does not arise.

At least, that's always been the approach I've taken. There may well be a simpler way of achieving the goal.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

Chris Miller (Programmer) · Answer 24 · 2023-09-02 08:06:46

I wonder if I once told you about using ZAP in grid cursors. They are part of a technique called "safe select".

Maybe you got it from https://andykramek.blogspot.com/2020/04/using-safe..., where Andy Kramek coined the term, perhaps, he's also using the alias name "junk" for a temporry cursor. This technique is meant to use, if your grid recordsource is an SQL query. And you repeatedly need to requery with different parameters or where clause. You always just zap a grid cursor, never a DBF, for the safety reasons and concerns MarK points out from Tamar Granors Hacker's Guide.

You artificially make up the situation by using a query to fill your BINILI alias from itself. Well, that's pointing out you actually don't need this. The safe select situation is doing these steps:
1. Create a grid cursor, Andy Kramek showcases this as creation of such a cursor

CODE

*** Create the working cursor
SELECT * FROM account WHERE 0 = 1 INTO CURSOR curacct READWRITE

2. Whenever other data should be listed, query that into cursor junk, ZAP in curacct and APPEND FROM DBF('junk').

The aim here is to show a list of accounts, and the UI allows users to filter for account in different ways, which could be done with further queries in the same structure, just with other where clauses than 0=1, which is in short meaning .f., so the initial curacct cursor is empty. Every future list has other filter criteria and always stems from the same type of query from all data in account.dbf, but doing this directly again INTO CURSOR curacct would cause the grid to lose its structure. So you select into a temporary junk cursor, zap the grid cursor and append the new result to scrap it afterwards. But also you depend on a query, as one list miight be all "meyer" accounts and the next all with a certain minimum amount in their depot. The lists change arbitrarily, the source of data always is account.dbf and the previous list is not needed anymore.

In your situation, you also start with an empty list. But you don't query different lists of products with varying where clauses, do you? You know every barcode scan or every pick of a user in an online shop to put something into the items list will add one single record to the already existing list, or up the qty by one. So in your situation you don't change from say select * from products where barcodde=x to select * from products where barcodde=y, you just add the one record of the barcode to your already existing list. And the easiest way to do this is start with an empty grid cursor you createe by a CREATE CURSOR statement and then insert into it and delete from it as you go, but you never reselect it, you don't even initially select data into it. Heere you don't even need an SQL query like I gave with where barcode=x, you can just SEEK x to go to the product with a certain barcode and then insert into curitems from fields of the product table.

This situation doesn't require safe select, it's over the top for this and it's nonsensical as you use the grid cursor as the source of the data for the grid cursor, you already have the grid cursor and all its data, you just need to either change qty, if it drops to 0 delete the record and if a record with the scanned barcode doesn't exist yet (the regular case, indeed) insert it. You always only have operations on one record, you don't ever pick a completely different list of source data. That's where you are safe by never needing to recreate the grid cursor, once it is created, it is only modified and stays the same file, the same dbf file or the same tmp file. See the section in Andy Krameks blogpost where he demonstrates the tmp file name of the cursor with the same alias name changes, which is the core reason the grid misbehaves and you need this technique. Where that's not the case you also don't need safe select.

Chriss

mjcmkrsr (TechnicalUser) · Answer 25 · 2023-09-02 08:29:35

Hi Mike

Quote:

... So, for a brief moment, the cursor does not exist. Because the columns within the grid are bound to the cursor, destroying the cursor will rob the grid of the controls. The result is an empty rectangle: a grid with no columns. ...

True, but this hardly noticeable and you could even add LOCKSCREEN to hide/show the updating process. And I guess it will take less time than filtering the data, ZAPing the table and appending the filtered data again to the table - with all the hick-ups that could arise during these procedures. When running my code snippet (see above), the grid is regenerated in less than 0,00 seconds.

Quote:

With Mandy's solution, the cursor (or table) stays in existence throughout the process, so the problem does not arise.

Maybe, but the question remains: what about the lost data while filtering and then ZAPping the table?

MarK

Mandy_crw (Programmer) · Answer 26 · 2023-09-02 09:01:59

Hi everyone... Thank you all for your discussions... it made me aware of a lot of things... i dont know guys but i have used DELETE and SET DELETED ON, but it gave me some problems... in my table even if i delete the item, when i add the same itemcode that has been deleted for edit, it shows two record with the same idnum, so what i tried to do is after deleting i issue PACK command... so i no longer use delete...

Chriss, ill try the snippet that you have given and try using the DELETE and SET DELETED ON.... Thank you always...

Thank you mjcmkrsr and Mike as always...

mjcmkrsr (TechnicalUser) · Answer 27 · 2023-09-02 09:26:36

Hi Mandy,

Quote:

... in my table even if i delete the item, when i add the same itemcode that has been deleted for edit, it shows two record with the same idnum, so what i tried to do is after deleting i issue PACK command

This topic has already been discussed in thread184-1825134: How to use UPDATE properly…?. Did you follow the advice given there?

hth

MarK

Mike Lewis (Programmer) · Answer 28 · 2023-09-02 15:20:26

Quote (Mark)
True, but this hardly noticeable and you could even add LOCKSCREEN to hide/show the updating process. And I guess it will take less time than filtering the data, ZAPing the table and appending the filtered data again to the table - with all the hick-ups that could arise during these procedures. When running my code snippet (see above), the grid is regenerated in less than 0,00 seconds.

Mark,

It's not a question of how long it takes, nor of the visual effect. It's more to do with the grid losing track of the columns to which the data is bound.

I appreciate that I have not explained it very well. I'll try to rig up an example which shows what I mean.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

mjcmkrsr (TechnicalUser) · Answer 29 · 2023-09-02 15:48:44

Hi Mike,

Quote:

It's more to do with the grid losing track of the columns to which the data is bound.

I fully understand. But it is a matter of choice: I prefer rearranging the columns of the grid to filtering/zapping/appending, which imho is quite error prone.

Using my code as an example : what would you change when applying your approach?

MarK

Chris Miller (Programmer) · Answer 30 · 2023-09-02 20:29:43

Quote (Mandy_crw)
when i add the same itemcode that has been deleted for edit, it shows two record with the same idnum

That shouldn't happen. What is your code, have you used APPEND or INSERT and RECALL both? You could reactivate a deleted record with recall, replace the qty and have it back. Or you can disregard the deleted record and insert it again. Unless you created a primary key indexx or candidate inde, that doesn't allow duplicates.

For a cursor you use in a form for temporary data, the accumulating list of sales items, you don't need to define standards like a primary key in them, it's just temporary list and its even a matter of taste whether an item is listed with qty=2 or just appears twice, I experience that daily with my groceries, it just depends on the clerks taste how they scan the items. And so actually a primary key is just hindering very normal use cases like that. Primary keys are important and I point that out often, but for a temporary list it' not yet permanent data in your dataabase, that's happeniing wwhen the items are actually sold, surely then you save the list of items and make your log also of the money or credit card transaction,, keep inventory, etc. But before that I wouldn't even make a barcode field a cnadidate to forrce unniqueness.

Well, and generally here's just a short code example showing that inserting and deleting the same record even multiple times doesnt cause an problms, conflictss or double listed records. Just have SET DDELETD ON and that means VFP takes the deletion mark into account and doesn't show records marked for deletion.

CODE

Set Deleted On
Create Cursor crsItems (Id I Autoinc, cItemName c(20) Default "item "+Transform(Id), lAlternate L)
Insert Into crsItems (cItemName) Values ('some item')
Delete
Insert Into crsItems (cItemName) Values ('some item')
Delete
Insert Into crsItems (cItemName) Values ('some item')
? reccount()
Browse

You can insert (or append, doesn't matter) and then delete as many times as you like. The cursor temp file will also actually grow in reccount, but all deleted records are not shown. To see, that this same item is 3 times in the cursor, you can SET DELETED OFF and convince yourself of it. But no, you don't have a problem even in case something like a customer changing his mind for 10 times would happen. VFP would be more patient than the clerk, for sure.

You have to do something to get into trouble with reinserting the same record. By itself no table or cursor is sensitive to that, that has to be limited by indexes, insert trigger code or rules, anything that's a "watchdog" in one or the other sense. But then your sales items list is not your product list with a unique barcode and idnum for every record. Something that's just temporary in a form and only becomes data you want to persist in your database. Up to the point the clerk actually sells the items that's not necessary is it? So you might have introduced something like an index that rejects a double idnum. Why? It's not a dbf of your database and outside the realm of its normalized data schema.

Chriss

Mandy_crw (Programmer) · Answer 31 · 2023-09-02 22:50:44

Hi Chriss… that was so comprehensive…! Im really learning with all of you… i appreciate so much… ill try to study my code and apply some refinement… thank you so much Chriss… God bless…

Girish Solanki (Programmer) · Answer 32 · 2023-11-16 16:35:45

Textbox grag and drop with populate grid
https://youtu.be/WQG7o4G677g?si=upXkK5K1dy-o1_5K

Contact US

Come Join Us!

Posting Guidelines

How to color row grid in alternate shade? 5

How to color row grid in alternate shade?

How to color row grid in alternate shade?

RE: How to color row grid in alternate shade?

CODE -->

RE: How to color row grid in alternate shade?

RE: How to color row grid in alternate shade?

CODE -->

CODE

RE: How to color row grid in alternate shade?

Quote (Me)

CODE

RE: How to color row grid in alternate shade?

Quote (Mandy)

RE: How to color row grid in alternate shade?

CODE

CODE

RE: How to color row grid in alternate shade?

RE: How to color row grid in alternate shade?

CODE

CODE

RE: How to color row grid in alternate shade?

RE: How to color row grid in alternate shade?

RE: How to color row grid in alternate shade?

RE: How to color row grid in alternate shade?

CODE

RE: How to color row grid in alternate shade?

RE: How to color row grid in alternate shade?

RE: How to color row grid in alternate shade?

Spoiler:

RE: How to color row grid in alternate shade?

RE: How to color row grid in alternate shade?

RE: How to color row grid in alternate shade?

RE: How to color row grid in alternate shade?

CODE

RE: How to color row grid in alternate shade?

RE: How to color row grid in alternate shade?

RE: How to color row grid in alternate shade?

CODE -->

Quote:

RE: How to color row grid in alternate shade?

RE: How to color row grid in alternate shade?

CODE

RE: How to color row grid in alternate shade?

Quote:

Quote:

RE: How to color row grid in alternate shade?

RE: How to color row grid in alternate shade?

Quote:

RE: How to color row grid in alternate shade?

Quote (Mark)

RE: How to color row grid in alternate shade?

Quote:

RE: How to color row grid in alternate shade?

Quote (Mandy_crw)

CODE

RE: How to color row grid in alternate shade?

RE: How to color row grid in alternate shade?

Red Flag This Post

Red Flag Submitted

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Join Tek-Tips® Today!

How to color row grid in alternate shade?
5

Join Tek-Tips^® Today!