Controlling Visual FoxPro grid data dynamically

How to give your users more control over the contents of their VFP grids

NOTE: For a general introduction to grids, see Understanding the Visual FoxPro grid control.

Let's suppose you want to create a FoxPro form like the one in Figure 1. As you can see, this uses a grid to dispay data from a Products table. Users can control the contents of the grid in the following ways:

They can limit the grid to showing products in a selected category.
They can choose which of two fields - the English name or the original name - is to appear in the Description column.
And they can stipulate a sort order for the grid.

Figure 1: Three ways for the user to control the grid

After making these selections, the user presses the Refresh button. The data in the grid then changes to reflect the user's choices.

Sounds straightforward? So how would you go about creating this form?

First thoughts

Your first thought might be to access the data via a local view. That sounds reasonable, given that VFP lets you modify the contents of a view by parameterising it. You would do that in the view's WHERE clause, like so:

WHERE Products.Category = ?lcCat

Here, lcCat is a variable which holds the user's choice of category. If you then specify the view as the grid's RecordSource, the data will be filtered by the required category each time the view is requeried. The call to the REQUERY() function would go in the Click event of the form's Refresh button.

That's fine as far as filtering is concerned. But you can't parameterise the choice of fields to diplay in a given column or the view's sort order. It is possible to re-create the entire view programmatically each time the user presses the Refresh button, but that's not a particularly elegant solution. Is there a simpler way?

Try SQL SELECT

Using a SQL SELECT statement to create a cursor sounds more promising. With not too much difficulty, you can write a SELECT which respects the user's three choices, and which generates a cursor which can be used as the grid's RecordSource.

Let's assume that we have already set up the following variables:

lcCat contains the required category.
llEnglish is .T. if the user has chosen English as the language for the product descriptions (in which case we will use the eng_name field as the second column). It's .F. if the user wants to see the original language (for which we will use the prod_name field instead).
lcOrder contains the number of the column that the data is to be sorted on (held as a character string).

The code in the Refresh button might then look something like this:

SELECT product_id,; 
 IIF(llEnglish,eng_name,prod_name) AS descript,;
 unit_price, in_stock ;
 FROM Products ;
 WHERE ALLTRIM(Category) = ALLTRIM(lcCat) ; 
 ORDER BY &lcOrder INTO CURSOR csrTemp
   
THISFORM.refresh

The SELECT statement sends the required data to a cursor, csrTemp. This is the RecordSource for the grid, so after the form has been refreshed, the grid should show exactly the data that the user requires. Problem solved.

Not that simple

Unfortunatetly, it's not quite that simple. If you were to create this form and run it, the SELECT would produce the correct data, but the grid would appear as an empty rectangle. No data at all would be visible.

The reason for this behaviour is not hard to see. Whenever you use SELECT to create a cursor in this way, Visual Foxpro first destroys the existing cursor (if any), then builds an entirely new one. This is very upsetting for the grid, which doesn't like to have its RecordSource taken away from it - even though it's only gone for a moment. Because the controls within the grid are bound to the cursor, destroying the cursor will rob the grid of the controls, which is why you see the empty rectangle.

Would it make any difference if we used a physical table rather than a cursor for the output of the SELECT? No, it would make no difference at all.

The solution

However, once you understand what's going on, it's not hard to devise a solution. The trick is to create a second cursor to use as the RecordSource, and to move the data from the first cursor (the one created by the SELECT) to the new one whenever the user wants to update the grid.

We will put the following code in the form's Load event:

CREATE CURSOR csrProducts ; 
  ( product_id C(6), descript C(40), ;
  unit_price N(6,2), in_stock N(6) )

This will create a cursor, called csrProducts, with the same structure as the one generated by the SELECT. Make this cursor the RecordSource for the grid.

In the Refresh button, keep the SELECT that you created earlier, but add some further code to copy the contents of the cursor which that SELECT generates (csrTemp) into the new cursor (csrProducts). That code will now look like this:

SELECT product_id, ;
  IIF(llEnglish,eng_name,prod_name) AS descript,;
  unit_price, in_stock ;
  FROM Products ;
  WHERE ALLTRIM(Category) = ALLTRIM(lcCat) ; 
  ORDER BY &lcOrder INTO CURSOR csrTemp
   
  SELECT csrProducts 
  ZAP 
  APPEND FROM DBF("csrTemp")
  THISFORM.refresh

The effect of this is to copy the results of the SELECT into csrProducts. After the form is refreshed, the data will be correctly displayed in the grid.

Note that you cannot use the COPY TO command to transfer the data to csrProducts, as that command always creates a new file. Instead, you need to zap the existing contents of csrProducts, than append the new data. Note also the use of the DBF() function. This is needed because the APPEND FROM command can only copy data from a physical table. DBF() returns the path and name of the real file that holds the cursor.

One final detail needs attending to. You probably want the grid to display some initial data when the form first appears. This data would be based on the default values for the three user choices. To achieve that, simply add code to the form's Init event to do the SELECT and to append the results into csrProducts. Of course, you will also need some code to set up the three variables used by the select (lcCat, llEnglish and lcOrder), but we will leave that as an exercise for the reader.

Acknowledgements

The technique which we have described here is based in part on information from that very useful book, 1001 Things You Always Wanted to Know About Visual FoxPro, by Marcia Akins, Andy Kramek and Rick Schummer (Hentzenwerke, 2000). You can read a review of this book on our Books page, or find further information about it at Amazon.com.

Mike Lewis Consultants Ltd. September 2001