Creating a grid from a cursor

[AndrewMozley]

I would like to have a form myform which displays a grid mygrid of customer accounts (from table XCUST) from which I can let the user select one. The customer table exists on entry to the form, but the range of customers does not, because I let the user specify criteria which limit the range of customers (name beginning with . . . . &c)

So the form has a grid defined with 3 columns, with a RecordSource csrThese; this last is the name of the cursor which will exist, once the form has done its stuff. The control sources of the columns (or possibly of their Text1 fields) are left as default, because the form will not load if the grid has an invalid control source of one of its elements.

I have this rather crude code in myform.Init().

PARAMETERS vString
WITH Thisform
SELECT ID, Account, common_name, Postcode FROM XCUST ;
WHERE .T. INTO CURSOR csrThese
* Eventually we pay attention to the search
* WHERE vString $ SearchKey INTO CURSOR csrThese
IF RECCOUNT("csrThese") = 0
.zReturn = 0
.cmdClose.Click()
ENDIF

* We couldn’t set the control properties of the grid within the definition
* of the form, because cursor doesn’t exist when form is first displayed.
WITH .grdThese
.Column1.ControlSource = "csrThese.Account"
* .Column1.Text1.ControlSource = "csrThese.Account"
ENDWITH
.grdThese.Refresh()
ENDWITH
RETURN DODEFAULT()

However I find that (although the grid is displayed) no records are shown (at this stage I would like just the account number in Column1). I have put a breakpoint into the code, and cursor csrThese has several records.

What do I need to do, please?

I was also confused that both the Column and its Text1 property have a control source. Why is this? I had thought that the property that I want is a link to the field in Cursor csrThese. If however I set the Text1.controlsource to the field in my csrThese table, the command is rejected at run-time.

Thanks. Andrew

 

[EzLogic]

thisform.grdThese.Column1.ControlSource = "YourCursor.FieldName"


Ez Logic
Michigan

 

[Olaf Doschke]

In what event do you create crsThese? In Form Init? Then grid.init already has been done and the grid already has reconstructed from what you set up at design time.

What you do is always create an empty curso for the grid in load and then append data in init by INSERT INTO ... SELECT FROM or by APPEND.

In the same sense you act with parameterized views, you set them to nodataonload and then bind them and in form init requery them. Using cursor you need to do the same thing to let the grid have it's recordsource and not reconstruct. And then you can deign the grid in the form designer or grid builder the way you like, just set all the controlsources to the alias name and field names, that already exist in form init by the empty cursor created in load.

In detail you can reuse your code to create the full unfiltered cursor, just replace the .T. with .F. and you get an empty cursor. The only purpose of that cursor is to let the grid have it's data and not reconstruct:

*LOAD event code
SELECT ID, Account, common_name, Postcode FROM XCUST ;
WHERE .F. INTO CURSOR csrThese READWRITE

In Init you do

*INIT event code
SELECT ID, Account, common_name, Postcode FROM XCUST ;
WHERE Empty(vString) OR vString $ SearchKey INTO CURSOR csrTheseTmp 

Select csrThese
Append FROM DBF("csrTheseTmp")
Use In Select csrTheseTmp

It's important you don't recreate the cursor bound to the grid, because that also leads to the grid problem of reconstruction. You instead fill the already existing readwrite cursor from the query result. All this works from VFP7 on, in VFP6 you need to USE AGAIN the first cursor to make it writable, as the READWRITE clause was not yet invented.

A bit more elegent and only working in VFP9 is inserting the query result into the already existing empty cursor without needing a Tmp curor, then you can use that INIT query, LOAD event doesn't change:

INIT event code
INSERT INTO csrThese;
SELECT ID, Account, common_name, Postcode FROM XCUST ;
WHERE Empty(vString) OR vString $ SearchKey

This is what I recommended in a recent thead I won't point to, as that would just mean lots of posts to read without getting to the point.

The main point is you need to know the grid needs data right from the start, and that means before form init. Unfortunately the form INIT comes after grid and all other controls and unfortunately the LOAD doesn't get your parameter.

Besides, as a matter of taste:The prefix cur is recommended for cursor objects in VFPs Naming convention help chapters. That's for the cursor objects inside the DE, but I also use it for cursor aliases. I like you going that route anyway, it doesn't matter much if you use csr o cur. Another recommendation is to use t prefix for parameTers, I rather memorize t for taken. Data coming from outside, taken as input. But even the native parameterization of VFP isn't consequently making use of naming conventions.

Bye, Olaf.

 

[mjcmkrsr]

Hi Andrew,

You'll loose all the settings of your grid when doing it this way. I hide the columns as needed. Please have a look at the code below.

Maybe you also want to consider a parameterized view combined with hiding the unwanted columns.

hth

MarK

**************************************************
PUBLIC oForm

oForm = NEWOBJECT("form1")
oForm.Show
Read Events
Close all
Clear All
RETURN


**************************************************

DEFINE CLASS form1 AS Form
	AutoCenter = .T.
	Caption = "Sales"
	ShowTips = .T.
	Height = 480
	Width = 648
	MinHeight = This.Height
	MinWidth = This.Width
	

ADD OBJECT grdSales AS Grid WITH ;
	Left = 12, ;
	Top = 48, ;
	Width = 624, ;
	Height = 420, ;
	Anchor = 1 + 2 + 4 + 8, ;
	Visible = .T., ;
	ColumnCount = -1, ;
	RecordSource = "curSales"
	
ADD OBJECT spnColumns as Spinner WITH ;
	Left = 12, ;
	Top = 12, ;
	SpinnerLowValue = 1, ;
	ToolTipText = "Increment or decrement to show number of columns"
	
	PROCEDURE spnColumns.InteractiveChange()
		LOCAL li_Count, li_Columns
		
		li_Columns = ThisForm.grdSales.ColumnCount
		
		This.SpinnerHighValue = li_Columns
		
		FOR li_Count = 1 TO li_Columns
			ThisForm.grdSales.Columns(li_Count).Visible = .F.
		ENDFOR 	
		
		
		FOR li_Count = 1 TO This.Value
			ThisForm.grdSales.Columns(li_Count).Visible = .T.
		ENDFOR 	

		ThisForm.Refresh()

	ENDPROC
	

PROCEDURE Destroy
	CLOSE ALL
	Clear Events

ENDPROC

PROCEDURE Load

	CREATE CURSOR curSales (C_CID C(3), C_Descri C(20), N_Price N(6,2), L_Paid L)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI1", "Description", 4.25, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI1", "Description", 114.24, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI1", "Description", 24.22, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI1", "Description", 324.23, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI2", "Description", 54.29, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI2", "Description", 654.27, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI2", "Description", 47.28, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI2", "Description", 468.26, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI3", "Description", 447.24, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI3", "Description", 426.23, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI3", "Description", 644.24, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI4", "Description", 954.25, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI4", "Description", 384.27, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI4", "Description", 274.29, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI8", "Description", 164.25, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI9", "Description", 104.25, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI3", "Description", 384.27, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI4", "Description", 274.29, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI7", "Description", 164.25, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI6", "Description", 104.25, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI7", "Description", 384.27, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI9", "Description", 274.29, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI6", "Description", 164.25, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI4", "Description", 104.25, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI8", "Description", 384.27, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI7", "Description", 274.29, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI9", "Description", 164.25, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI9", "Description", 104.25, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI6", "Description", 384.27, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI7", "Description", 274.29, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI8", "Description", 164.25, .F.)
	INSERT INTO curSales (C_CID, C_Descri, N_Price, L_Paid) VALUES ("CI9", "Description", 104.25, .F.)

	LOCATE 
	
ENDPROC

PROCEDURE Init()
	LOCAL li_Count, li_Columns
		
	li_Columns = ThisForm.grdSales.ColumnCount

	FOR li_Count = li_Columns TO 2 STEP -1
		WAIT WINDOW "Column " + TRANSFORM(li_Count) + " will be invisible" TIMEOUT 1
		ThisForm.grdSales.Columns(li_Count).Visible = .F.
	ENDFOR 	
	
	
ENDPROC

	


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

 

[Olaf Doschke]

Errata:

1st INIT should end with Use In Select("csrTheseTmp")
2nd INIT should also start with the REMARK * INIT event code

Bye, Olaf.

 

[AndrewMozley]

Thank you all for your replies

EzLogic. This was indeed the instruction that I was using :

Thisform.grdThese.ControlSource = “csrThese.Account”

(It may have been obscured by the nested WITH . . ENDWITH in the code).

This was the instruction that I thought would do the trick, but it did not seem to have the desired effect


Olaf. Thanks. Your suggestion - of creating the cursor in the Load() method, then putting the records I really want in the Init() method – works.

It seems rather cumbersome, but the only way to achieve the result. As I see it, the problem arises, because the cursor ( with its rowsource and Column1.controlSource) has to exist at the time the grid is created in the Load() method, whereas I can only determine the content of the cursor, by picking up my search-string as a parameter in the Init() method.

Since then I have discovered another way of doing this : If I also leave the rowsource blank at design time, and then set that property once the cursor has been created – in the Init() method, thatt does it. See code below.

Init() Method
PARAMETERS vString
WITH Thisform
   SELECT ID, Account, common_name, Postcode FROM XCUST ;
      WHERE .T. INTO CURSOR curThese
*  Eventually we pay attentionto the search
*      WHERE vString $ SearchKey INTO CURSOR curThese
   IF RECCOUNT("curThese") = 0
      .zReturn = 0
      .cmdClose.Click()
      ENDIF
   
*  We couldn't set the control properties of the grid within the definition
*  of the form, because cursor doesn't exist when form is first displayed.
   WITH .grdThese
      .RecordSource = "curThese"
      .Column1.ControlSource = "curThese.Account"
      .Column2.ControlSource = "curThese.Common_name"
      .Column3.ControlSource = "curThese.postCode"
      ENDWITH
   .grdThese.Refresh()
   ENDWITH
RETURN DODEFAULT()

What had fooled me was that my definition of the Rowsource property (in the design of the grid) was being ignored at run time, because the cursor did not exist; I had not been getting an error message at Run-time - that setting was just being ignored! The only error message I had been getting was to the setting of the Column1.controlsource.

I take your point about the ‘cur’ prefix for cursors. My excuse is that I had been copying the style of my good friend Mike – but I think I will copy your recommendation.

 

[Olaf Doschke]

Yes, the form.init is your "last chance" to set the grid.recordsource, but this still has disdvantages, eg I believe the grid loses any code you put into it's controls.

There is one easier way not needing the form.load (which by the way happens even before any controls exist):
The most easiest way is set the form.bindcontrols = .f. at design time and then at runtime in the form init, right after you created crsThese and filled it, you set thisform.bindcontrols = .t.

Nevertheless, that only works once and the two cursor method works any number of times and also while the form already runs, eg you can add filtering by requerying data and don't need to start your form all over. all your queries go into a separate alias and then you ZAP and refill the grid cursor.

It's not half as cumbersome as you say, no, you can put the query to generate the initial grid cursor and refill or add to it later into a separate parameterised method. It works the same way as views with requery don't reconstruct the grid.

Cur or crs: actually it makes sense to NOT use object/class prefixes for aliases, so it could be vice versa and crs is the better choice for such an alias prefix. But it's a matter of taste to use such a prefix for aliases at all.

Bye, Olaf.

 

[Olaf Doschke]

* We couldn't set the control properties of the grid within the definition
* of the form, because cursor doesn't exist when form is first displayed.

That's also not true. You can set the column conrolsources to any expression you want, you just need to make sure it exists before the controlsource is first used. So you can layout and setup your grid at design time, no problem. If using the builders it's easier, if the alias you use exist at design time, for that matter you could for example do the query from command window and then use the grid builder using the alias name.

Bye, Olaf.

 

[Olaf Doschke]

One more thing:

The grid really is "greedy" about getting hands on data it can display. Even if you don't set it's recordsource it'll show the current workarea.

This short dom shows that:

Use Browser && a dbf in Home(), you may open any other table or create a cursor, something with data, so it shows.
oForm = Createobject("form1")
oForm.Show(1)

Define Class form1 As Form
   Add Object grid1 As Grid

   Procedure Load()
      Messagebox(Thisform.Objects.Count)
   Endproc

   Procedure Init()
      Messagebox(Thisform.grid1.RecordSource)
   Endproc
Enddefine

The first message box comes from LOAD and shows there is no object on the form yet. That's why you can use the LOAD event (and also the dataenvironment code) to prepare everything before it's too late, but don't have your INIT parameter yet. The second message box shows the recordsource is not empty (unless the current workarea is empty). I didn't succeeded in causing the grid to lose code, but I have inited grids in form init, too, in some of my apps, with mixed success. 14 Years Foxpro experience tell me not to do this. It's totally okay in conjunction with bindcontrols = .F., but that is only a solution to initialisation and it's a form scope setting, it keeps ALL controls unbound until you set it to .T. and that can already break class init code of other data bound controls you may use or add later.

Bye, Olaf.

 

[Mike Lewis]

Andrew, this doesn't directly answer your question, but here's an article will give you some background information that you might find useful: Controlling Visual FoxPro grid data dynamically.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Very well explained, Mike. I should keep track of your Foxstuff repository to reference it, this surely is kind of a FAQ about the grids nature that could be pointed to quite often.

Bye, Olaf.

 

[Mike Lewis]

Thanks for those kind words, Olaf.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads