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: A Visual FoxPro grid.
Let's start with some key points about the grid control:
- It is extremely efficient. It will have no difficulty showing a table with a quarter of a million or more records. And even with such a large volume of data, it will still let you jump from row to row almost instantly.
- It can only be populated from the data in an open table (or cursor, or view). Unlike with most other grid-style controls, you can't populate it with arbitrary values.
- A grid object is a container. It contains a collection of column objects. In turn, each column object contains a header object and (by default) a text box. But you can add other controls to the columns, for example to show checkboxes or images.
The easiest way to add a grid to a form is by drag and drop. Try this:
- Open a form in the Form Designer.
- 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.
- Save and run the form.
You now have a fully-functioning grid, showing all the data in the table.
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:
- 0 - Table. This says that the grid will be populated with the contents of the table whose name and path is specified in the RecordSource. VFP will open the table if necessary.
- 1 - Alias. This is the default setting, and the most common. It causes the grid to be populated with data from the open table (or cursor or view) whose alias is specified in the RecordSource. If the RecordSource is empty, the grid will use the alias in the current work area as its data source.
- 2 - Prompt. This tells VFP to prompt the user to select a table (the RecordSource is ignored). This is the least useful of the settings, as you would rarely want to rely on the user to select the correct data source.
- 3 - Query. Here, the RecordSource points to a query (QPR) file. This is essentially a program file that contains a SQL SELECT statement. When you run the grid, VFP will execute the SELECT statement, and use the resulting cursor to populate the grid.
- 4 - SQL SELECT statement. This is similar to 3, except that the SELECT statement itself is stored in the RecordSource.
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: 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:
- In the Form Designer, right-click on the grid, and choose Edit.
- 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.
- 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:
- 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.
- Click on the title bar of the Form Designer (not on the form itself; if you do, you will de-select the textbox).
- 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.
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.
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).
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.
Here are some more of the properties that you can use to customize the grid:
- AllowAddNew. If .T., the user can add a new row to the grid (and therefore a new record to the underlying table) by pressing the down arrow key from the last row. This only applies if the grid is editable. The default is .F.
- AllowHeaderSizing. Says whether the user can change the height of the grid's header row.
- AllowRowSizing. Says whether the user can change the height of the rows within the grid. (You can't change the height of a specific row; all rows are always the same height.)
Figure 4: The deletion column.
- ColumnCount. The number of columns in the grid. When set to -1 (the default), the grid will have as many columns as there are fields in the underlying table.
- DeleteMark. Says whether the deletion column is visible. This is a narrow column to the left of the first data column; the user can click in it to toggle the record's deletion flag (Figure 4). In most cases, showing this column is undesirable because users won't be aware of it, and so risk accidentally deleting records. To avoid that, change the property from .T. (the default) to .F. When .T., the user will be able to click in the column even if the grid is read-only, but not if AllowCellSelection is non-zero.
- SplitBar. Says whether the split bar is enabled. The split bar is the small black rectangle in the bottom-left corner of the grid. If you drag this to the right, the grid will be split into two partitions. This can be useful at times, but also confusing to users who don't know about it and who might therefore create a partition by accident. For that reason, you would usually set the property to .F. unless you had a particular reason to partition the grid.
Figure 5: The not-so-familar
- View. This allows you to change between two modes: grid view (the conventional view, with the familiar rows and columns) and change view (in which each field occupies a full row; see Figure 5). If the grid is partitioned, you can apply a different mode to each partition. In practice, this is very seldom done, probably because few FoxPro developers know about it.
The above are all properties of the grid. These next ones are properties of the column:
- Movable. Says whether the user can drag the column to a new position within the grid.
- Resizable. Says whether the user can resize a column, either by dragging the edge of the column header or by means of auto-fit (see above).
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.
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.