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.

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:

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):

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).

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.

• If a cell is selected in a column, and you click on a new column heading, the appropriate cell in the new column, but the same row, will be selected.
• If no cell was selected, clicking on the column heading will select the first cell in that column, unless the rowSelect or multiSelect properties are true.
• If rowSelect or multiSelect are set to true, column selection does not occur, as it's not a valid option.

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:

• If a cell has focus, selecting a new row this way will set focus on the same column in the new row.
• If rowSelect and/or multiSelect are set to true, selecting a row can be done by clicking on the row or on the indicator column.
• In the case of multiSelect, you can use all the standard keys:
  • To get a contiguous group of rows, click on the first row, then hold the shift key down and click on the last row you want to select.
  • To get a non-contiguous group of rows, press ctrl and click on individual rows you want to select.

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:

• fontBold
• fontItalic
• fontName
• fontSize
• fontStrikeout
• fontUnderline
• headingFontBold
• headingFontItalic
• headingFontName
• headingFontSize
• headingFontStrikeout
• headingFontUnderline

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:

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 ...:

Displaying a Calculated Field

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

• Click on the query, so it has focus
• Go to the EVENTS page of the inspector
• Select the onOpen event, and the tool button.
• Add code like 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