Understanding the Visual FoxPro grid control

The grid is the most powerful of FoxPro's native controls, but it can also be the most confusing to work with. This tutorial will explain the issues and show you how to get started.

By Dan Macleod

Whatever kind of applications you develop, the chances are that you'll need some form of grid control. VFP's native grid (Figure 1) is a great way of displaying large amounts of data quickly. But it can be infuriatingly difficult to work with at times, to the point where some developers prefer not to use it at all.

But it's worth persevering, because the grid really is a very powerful tool. In this tutorial, I'll give you an introduction to the control, and show you how to get the best from it.

Figure 1

Figure 1: A Visual FoxPro grid.

Let's start with some key points about the grid control:

Quick start

The easiest way to add a grid to a form is by drag and drop. Try this:

  1. Open a form in the Form Designer.
  2. Drag a table and drop it onto the form. You can drag it from the form's Data Environment, a VFP Project Manager window, or even Windows Explorer.
  3. Save and run the form.

You now have a fully-functioning grid, showing all the data in the table.

Figure 2

Figure 2: Multi-selecting fields
in the Data Environment.

If you want the grid to show only certain fields from the table, add the table to the form's Data Environment, then multi-select the fields you're interested in, as shown in Figure 2 (hold down Ctrl and/or Shift while you select the individual fields). Drag these to the form.

Either way, the grid will have most of its properties correctly set, although you'll probably need to do a bit of customization to get it exactly the way you want. More on that later.

How to populate the grid

When you drop a table onto a form in this way, VFP will set the grid's RecordSoure property to the name (or, more precisely, the alias) of the table in question. This is important, because it's the RecordSource that defines the data used to populate the grid. Of course, you can also set the RecordSource yourself, either in the Properties window in the Form Designer or programmatically in your code.

The RecordSource works in conjunction with another property: the RecordSourceType. This can have the following settings:

Most of the time, you won't need to worry about these settings. You'll simply leave the RecordSourceType at its default value of 1.

Mapping columns to fields

We've talked about how the grid as a whole is populated. But how do you specify which fields show up in the various columns? That's done by setting the ControlSource property of each of the columns.

You need to be careful here. We've seen that a grid contains a collection of column objects, and that each column object contains (by default) a textbox. Both these objects (the column and the textbox) have a ControlSource property. But it's always the column's ControlSource that establishes the link to the field in the underlying table. The textbox's ControlSource is ignored.

The same applies to the formatting and styling of the data. If you want to change the font size, font name, foreground color, etc. of the text, you must adjust the relevant properties of the column, not of the textbox.

What about row objects?

Given that the grid contains column objects, you would expect it also to contain row objects. It doesn't. There's no such thing as a row object in a Visual FoxPro grid. The only way to address individual rows is by reference to the records in the underlying table.

This is an important point. It means that you can't look at the value of, say, the ProductID column in the fifth row of the grid. Instead, you must look at the value of the ProductID field in the fifth record of the table.

There is a particularly close connection between the rows in the grid and the records in the table. When you select a row in the grid, VFP automatically moves the record pointer to the corresponding record in the table. Conversely, moving the record pointer in the table causes the corresponding row to be selected in the grid. (You will need to set focus to the grid, either interactively or programmatically, to see a change in the selected row.)

Similarly, if you want the rows to appear in a different order, you do so by changing the order of the table (usually by changing the controlling index). And to remove a row from the grid, you delete the relevant record - or filter it out - from the table. There's usually no need to refresh the grid after making these kind of changes, but, again, you will need to set focus to it in order to make the changes visible.

Adding different controls to the grid

We saw earlier that the column object contains a textbox by default. You can also place other controls in the column, and you can remove the default textbox if you wish. This can be useful if you want to use, say, a checkbox to represent a logical field, or an image control to add a touch of graphics to the grid (Figure 3).

Figure 3

Figure 3: The grid can contain other VFP controls;
this one includes an image and a checkbox.

As an example, the Discontinued column in Figure 1 shows logical values as T and F. A checkbox would be a friendlier way of displaying these values, and would also be easier to edit. So let's add a checkbox to that column:

  1. In the Form Designer, right-click on the grid, and choose Edit.
  2. Click in the column (the column itself, not the header) that is to contain the checkbox. You should now see the name of that column in the drop-down list at the top of the Properties window.
  3. Select a checkbox in the toolbar, Project Manager window, Toolbox, or whatever. Drop it in the column.

Nothing seems to have happened. In fact, the checkbox has been successfully added, but it is obscured by the existing textbox, which is still present. (You can confirm this by looking at the drop-down list at the top of the Properties window.)

So, the next step is to remove the textbox:

  1. Select the textbox in the drop-down list at the top of the Properties window. Be sure that it's the textbox in the column which also has the checkbox.
  2. Click on the title bar of the Form Designer (not on the form itself; if you do, you will de-select the textbox).
  3. Press the Del key.

You should now see the checkbox in the column.

You can also add and remove controls programmatically. Use the column's AddObject method to add a control, and its RemoveObject method to remove it. After adding the control programmatically, you will also need to make it the current control. To do that, store its name in the column's CurrentControl property.

The Sparse property

After you've added the checkbox and run the form, you might at first think that something has gone wrong. The checkbox seems to have disappeared. In order to see it, you have to click in one of the cells. That's because, as a general rule, the object contained in a column is only active in the cell that currently has focus.

You can see this behavior in other ways. If you set a value for the Format or InputMask properties for a control within a column, the setting only takes effect in the cell that has focus. The same is true of various other settings and properties.

Often, this is what you want. But if it isn't, you can change the behavior by changing the column's Sparse property to .F. When you do this, all the cells in the relevant column will display the contained control, and will respect all its settings.

Read-only grids

By default, the grid is fully editable. The user can click in a cell and edit its value. When this happens, VFP will immediately update the corresponding field in the underlying table (or, where appropriate, in its buffer).

However, this is not always a sensible thing to do. The fact is that a grid makes a poor data-entry or editing tool. It's clumsy to use for this purpose, especially when editing long strings of text. One of the reasons that some FoxPro developers lose patience with the grid is that they mistakenly think it should be as easy to edit as a spreadsheet, which it isn't.

A much better approach is to use the grid as a read-only navigation device. Its aim should be to help the user locate the record that needs to be edited. You would then use other controls (textboxes, spinners, etc.) directly on the form for the actual editing, and refresh these with data from the current record as the user scrolls through the grid.

To make the grid read-only, set its ReadOnly property to .T. However, the effect of this isn't very satisfactory, mainly because the insertion point will still appear in a cell when the user clicks in it. This could lead the user to think that the cell is editable, when in fact it isn't.

In VFP 8.0 and above, you can avoid that problem by also setting the grid's AlllowCellSelection property to .F. and its HighlightStyle property to 2. These settings make the grid appear more like a true navigation control, with the current row being highlighted and no insertion point visible within a cell. You can also use the HighlightBackColor and HighlightForeColor properties to customize the colors of the selected row.

If you want to make some of the columns read-only and others editable, set the grid's ReadOnly property to .F. and set the ReadOnly property of the relevant columns to T.

Conditional formatting

When working with grids, a common requirement is to apply formatting to some of the rows but not others. For example, you might want to show overdue invoices in red, or out-of-stock items in bold. Or you might want to apply a different background color to alternating rows.

You can achieve this type of conditional formatting by means of the group of properties whose names begin with the word Dynamic. They include DynamicFontBold, DynamicForeColor, DynamicInputMask, and several others. These are described in a separate article (see Conditional formatting in a Visual FoxPro grid).

Auto-fit

Grids in Visual FoxPro 8.0 and above support auto-fit. This means that the user can easily resize the columns so that they are wide enough to show all the data within them. This feature is controlled by the AllowAutoColumnFit property.

When AllowAutoColumnFit is set to 0 (the default), you can auto-fit individual columns. You do this by double-clicking between the column headers. You can also double-click in the square in the top-left corner of the grid; this resizes all the visible columns at the same time. Setting the property to 1 allows auto-fit for individual columns only; setting it to 2 disables the feature.

In all cases, auto-fit only applies to the data that is actually visible. If a column contains a particularly wide value in a certain row, but that row is not currently visible, then auto-fit won't necessarily make the column wide enough to show it. You would have to scroll the grid to bring the row into view, then apply auto-fit again.

To auto-fit a grid programmatically, call its AutoFit method.

More properties

Here are some more of the properties that you can use to customize the grid:

Figure 4

Figure 4: The deletion column.

Figure 5

Figure 5: The not-so-familar
"change" view.

The above are all properties of the grid. These next ones are properties of the column:

Summing up

In this article, I've presented a quick introduction to what is a fairly complex item in Visual FoxPro's repertoire of controls. Fortunately, you don't need to know all of the grid's many nuances in order to get started with it. So if you're looking for an effective way to display tabular data in your VFP application, give the grid a try.

December 2011

Please note: The information given on this site has been carefully checked and is believed to be correct, but no legal liability can be accepted for its use. Do not use code, components or techniques unless you are satisfied that they will work correctly with your sites or applications.

If you found this article useful, please tell your friends: