How to populate grid rows with array 2

[SitesMasstec]

Hello colleagues!

How can I populate rows in a grid with an array of values, like in the picture bellow:

Thank you,
SitesMasstec

 

[Olaf Doschke]

If you take the time to read through this thread, all you ask for is already given and talked about, Koen. SitesMasstec has shown his table design, I have proposed a better design. The initial task to show an array was not the real problem anyway, it was just SitesMasstec idea of loading data of a badly designed table into memory variable to make it available for binding. At least he saw he can go directly from the pivoted table to unpivoted cursor and show it. But even that step is unnecessary with a good and normalized table design.

SitesMasstec is finished with this, so it's futile to continue here.

Bye, Olaf.

 

[SitesMasstec]

Hello colleagues!

Olaf, this original table is just to keep all information in one table. It will never or rarely will be updated:

[pre]
Field Field Name Type Width Dec Index Collate Nulls Next Step
1 INU_DESC01 Character 20 No
2 INU_QTDE01 Character 22 No
3 INU_MEDI01 Character 5 No
4 INU_PERC01 Numeric 5 1 No
5 INU_DESC02 Character 20 No
6 INU_QTDE02 Numeric 12 3 No
7 INU_MEDI02 Character 5 No
8 INU_PERC02 Numeric 5 1 No
9 INU_DESC03 Character 20 No
10 INU_QTDE03 Numeric 12 3 No
11 INU_MEDI03 Character 5 No
12 INU_PERC03 Numeric 5 1 No
13 INU_DESC04 Character 20 No
14 INU_QTDE04 Numeric 12 3 No
15 INU_MEDI04 Character 5 No
16 INU_PERC04 Numeric 5 1 No
17 INU_DESC05 Character 20 No
18 INU_QTDE05 Numeric 12 3 No
19 INU_MEDI05 Character 5 No
20 INU_PERC05 Numeric 5 1 No
21 INU_DESC06 Character 20 No
22 INU_QTDE06 Numeric 12 3 No
23 INU_MEDI06 Character 5 No
24 INU_PERC06 Numeric 5 1 No
25 INU_DESC07 Character 20 No
26 INU_QTDE07 Numeric 12 3 No
27 INU_MEDI07 Character 5 No
28 INU_PERC07 Numeric 5 1 No
29 INU_DESC08 Character 20 No
30 INU_QTDE08 Numeric 12 3 No
31 INU_MEDI08 Character 5 No
32 INU_PERC08 Numeric 5 1 No
33 INU_DESC09 Character 20 No
34 INU_QTDE09 Numeric 12 3 No
35 INU_MEDI09 Character 5 No
36 INU_PERC09 Numeric 5 1 No
37 INU_DESC10 Character 20 No
38 INU_QTDE10 Numeric 12 3 No
39 INU_MEDI10 Character 5 No
40 INU_PERC10 Numeric 5 1 No
** Total ** 431
[/pre]

I understand the the above table is poor normalisation.

Then, each group of contents INU_DESCxx, INU_QTDExx, INU_MEDIxx, INU_PERCxx (xx = 01, 02 ... 10) are transformed in a Cursor with 10 records (to be possible to use them in a Grid, and also it will be normalised with VFP good rules). So each INU_DESCxx, INU_QTDExx, INU_MEDIxx, INU_PERCxx will be in just on record.

Then, I connect the Cursor INFONUT1A to the Grid (in Grid, Properties: RecordSource: INFONUT1A, RecordSourceType: 1-Alias).








Thank you,
SitesMasstec

 

[SitesMasstec]

Olaf:

Yes, if I use your proposed two tables (parent + child table, interconnected) instead of the just one above, I will not even need a cursor!

I can populate the Grid with the child table! You're right!



Thank you,
SitesMasstec

 

[Olaf Doschke]

Take a look at the crosstab wizard, you generate the pivoted data with fields 01-20 via this and not by hand.

And even if you only use this as template data, you do so in separate tables with 1+20 records instead of 1. Then you also can copy this template 1:1 into final tables. I did exactly that for templates about product testing. There was template data tables and job data created from the template data.

Bye, Olaf.

 

[SitesMasstec]

Hello colleagues!

Not working in the real application...

The code bellow is in the Pageframe1, Page4 (last folder), Click event:

SELECT 41
USE INU10000
GOTO 1

CREATE CURSOR INFONUT1A (INU_DESC C(20), INU_QTDE C(18)) 

FOR Y=1 TO 10
	SELECT 41
	strY=STR(Y,2)

	IF SUBSTR(strY,1,1)=" "
		strY="0"+SUBSTR(strY,2,1)
	ENDIF
	CAMPOY="INU_DESC"+strY
	DESCRICAO=&CAMPOY

	SELECT INFONUT1A 	
	APPEND BLANK 
	REPLACE INU_DESC WITH DESCRICAO    && first  element for the grid
	REPLACE INU_QTDE WITH "0.000"      && second element for the grid
NEXT Y

BROWSE     && Just to check if the values are in the Cursor

thisform.Pageframe1.Page4.Grid1.Refresh

Thank you,
SitesMasstec

 

[Olaf Doschke]

In a grid with 4 columns, you need a cursor with 4 columns.

 

[SitesMasstec]

Hello Olaf!

I changed the code (please see bellow) but the result is the same (no display in the Grid).

Through command Browse I can see that data populated the Cursor.

SELECT 41
USE INU10000
GOTO 1

CREATE CURSOR INFONUT1A (INU_DESC C(20), INU_QTDE N(12,3), INU_MEDI C(5), INU_PERC N(5,1)) 

FOR Y=1 TO 10
	SELECT 41
	strY=STR(Y,2)

	IF SUBSTR(strY,1,1)=" "
		strY="0"+SUBSTR(strY,2,1)
	ENDIF
	CAMPOY="INU_DESC"+strY
	DESCRICAO=&CAMPOY

	SELECT INFONUT1A 	
	APPEND BLANK 
	REPLACE INU_DESC WITH DESCRICAO    && first  element for the grid
	REPLACE INU_QTDE WITH 0.000        && second element for the grid
	REPLACE INU_MEDI WITH "g    "      && third  element for the grid
	REPLACE INU_PERC WITH 0.0          && fourth element for the grid
NEXT Y

thisform.Pageframe1.Page4.Grid1.Refresh

Thank you,
SitesMasstec

 

[mjcmkrsr]

Hi,

Try this code from the the CLICK event of your page 4

SELECT 41
USE INU10000
*!* GOTO 1
[highlight #73D216]LOCATE[/highlight]

CREATE CURSOR INFONUT1A (INU_DESC C(20), INU_QTDE N(12,3), INU_MEDI C(5), INU_PERC N(5,1)) 

FOR Y=1 TO 10
	SELECT 41
	strY=STR(Y,2)

	IF SUBSTR(strY,1,1)=" "
		strY="0"+SUBSTR(strY,2,1)
	ENDIF
	CAMPOY="INU_DESC"+strY
	DESCRICAO=&CAMPOY

	SELECT INFONUT1A 	
	APPEND BLANK 
	REPLACE INU_DESC WITH DESCRICAO    && first  element for the grid
	REPLACE INU_QTDE WITH 0.000        && second element for the grid
	REPLACE INU_MEDI WITH "g    "      && third  element for the grid
	REPLACE INU_PERC WITH 0.0          && fourth element for the grid
NEXT Y

[highlight #8AE234]WITH THIS.Grid1 && I assume that's the GRID's name
       .ColumnCount = -1
       .Recordsouce = "INFONUT1A"
       .SetAll("Sparse", .F., "Column")
       .Visible = .T.
       .Refresh() 
ENDWITH[/highlight]

hth
MK

 

[Olaf Doschke]

As a beginner of grid usage, let me tell you some rules of thumb:

1. You never recreate a cursor bound to the grid, that destroys detail info and binding, even just a CREATE CURSOR of a cursor already existing causes "grid goes blank".
2. Ideally the cursor you bind to a grid exists before the grid, so you create cursors in a forms data environment or Load() event. Last chance is the Init of a grid. If you don't have a cursor at hand to bind to, a grid will simply take the current workarea as it's recordsource, even if recordsourcetype still is 0.
3. When replacing grid cursor content with something completely new, you use a technique for which the name "Safe Select" was coined at Foxite, you have your grid cursor, you query into a separate cursor with other alias name and then empty the grid cursor with ZAP and append your new data from the other alias. That works fine, for the typical cases of up to a magnitude of 1000s of records, so for any typical practical case.
4. Views are another nice source to bind to grids, as a REQUERY("view") is capable to ZAP and repopulate a view alias the way a safe select does, without needing a second alias. The downside of this is view SQL limits you, especially thinking of your ways to unpivot another table record with lots of number suffixed fields, it's not doable with SQL, it's a hack anyway, which you should abandon overall.
5. Another simple way of showing 10 of 1000s of records is to use SET FILTER. It's also unfortunate about the scrollbar of the grid, and it works sluggish in very large tables, but for your cases it would be ideal, you would have your chunks of 10 or 20 records and switch between them with a filter instead of SKIP, you wouldn't even need to copy data over from table to grid cursor. Just to make it clear, in this case of binding to a table you never ZAP, unless your table data was just meant temporal, too.

Bye, Olaf.

 

[SitesMasstec]

Dear colleagues:

I have been using Grid for the last 2 years, but only for displaying data from tables.

Using Grid for data entry and update tables (as I needed), for my level of knowlegde, is more difficult.

I think Grid is like an helicopter: you have to learn more skills to pilot it, and when learned, you can make long distances in a short time. And similarly, Textbox is like a car: it is easier to drive, but if you have to travel long distances, it will take a lot of time.

Just to share with you all (and Olaf in particular, for his great patience) who helped me a lot during the last 8 days, the solution for my problem, using Textboxes:

Thank you,
SitesMasstec

 

[GriffMG]

Well done for resolving your issue.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[mjcmkrsr]

Hi,
Why give up that fast?
Please have a look at the code below and specifically the AfterRowColChange() procedure.
In short: Column2, entitled "ME" is the only column that accepts changes - the values of the other fields in the cursor are updated immediately.
Please consider the code as a sketch of how you could approach this problem.

PUBLIC go_Form
go_Form = CreateObject ("frmForm")

WITH go_Form.grdNames
	.Readonly = .T.
	.SetAll("Sparse", .F., "Column")
	.Column1.Header1.Caption = "Name" 

	.Column2.Header1.Caption = "Me"
	.Column2.ReadOnly = .F.
	
	.Column3.Header1.Caption = "Sq"
	.Column4.Header1.Caption = "Vo"
	.Column5.Header1.Caption = "Fo"
ENDWITH 

go_Form.Show

READ Events
CLOSE ALL
CLEAR ALL


DEFINE CLASS frmForm As Form
  Width = 420
  Height = 360
  AutoCenter = .T.

  * Add a grid to the form
  Add Object grdNames as Grid with;
    ColumnCount = -1, RecordSource = "curNames", Visible = .T., Top = 18, Left = 18, Width = 390, Height = 270, Partition = 108, View = 1
    
    PROCEDURE grdNames.AfterRowColChange()
	    LPARAMETERS nColIndex
	    
	    GOTO RECNO()
	    REPLACE nSquare WITH nMeters * 2, nVolume WITH nMeters * 3, nForth  WITH nMeters * 4

	    THISFORM.grdNames.Refresh()

    ENDPROC 
    			
  * Add cancelbutton to the form
  ADD OBJECT cmdCancel As CommandButton WITH;
    Width=60, Height=30, Left=18, Top=300, Caption="Exit"

	PROCEDURE cmdCancel.Click()
		CLOSE ALL 
		CLEAR Events
		ThisForm.Release
	
	ENDPROC
  
	PROCEDURE Destroy()
		ThisForm.cmdCancel.Click()
	
	ENDPROC
    
	PROCEDURE Load
		Create Cursor curNames (cName C(10), nMeters I, nSquare I, nVolume I, nForth I)
		
			For li_I = 1 to 50
				INSERT INTO curNames (cName, nMeters, nSquare, nVolume, nForth) ;
							VALUES ("Name" + PADL(li_I,3,"0"), li_I, li_I * 2, li_I * 3, li_I * 4)
			Next li_I   
		
		Locate
	
	ENDPROC
ENDDEFINE

Enjoy
MK

 

[Olaf Doschke]

Indeed, there's no reason you need to give up. Also, I could argue if these nutrition facts you store always are about the same 10 aspects from the Energy to the Iron amount and if the units also stay, why not make these labels? What remains then is a single column for the numbers to enter. But would you go into that less flexible direction, SiteasMasstec? The information you need to record may change at any time.

Make that data! Because it is. And that means not just store the descriptions and units as you have them in a table with 10x3 fields, have a table with productid, property name, value and unit fields and you can store as many varying properties of a product, as you wish. Then bind this properties table and SET FILTER TO productID = X and the grid shows all properties of just one product. It's editable, there's nothing special to do about that. You never have to read from N fields into a grid cursor and then store the edited values back.

Your other main table will be the list of products, with their product id matching here and at minimum product name, surely much more single value information about the product, like current price or GTIN code or anything else. You still refuse to spread information about an entity like a product into multiple tables and even 1 major + multiple detail record. But that's the norm. And the grid displays multiple records at once. There is no magic. Doing it this proposed way the grid cursor will simply be the productnutricioninfo table or however you call it, and a relation from the products table to it would act as the filtering. There is no cursor recreation, no copy forth and back, no grid going blank problem, no hard problem at all, if you do it by that normal data design.

Bye, Olaf.

 

[SitesMasstec]

Hello colleagues!

I have not given up! What I mean is that, for this time, I will use textboxes to solve the problem I am working on, but I try to learn step by step the use of Grid for data entry (of course I want to program in faster way).

I have lots of Hentzenwerke books about Visual FoxPro, and also the VFP 9 Help file is available, but it seems to me that every control (of any book I have VFP) are for people who already have some knowledge about the it.

Any of you know about a VFP 9 book "for dummies"?




Thank you,
SitesMasstec

 

[Koen Piller]

Hi,

1000 Things you want to know about Visual FoxPro by Andy Kramek c.s. Edited by Hentzenwerke, Hardcopy out of Sale however the even more handy electronic version is still available.

It is written for VFP7 but very well usable for VFP9 also, and if you like from the same Editor What's new in VFP9 to update

Koen

 

[mjcmkrsr]

Hi,

Don't go for "Dummies"! Go for good books if you really want to make VFP work for you.

An absolute must-have: "Hacker's Guide to Visual Foxpro 7.0" by Tamar E. Granor, Ted Roche, Doug Hennig and Della Martin. I don't know whether the hardcopy still exists, but the e-version should be around.

"Programming in Visual Foxpro 3.0" By Whil Hentzen. Although rather old, this book starts from 0 and focuses in each chapter on a different aspect of VFP (Tables and Fields, Databases, Queries, Views, Controls, Functions, Variables, Classes, Programming ...) and at the end you have an almost full demo application.

And many more ...

hth

MK

 

[mjcmkrsr]

and this

[link ]

https://www.google.com/search?q=visual+foxpro+books&ie=utf-8&oe=utf-8&client=firefox-b-ab

[/url]

hth

MK

 

[mjcmkrsr]

Hi,

Below a slightly pimped-up version of my former example-code. This one draws a sketch of how to work on the CURSOR and to UPDATE the underlying tables on the fly. The BROWSE button allows to display the two underlying tables tblNames and tblAddNames. Just move the frames.

PUBLIC go_Form
go_Form = CreateObject ("frmForm")

WITH go_Form.grdNames
	.Readonly = .T.
	.SetAll("Sparse", .F., "Column")

	.Column1.Header1.Caption = "Name"
	.Column1.Text1.FontBold = .T.
	.Column1.Text1.FontItalic = .T.
	
	.Column2.ReadOnly = .F.
	.Column2.BackColor = RGB(0, 180, 180)
	.Column2.Header1.Caption = "Me"
	.Column2.Text1.InputMask = "9,999.99"
	
	.Column3.Header1.Caption = "Sq"
	.Column3.Text1.InputMask = "9,999.99"
	
	.Column4.Header1.Caption = "Vo"
	.Column4.Text1.InputMask = "9,999.99"
	
	.Column5.Header1.Caption = "Fo"
	.Column5.Text1.InputMask = "9,999.99"
	
	.Column6.Header1.Caption = "Sqrt"
	.Column6.Text1.InputMask = "9,999.99"
ENDWITH 

go_Form.Show

READ Events
CLOSE ALL
CLEAR ALL


DEFINE CLASS frmForm As Form
  Width = 420
  Height = 360
  MinWidth = 420
  MinHeight = 360
  MaxWidth = 420
  MaxHeight = 360
  AutoCenter = .T.

*!*		Add a grid to the form

	Add Object grdNames as Grid with;
		ColumnCount = -1, RecordSource = "curNames", Visible = .T., ;
		Top = 18, Left = 18, Width = 390, Height = 270, Partition = 108, View = 1
    
	    PROCEDURE grdNames.AfterRowColChange()
		    LPARAMETERS nColIndex
		    
		    GOTO RECNO()
		    
		    REPLACE nSquare WITH nMeters * 2, ;
		    		nVolume WITH nMeters * 3, ;
		    		nForth WITH nMeters * 4, ;
		    		nSqrt WITH SQRT(nMeters)
		    		    
			UPDATE tblNames SET ;
					nMeters = curNames.nMeters, ;
					nSquare = curNames.nSquare, ;
					nVolume = curNames.nVolume ;
				WHERE cName = curNames.cName

			UPDATE tblAddNames SET ;
					nForth = curNames.nForth, ;
					nSqrt = curNames.nSqrt ;
				WHERE cName = curNames.cName
				
			
		    THISFORM.grdNames.Refresh()

	    ENDPROC 
    			
*!*	Add object Label

	ADD OBJECT lblInfo as Label WITH ;
		Top = 330, Left = 168, Autosize = .T., FontSize = 8, FontItalic = .T., ;
		Caption = "Only green colored items accept user input"


*!*		ADD a Browse button - allows you to see the underlying tables

	ADD OBJECT cmdBrowse As CommandButton WITH;
		Width=60, Height=30, Left=84, Top=300, Caption="Browse"

		PROCEDURE cmdBrowse.Click()
			LOCAL lcAlias
			
			lcAlias = ALIAS()  
			
			SELECT tblNames
			BROWSE TITLE "tblNames" NOEDIT NOWAIT
			LOCATE  
			
			SELECT tblAddNames
			BROWSE TITLE "tblAddNames" NOEDIT NOWAIT 
			LOCATE 
			
			SELECT (lcAlias)
			
		ENDPROC 
		
		
*!*		Add exitbutton to the form
  
	ADD OBJECT cmdExit As CommandButton WITH;
    	Width=60, Height=30, Left=18, Top=300, Caption="Exit"
    	
		PROCEDURE cmdExit.Click()
			CLOSE ALL 
			CLEAR Events
			ThisForm.Release
		
		ENDPROC
	  
		PROCEDURE Destroy()
			ThisForm.cmdExit.Click()
		
		ENDPROC
    
		PROCEDURE Load
		
			IF !FILE("tblNames.dbf")
			
				Create Table tblNames (cName C(10), nMeters I, nSquare I, nVolume I )
			
					For li_I = 1 to 50
						INSERT INTO tblNames (cName, nMeters, nSquare, nVolume) ;
								VALUES ("Name" + PADL(li_I,3,"0"), li_I, li_I * 2, li_I * 3)
					Next li_I   
			
			ENDIF 
			
			IF !FILE("tblAddNames.dbf")
			
				Create Table tblAddNames (cName C(10), nForth I, nSqrt N(6,2))
			
					For li_I = 1 to 50
						INSERT INTO tblAddNames (cName, nForth, nSqrt) ;
								VALUES ("Name" + PADL(li_I,3,"0"), li_I * 4, SQRT(li_I))
					Next li_I   
			
			ENDIF 

			SELECT tblNames.cName, nMeters, nSquare, nVolume, nForth, nSqrt ;
				FROM tblNames ;
				JOIN tblAddNames ON tblNames.cName = tblAddNames.cName ;
				INTO CURSOR curNames READWRITE 
			
			LOCATE
		
		ENDPROC
ENDDEFINE

hth
MK

 

[Olaf Doschke]

It's even simpler, with the table structure I propose you'd simply bind a grid or two grids 1:1 and edit the data as is. There neither is a translation from some DBF to grid cursor nor back.
You might just want to have 1 column readonly as "labels" of the values you enter in column2 and then again a readonly column3 for units.

Bye, Olaf.