Working with the Grid Control
in dBASE

Last Modified: January, 2004
Ken Mayer, Senior SQA Engineer
dBASE, Inc.


Example file 1 to 4 can be found here: testGrids.zip


NOTE: This document was originally written for Visual dBASE 7.0/7.01, it has been updated for dB2K (release 1) and now dBASE Plus to include information about new properties, events, etc., and any new controls since the document was first written. If a control is new it will be noted.

In addition, this document refers to dB2K and dBASE Plus a lot, but unless it is about a dB2K or dBASE Plus specific aspect, the text can be used for Visual dBASE 7.0 through Visual dBASE 7.5.


Note: This is a very short practical guide to some of the areas that people really wish to be able to do with the grid ... (The following assumes that you are somewhat familiar with forms, form controls, and using data classes in dBASE with all of these. If this is not the case, you may want to go to some of the other HOW TO documents first, such as "Beginning Forms", and such.)


The Grid's Object Model

The grid itself is an object, and it has objects associated with it. The following is a brief description of the object model.

The Grid Object Model
(Image created by and used with permission of Michael Nuwer)

The grid itself has many properties, events and methods associated with it, a lot of these are discussed later in this paper.

In the first part of this paper there are instructions which explain how to add grid columns using the designer. Each grid column object which is contained in the grid's column array, contains an editorControl and a headingControl.


Getting Started

Grids have rows (which correspond to the rows of a table) and columns (also corresponding to the columns or fields of a table), and cells (where the rows and columns intersect).

When designing a form that uses the Grid control in dBASE, if you wish programmatic control over the grid, here are some steps you must follow [this assumes you have your query/queries or datamodule(s) on the form]:

  1. place the grid on the form
  2. set the dataLink property to the rowset you wish to display
  3. (and most important) -- while the grid should be displaying all columns from your rowset, you do not have real control yet -- in the inspector click on the columns property. Note that it shows the word "object" in boldfaced text, and should display two buttons -- a tool (wrench) and the letter 'I' in a small graphic. Click on the tool button. Select the columns you wish to display, in the order you wish to display them, and then click the OK button.
At this point, you have a lot of control over your grid. That's the absolute basics. To get a better understanding of the grid, and get some idea what you can do with it, read on.

Note: In order to change the sequence of columns in a grid, the only way is to define the grid's columns as shown above. A simple datalink to the table will not give you enough control -- this is useful for a quick display, but for any control beyond that, you need to set the columns as shown.


Grid Columns Array
The Grid Columns Array is how you add columns to a grid. In addition to being able to add columns using the inspector, you can programmatically add columns to a grid. There is an example of this kind of thing in the sample form "testGrid.wfm", in the form's OnOpen event.

The columns array itself only has a small amount of options, as it is quite literally a subclass of an array. It is really simply a way to contain references to all of the grid's columns. You can use the grid's add method to add columns, and the grid's delete method to delete columns. That's really about it.

Grid Column
The grid's column object is where we start seeing the real control over individual columns of a grid. This object has several properties, but no events or methods. The properties that are most important are:

dataLink this is how you specify the link for a field to the grid. In other words, what data do you want to appear in this column?
editorControl this is a reference to the object that actually displays the data. In the designer you can drill down to this using the inspector and see various properties and such (some of them are described below).
editorType this is the type of control you wish to use to display the field. If the field is numeric or a date, you might wish to use a spinbox, for example. The options:
  • 1 - Entryfield (the default) (Works for all field types)
  • 2 - Spinbox (Numeric and Date field types)
  • 3 - Checkbox (Logical field types)
  • 4 - Combobox (Character field types)
  • 5 - Editor new to dBASE Plus 2.2 (Memo and Character field types)
Each of these controls has a limited subset of the properties, events and methods of the controls you can place directly onto a form's surface.
headingControl this is the heading for the column. This is discussed a bit below ...
width this is the width of the column when it is displayed.

editorControl
The editorControl object is where you can fine-tune the display of any specific column of a grid. You can override colors, you can set fonts for just that column, and so on. The following are some of the properties, and events (no methods are available) that you may find useful (not all are discussed here):

dataSource,
sorted
,
dropDownHeight
,
dropDownWidth
combobox only -- these work just like with a normal combobox. Note that in dBASE Plus 2.2, the dropDownHeight property is also available for the editor.
rangeMax,
rangeMin
,
rangeRequired
,
step
spinbox only -- these work just like with a normal spinbox.
function can be used with entryfield, spinbox and combobox editorControls to determine how something appears and is used for data entry.
picture can be used with entryfield, spinbox and combobox editorControls to determine how something appears and is used for data entry -- is often used with the function property.
validErrorMessage for those editorControls that have a valid event, this is used to display an error if the event returns a false value.
fontBold, etc. these properties can be used to set the font for a specific column of the grid. If you use the grid's font properties, setting them for a specific column will override the grid's fonts for that column.
borderStyle this changes the border of a cell when it has focus -- if you use this, you should probably check the column's width and the grid's cellHeight properties to make sure that you are not obscuring the data with the border ...
colorHighlight the color of a cell when it has focus.
colorNormal the color of a cell when it doesn't have focus -- this affects the whole column.
evalTags editor only -- for a columnEditor, this can be used to tell the editor control to evaluate HTML tags, or to not do so. This is very much like the property of the regular editor control of the same name.
key() this event is called every time a keystroke occurs, and you have code associated with it.
onGotFocus() when a cell gets focus is when this event is called.
onLostFocus() when a cell loses focus.
valid() this event is called when a cell loses focus and can be used to validate data entry. If the event returns true, nothing happens, but if it returns false a message is displayed (see validErrorMessage above), and focus returns to that cell.

NEW! In dBASE Plus vers. 2.2 and later if you have a column that is datalinked to a memo field the editorControl will display by default a Editor control that will display part of the memo field with a pushbutton that can be used to expand the editor window. In earlier releases of dBASE a memo icon is displayed in a standard entryfield style, and the user would have to double-click on it to get it to display an editor window.

NEW! In dBASE Plus vers. 2.21 and later, if you would rather that the grid (and browse from the Command Window) default to showing the older entryfield style for memo fields, you can set this by adding to the Plus.ini file the following:

   [Grid]
   DefaultMemoEditor=1

The number '1' in the above entry specifies that the older entryfield style be used for memos by default; changing this to number '0' sets the default to the newer editor (columnEditor).

In addition, the grid has a new property: defaultMemoEditor, which can be set to 1 or 0 (see above). The difference here is that you can set the column's editorType property to zero (default), and change the defaultMemoEditor property to show either entryfield or editor styles.

headingControl
The headingControl is the heading at the top of the column of a grid. It is used to display (by default) the field name. You can change the text in the heading, and the font properties, and more. The following are some of the properties available (the events available are all mouse events, which are described in online help).

function can be used to determine how the text appears.
picture can be to determine how the text appears.
fontBold, etc. this will override the grid's headingFont properties.
value this is the actual text that appears in the column heading.
colorNormal this will override the grid's headingColorNormal property.

New! In dBASE Plus vers. 2.2 and later, functionality has been added to column headings -- and it's automatic. Clicking on a column's headingControl will select that column, placing focus on it, unless the grid's rowSelect or multiSelect properties are true. This takes effect on the mouse up (when the user lets go of the mouse button). This allows the user to select the column with the header. It's a very nice feature, and one that's been requested for awhile.

dBASE Plus vers. 2.2 and later also includes the ability to click on a row or rows (depending on properties set for the grid) using the grid indicator column. This is something that wasn't possible in earlier versions (in other words clicking on the indicator column -- the one with the arrow-head pointing to the current row had no effect). This means that:

Note that the big difference here is that in dBASE Plus 2.2 and later versions you can do this with the indicator column, you can do this in earlier versions using the same keystrokes noted above by clicking on the rows themselves (and can still do that in dBASE Plus ...).

Changing Fonts

dBASE Plus has added some control over the grid that was missing in earlier versions, specifically there are now grid defaults for fonts and for colors. In addition, you can override the default colors with your own.

If you wish to set default fonts, click on the grid in the form designer, and then in the inspector set the font properties that you wish to affect all columns in the grid. Note that you can set font properties for the columnHeadings as well as the grid columns. The following properties are new to dBASE Plus, and may be set for the font defaults:

Once you have done the steps shown in 'getting started' above, you can set the font for individual columns with the following:

  1. select the column -- this can be done in one of three ways:
    1. click on the column itself in the designer -- this may require two clicks -- the first to get focus on the grid, the second to focus down to the column. Do not click on the column header, or the indicator column -- that will only place focus on the grid itself.
    2. in the inspector, after you have selected your columns, you must first move the indicator off the 'columns' property, and then back (this is to ensure that it "took"), and then if you click on the 'I' (inspect) button, you should get an entry in the inspector that shows "Array Elements" -- if you open this section up, you will see "Column 1" through the last column number. You can click on an individual column from there and then click the 'I' button to inspect that column ...
    3. in the drop-down list at the top of the inspector, you can scroll down to "columns" for the grid, and then in the inspector you can see each column. (Click on it, click "I", and you will have selected that specific column ...)
  2. Now that you have the column selected in the inspector, you will see some entries: editorControl and headingControl -- these are the items you need to change the font in ... Click on the editorControl, and the 'I' button.
  3. From here your font properties should be available ... set the font as you prefer. You can do the same for the headingControl ...

Upgrading your grid from an earlier version of dBASE to dBASE Plus (or later) with these new properties, and want to take advantage of them?

Once you set the default values, you can go through the columnHeading or editorControl font settings and delete them. You may even want to do it before you set the default values, so that you can see the change take place. You can do this in the source editor, or if you are not comfortable with using the source editor for this, use the form designer. In the Inspector, once you've gotten to the specific column, find the property you wish to remove, delete the value, and press the Enter key (when the property shows a yellow background -- if you don't press Enter the change will not be accepted).


Changing Colors

Colors can be set in a similar fashion to changing fonts, and again the color changes are done column by column, although in dBASE Plus you can also set grid default colors.

In dBASE Plus the following grid properties have been added or enhanced:

bgColor this has been fixed in dBASE Plus so that setting this will affect the background color of the grid -- the portion that is not covered by columns or rows (the right and/or bottom of the grid). The default is set to gray in dBASE Plus (so as not to break anyone's existing color scheme).
colorHighlight this is the color of the currently selected cell when the cursor is on it.
colorNormal this is the default color of all cells in the grid when not selected.
colorRowSelect in Visual dBASE 7.x through all versions of dB2K, if you have rowSelect=true for a grid, the color of the selected row has been the color selected in Windows for "Selected Items". In dBASE Plus you can now set the foreground and background color of the row that is selected.

Upgrading your grid from an earlier version of dBASE to dBASE Plus (or later) with these new properties, and want to take advantage of them?

Once you set the default values, you can go through the columnHeading or editorControl color settings and delete them. You may even want to do it before you set the default values, so that you can see the change take place. You can do this in the source editor, or if you are not comfortable with using the source editor for this, use the form designer. In the Inspector, once you've gotten to the specific column, find the property you wish to remove, delete the value, and press the Enter key (when the property shows a yellow background -- if you don't press Enter the change will not be accepted).


Column Widths

Once you've set any fonts that you wish to, you can set focus to a column in the form designer and change the width in the inspector. You can set this value by typing a number or using the spinbox buttons to make it smaller or larger ...

You will want to spend some time getting it "just right".

dBASE Plus fixed the grid columnWidth property as well -- if the user changes the columnWidth, the grid will return the current width (this is useful if you want to save the information and restore it later).


Cell Height

The cellHeight property can be set to affect all cells in a grid, including the headerControls for each column.


Options

There are quite a few options for the grid, and you should be aware of what these can do for you ...:

allowAddRows Does what it sounds like -- if this is set to true (the default) the user can add rows by going to the bottom of the grid and hitting the down arrow ...
allowColumnMoving Do you want your user to do this? There's no way to remember where they may have moved any specific column to ... At least not without a lot of programming (and I am not sure I would want to try to handle that kind of code ...) See sample form testGrid.wfm, which allows column moving.
allowColumnSizing Ditto
allowEditing This is what it sounds like. You can make your grid readonly by setting this property to false. (You can make individual columns read-only by setting the readonly property in the rowset.fields array)
allowRowSizing Same as the columnSizing and such ...
defaultMemoEditor
New to dBASE Plus, 2.21
If a grid column's 'Type' is set to 0 (Default), or if you simply datalink the grid without assigning columns, this property will determine if memos are displayed in editor style (default, property is zero (0), or entryfield style (property is one (1)).
frozenColumn This allows you to force the grid to only allow entry in a single column. The cursor cannot move to another column when this is defined.
hasColumnHeadings You can turn these off ...
hasColumnLines You can turn these off -- these are the lines between columns.
hasIndicator The indicator column can be turned off ... (that's the grey column on the left that shows which row is the 'current' one)
hasRowLines Same as columnLines -- if you don't want any grid lines turn off both ...
integralHeight This is handy -- it forces the last row to display only if there is enough room for it to, otherwise it displays the grid's background color. (No partial row ...)
lockedColumns Number of columns that are locked on the left, when a grid scrolls to the right. These columns do not scroll off the left of the grid.
multiSelect You can allow the user to select multiple rows ... (see online help for more info, and there's a bit of information below for this ...)
rowSelect This shows the current row by highlighting the whole row. It's handy, but note that your grid is non-editable if you select this option -- this is "as designed". This is really useful for those situations where you have a seeker control on the form, and you are just displaying to the user which row has focus based on what they entered ...

Displaying a Calculated Field

Calculated fields are useful for a wide variety of things, but ... they must exist in the rowset. There are a couple of ways to do this. One of these is to use a datamodule -- you can create your calculated field(s) there, and when you bring the form up the calculated field will be there. However, you can also do this with a standard query on a form. If you create the calculated field on a query in the designer, you will need to close the form and re-open it (the query's onOpen event, which is where the field is added, will not fire until you exit and re-open the form ...).

For the query (the object appears as an 'SQL' image) for the table where you need the calculated field, do the following:

        f = new Field()
        f.fieldName = "My Calculated Field"
        // note: this will fire for each row ...
        // "this" refers to the field, "parent" is the fields
        // array ... (substitute 'field1' with the name of the
        // field, and of course, you would want to set your
        // calculation for what you need)
        f.beforeGetValue = {|| this.parent["field1"].value * 2 }
        // here "this" refers to the current query:
        this.rowset.fields.add( f )

Once you have done this, save your form (or datamodule), and exit the designer. If you were working directly on the query (in the form designer), re-open the form in the designer. If you were creating a datamodule, in the form designer, rather than dragging your table to the surface of the form, drag the datamodule. As soon as you do this, the query's onOpen event will fire, and your calculated field will appear in the field palette, as well as when you design the grid.


MultiSelect

This is a useful area for some developers, but there are some quirks in how this works. MultiSelect allows the user to select multiple rows by using the CTRL or SHIFT keys along with mouse clicks.

When you set multiSelect to true, you may use the grid's selected() method to return an array of bookmarks to the selected records. This differs from the way a listbox's selected() method functions, in that a listbox's selected() method will return the text of a single item if multi is set to false. The Grid's selected() method does not fire if multiSelect is false. The grid's selected() method always returns an array object, like the listbox does when multi is set to true.

Once you have the array of bookmarks, you may process the selected records. Be aware of the fact that bookmarks are transient. Changing an index order, filter condition, or setRange will destroy them. Code for processing selected records could look something like the following (this assumes a pushButton that you click ... this was copied from a working test form/grid ...):

   function PUSHBUTTON1_onClick
      // if the number of rows selected is greater than zero:
      if form.grid1.selected().size > 0
         // get an array of bookmarks to the selected records
         aRows = form.grid1.selected() //array of bookMarks
         // disp. number of rows selected (not necessary, just a demo!)
         ? "Number of rows selected: " + aRows.size
         // Loop through the array
         for i = 1 to aRows.size
             // the goto method uses bookMarks, which is
             // what is stored in the array:
             form.rowset.goto( aRows[i] )
             // replace the fieldName below with the name of
             // the field you want to display ... note
             // that you can do whatever processing you may
             // need to do here -- this is just an example:
             ? i, form.rowset.fields["holname"].value
         next // end of looping through array
      else
         ? "No rows selected"
      endif
      form.close() // of course, you may not want this ...
   return

Saving/Restoring Column Positions

dB2K added to the grid a new method -- this method is getColumnOrder() and can be used to save the column layout for a grid. At the time of this writing, the method does not save the column width property, but it does save the position and the field information. Note: in dBASE Plus the grid's columnWidth is correctly changed in the form if a user changes the width, so you can ask for that information if you need to, and restore the value ...

An example of its use is given in the sample form 'TestGrid.wfm', with a fully-functional form that you can test.

The code is a bit complex in some spots, but this is because I tried to anticipate a few things when writing it. If you try this sample code, run it, move some columns around, and close the form. When you open it up, you will see that the columns should be in the same position that they were when you closed the form. The widths are not saved, hopefully in a future update to the software ...


Saving/Restoring Column Information -- Part 2

dBASE Plus has added even more functionality, in that there is now an even newer method of the grid (dBASE Plus release 2.01 and later) named getColumnObject(), which can be used to return a pointer to any specific column.

Things to know about this:

  1. If you have not defined the columns array (meaning that there are no 'custom' column objects), this method will return a null value.
  2. This is a pointer to the actual column object. What this means is that you can change values, select values, and so on, programmatically, at the time you have the pointer.

How do you use it?

Assuming that your form's grid has custom columns:

   oColumn = form.grid1.getColumnObject( nColNumber )

Where nColNumber is a numeric value from 1 to the last column in the grid.

Once you have that, you can do such things as troll for information:

   ? oColumn.width
   ? oColumn.editorType
   ? oColumn.editorControl.fontName
   // etc.

What this means is that if you need to save information about a column to an array or table so you can re-create the column later, you have full access to that information. Even more importantly, the information is current -- it is what the user last saw, including column widths and more.

The sample form "testGrid4.wfm" is a more involved, but perhaps even more useful version of testGrid.wfm, which allows the user to move columns around, change sizes, and all that, saves the information, and when the form is re-opened, it should restore even more accurately ... it uses a combination of getColumnOrder() and getColumnObject().


Programming A Grid

Your best bet to learn more about programmatic control of a grid is to create a test form, and examine the streamed out code. You can also learn from looking at other people's code ...

There are examples available all over the place, including the forms that come with this How To document (testGrid.wfm, testGrid2.wfm, testGrid3.wfm and testGrid4.wfm), the Contax sample application that ships with dBASE (see specifically the AddressBook.wfm file, and look at the "Name_onLeftMouseUp" and "Company_onLeftMouseUp" event code), and other places, such as "Vesper" -- an application available at my personal website (listed at the end of this document).

TestGrid2.wfm shows an example of changing a grid's column colors and the index used to display the grid using the grid's headingControls (as well as using the grid's bgColor and other default color properties available in dBASE Plus).

TestGrid3.wfm (created by, and used with permission of, Michael Nuwer) shows an example of changing a grid's column colors and the index used to display the grid with radioButtons.


Custom Grids

It is possible to create a custom grid control that you can store in a custom conrol (.CC) file, and place onto a form in the designer with properties set. This works like other custom controls.

Note, however, that there have been reports in the dBASE newsgroups of problems with custom grids that are actually stored in a container in the .CC file (removing the container from the equation seems to solve the problem, but if you have a set of controls that you always use, a container is a handy way to do this ... it's definitely a bug, if it's real, and needs to be looked at by the developers ...).


The End

The idea of this little discussion is to help folk who are confused about some of the basics of using the grid. Trying to go beyond this, use the standard resources: the dBASE, Inc. newsgroups. Details on the newsgroups can be gotten at the dBASE, Inc. website.

Sample code can be found on various websites. I can point you at mine (which has links to a lot of other sites, and some useful code, including the dUFLP library):

 http://www.goldenstag.net/dbase

As noted above, the Vesper applicaiton has an example of some of what can be done with a grid, including some programmatic control.

Enjoy -- Ken Mayer (dBASE, Inc. Quality Assurance Engineer)

(Special Thanks to Gary White, Michael Nuwer, Barbara Betcher, Dan Howard, Michael Joyce, John Staub, and Martin Kay (dBASE R&D) for editing and making suggestions)


DISCLAIMER: the author is an employee of dBASE, Inc., but has written this on his own time. If you have questions regarding this .HOW document, or about dBASE you can communicate directly with the author and dBVIPS in the appropriate newsgroups on the internet.

.HOW files are created as a free service by members of dBVIPS and dBASE, Inc. employees to help users learn to use dBASE more effectively. They are edited by both dBVIPS members and dBASE, Inc. Technical Support (to ensure quality). This .HOW file MAY NOT BE POSTED ELSEWHERE without the explicit permission of the author, who retains all rights to the document.

Copyright 2004, Kenneth J. Mayer. All rights reserved.

Information about dBASE, Inc. can be found at:

       http://www.dbase.com
    

EoHT: GRID.HOW -- January, 2004 -- KJM