The Grid Control
Lisa Slater Nicholls
The Grid Control has been described as "BROWSE on steroids". But is the Grid just a more-powerful, less-painful BROWSE? This session covers the Grid control and its component objects (columns, headers, and contained controls). We'll discuss the relationship of Grid and Browse, and how you can manipulate Grid's appearance, relation to data, and its special events and methods, to achieve the results your applications need.
The heart of this session is found in its source codes, so you'll find that the accompanying session notes are relatively short.
Please be sure to run LSN_DEMO to check out all the Grid examples and to investigate the accompanying files. These notes will merely point you in the direction of the "goodies" you'll find there.
The Grid As Container Object
The most amazing thing about the VFP grid object is how little of it there is.
When you first drag a default grid object onto a form, it seems complete in itself. If you bind it to data by filling its Datasource and DatasourceType properties, it will comfortably display the table, alias, or query result you've specified. It's just like a BROWSE with no switches.or optional clauses.
A BROWSE with no switches or optional clauses doesn't satisfy you for long. You soon learn that the real power of the BROWSE lies in the ways you configure it with the vast number of configurable options the BROWSE command offers.
Similarly, a Grid starts to show its muscle when you custom-configure it. Almost every kind of tailoring you do with a Grid forces you out of its default ControlCount = -1 state, to design separate columns within the Grid object.
So far, this seems analogous to a BROWSE you configure by adding a FIELDS clause.
There's an important difference, however: the Columns are objects in their own right, and you manipulate them all separately.
In fact, you will find that you very rarely interact with the grid directly.
Instead, you talk to the controls and objects the grid contains, starting with the all-important columns. Data, headers, and display elements (controls such as textboxes, checkboxes or other controls) are all "owned" by columns. Each object and object level has its own set of properties, events, and methods, depending on its job. So learning to work with the grid is largely a task of learning to work with these component parts.
The jobs of the different member-levels
Your first look at the PEMs for each component is a bewildering experience, but when you get to know them better, the division of labor makes sense.
Columns in grids are responsible for one field or calculated value each. By default, each column owns one header to describe its piece of data, and one control (a textbox) to display the data and accept edits. You can change the column's header, and you can add more controls to the columns, but you can't simply add controls (other than columns) to the grid itself. This is exactly the same as Pageframes; they don't accept controls themselves, but instead they contain pages to which you add controls.
Because the column theoretically owns the data, the column object has a Controlsource property to bind it to the data. However, since controls like textboxes also have Controlsource properties, there is a potential for conflict that must be mediated. The column has a Bound property, normally set to .T., to indicate that its contained controls must share its data source. If you set Bound to .F., you can force a control contained in the column to display other data although you'll be in real trouble if you try to manage data edits this way!
The Bound property is not the only time that conflict exists, but most other conflicts are resolved using the same rule that the rest of Visual FoxPro observes with regard to containership: the contained object wins.
For example, a textbox and a checkbox are both in a column in a grid which is on a form. The textbox, the checkbox, the grid, and the form, all have Mousepointer properties. Which "wins" (which Mousepointer property determines the pointer the user sees)?
The innermost object wins. In this case, either the textbox or the checkbox may be the innermost object, depending on the column's CurrentControl property (which in turn is determined by the expression in the DynamicCurrentControl property in some instances!).
You've probably already experienced this rule with events as well as properties. For example, when you click on a textbox on a form, you trigger the textbox's Click event, not the form's. Similarly, when you click on a textbox in a column in a grid on a form, you only trigger the textbox's Click event, not the grid's.
So where is the grid, anyway??
Where would you actually get a response from code in the grid's own Click event or see its appointed MousePointer?
The Click event on the grid will fire only in areas of the grid that have no data, and thus have no corresponding controls to manage that data. You'll only see the Grid's Mousepointer in the same regions.
You're really not going to be able to do much with the Grid's Mousepointer or Click event, because they hold sway over such a small portion of the grid. For most purposes, you can think of the Grid's set of PEMs as limited to those that affect the whole Grid at once, and that are not shared by columns and Headers.
For example, the Grid has only one HeaderHeight, affecting all Columns (Headers can't have their heights adjusted individually). The Before RowColChange( ) and AfterRowColChange( ) methods belong to the Grid, because a row change is something that happens throughout the Grid, not a Column at a time. The RecordSource property which indicates the data set the Grid displays belongs to the whole Grid, and cannot be broken down into separate RecordSources for separate Columns, with results we'll discuss later.
The Grid itself turns out to be the simplest, and perhaps the least interesting, part of the whole problem.
Adding controls into columns visually
To get anything interesting out of the Grid, you add objects into it.
You probably already know how to change the number of columns on a Grid in the Form or Class Designer. Simply increase ColumnCount, and new Columns appear.
You rightclick on the Grid and choose Edit to "drill down" and edit the properties of individual Columns, Headers, and contained controls. While the highlight around the Grid shows that you're editing its contents, you can select controls from the Form Controls toolbar (either standard base class items or classes you've created), and drop them into columns. (They will appear in iconified form, not WYSIWYG, in the grid layout.)
While the highlight around the Grid show that you're editing its contents, you can also delete a member Column or contained control. Make sure that the object you wish to delete shows in the Object drop-down box in the Properties window, click into the Grid in the design window, and press Delete.
Adding custom columns and headers programmatically
Since, as we've already discussed, certain features of the Grid are really features of its members, you will doubtless want to create Column and Header classes with tailored functionality, and add these classes to your form. Unfortunately you can't do this in the Visual Designers. (There's no way to create Column and Header classes in the Class Designer and no way to drop them into an existing Grid.)
The AddColumn( ) method of the Grid shares the Designers' disability: only allows you to add baseclass Columns, supplied with baseclass Headers. I can't imagine why anybody would want to use this!
You can subclass these "leaf" classes programmatically, however. You'll find a Header class well used in the LSN_DEMO topic Grid Incremental Seek and Changeable Order on your source code disk, to allow the user to click on the Header to change the Grid's order. Here's the code I use (in LSN_GR2.PRG) to change the Headers on columns that have appropriate indexes:
* In a loop through the available fields, having
* previously gathered
available tags into an array:
IF ASCAN(laTags,UPPER(FIELD(lnIndex))) > 0 OR ;
ASCAN(laTags,"UPPER("+UPPER(FIELD(lnIndex))+")") > 0 OR ;
ASCAN(laTags,"LOWER("+UPPER(FIELD(lnIndex))+")") > 0
*&* no tag for this column, leave default header alone!
Notice that I add the header derived from my hdrOrderingColumn class using the ADDOBJECT( ) method and I also remove the other, default, header that the Column doesn't need, using the REMOVEOBJECT( ) method.
The class itself is in LSN_GRID.PRG, and contains a special trick with which I am very pleased I think you'll find its implications go far beyond this little test case: I added a Value property and a ProgrammaticChange method to my hdrOrderingColumn class. In every class that I have tested so far, a ProgrammaticChange method acts as an assign method for a Value property. This means that if you change the Value, the ProgrammaticChange method automatically fires.
Why is this such a big deal? Because sometimes you want to affect all the Grid's (or other container's) member objects at once. You can use a Setall( ) method to change a property, such as Value, for all the members of one container, or all the members of one container that belong to a specified class.. But we have no Setall( ) method to fire methods of all the containers' members at once.
Since the ProgrammaticChange method fires automatically when the Value changes, I can use Setall( ) to fire code as well as change property values.. Here's what the hdrOrderingColumn code looks like:
lnValue = THIS.Value
IF NOT EMPTY(ORDER())
*&* Null out all the headers at once...
*&* since one of them may currently be showing
*&* a symbol indicating an order set:
* the click is a three-way-switch
* (OFF, ASCENDING, DESCENDING), change it now
* the change of Value will once more
* fire off the ProgrammaticChange to adjust
* the caption for this particular header
CASE lnValue = n_ORDER_OFF
THIS.Value = n_ORDER_ASCENDING
SET ORDER TO THIS.TagNo
CASE lnValue = n_ORDER_ASCENDING
THIS.Value = n_ORDER_DESCENDING
SET ORDER TO THIS.TagNo DESCENDING
CASE lnValue = n_ORDER_DESCENDING
THIS.Value = n_ORDER_OFF
SET ORDER TO
THIS.Parent.Parent.RecordSource = ;
CASE THIS.Value = n_ORDER_OFF
THIS.Caption = c_ORDER_CAPABLE+ ;
CASE THIS.Value = n_ORDER_ASCENDING
THIS.Caption = c_ORDER_ON_ASCENDING+ ;
" " +PROPER(THIS.Parent.ControlSource)
CASE THIS.Value = n_ORDER_DESCENDING
THIS.Caption = c_ORDER_ON_DESCENDING+ ;
Note that the behavior of custom header, column, and page class methods in VFP 3.0 was somewhat buggy when the classes were added using ADDOBJECT( ) (they worked with ADD OBJECT, but the methods did not fire properly when the members were added dynamically using ADDOBJECT( ) after the class was instantiated). However, this is definitely in the process of being fixed right now and these techniques will be quite stable in the next release.
Without taking the time or space to go through the other goodies in LSN_GRID, let me direct your attention to the txtIncrementalGridSearch control from which I built the display controls all the grid columns in the same example program.
This class was a joint effort by my husband, Colin Nicholls, and me, to provide incremental search in a Grid. It has a number of special features, such as an intelligent effort to guess at whether the user's keystrokes should be evaluated using a case-sensitive or case-insensitive approach or whether incremental seek should be turned off because the current index is inappropriate (such as a logical key expression).
You could easily adapt the class to accept a parameter specifying the index key, rather than using the current key, if you prefer. The new SEEK( ) function parameter in VFP makes this especially easy to do. You could also evaluate the user's current column and figure out what index to use on that basis!
I preferred to have the user specifying the search order using the headers, in this instance and having incremental seek working in one consistent way no matter what column currently has the focus. I've added a Seekoff property you can use to allow the user or program to turn off the incremental seek function when it's time to edit the data.
I have to admit that I find the way Grids are bound to data extremely confusing.
Suffice to say that it's important to separate what occurs at the Grid level from what occurs for the individual columns, here as elsewhere in Grid exploration. In this case, remember that the Grid.Recordsource defines the data set through which you scroll (a table, alias, or query result) and the Columns' Controlsources represent the individual fields you see in each column, for the purposes of display and also for the purpose of sending a value back into the table.
Grids are only designed to work with a single Recordsource, just as listboxes in 3.0 are only designed to pull fields from one table/alias. This means that some of the tricks we used to do in 2.x, with related tables in a single BROWSE, are quite difficult to accomplish in 3.0. Related tables, either with SET SKIP and virtual records or with the parent table serving as a lookup to the primary table, are an irrelevant concept outside much of Xbase.
You will usually create a flat file in the form of a View when you need fields from separate entities in a Grid.
Related tables inside a grid
However, the LSN_DEMO topic Grid Formatting Tips and Tricks does have another way you can put related fields into one table, if you're not planning to edit them. This simple technique will allow a Grid to function as a picklist, just as BROWSE does (and like BROWSE it has less overhead than a listbox or combobox when you have more than a few items in the list).
The RecordSource for this "Picklist Grid" is set to a single alias, as normal. I've manually SET a RELATION between this table of salespeople and a lookup for the name of the office they work from. (Don't set the relation in the Data Environment, even though your tables may be there, as mine are! I have used the Init( ) method of the DE, which occurs after all its tables are opened, but I wrote the SET RELATION code myself.) I've used 1 column for this Grid, and marked its Bound property as .F., so that the expression I write as a Controlsource for a contained Textbox will be significant. Then I just specify the Textbox's Controlsource using an indirect reference containing a complex expression, instead of a single field from a single table, as follows:
(ALLTR(Picklist.Name)+" of "+OfcLookup.oName)
Obviously, a picklist like this should have its editing attributes set to Readonly.
One-to-Many Relationships using grids
Most of the time, you'll have a single table in the grid and you'll relate this table as the child side of a relationship with a parent record being edited on the same form. You can use a parameterized view for this grid's Recordsource. When the parent changes, all you have to do is change the value of the parameter and REQUERY( ) to synchronize the data in the view.
However, you don't need to use the parameterized view if you prefer not to. You can set relations just as you always did, or use the SET KEY ... RANGE... command that appeared in 2.6, to limit the records appearing in the Grid. You don't even need to refresh the Grid, even when you need to refresh other elements of the form because a parent record pointer has changed; the refresh happens automatically for Grids.
You'll see an example of this in the LSN_DEMO topic Dynamic Colors, Refreshing, 1-to-Many Rel. When you look at the code which navigates in the parent record, you'll find I do not refresh the Grid at all unless I've added DynamicColorControl to the mix. In that case, the Grid needs to evaluate the records for your color criteria. A simple
<gridreference>.DataSource = <gridreference.DataSource
is just fine to create the refresh effect when necessary. This seems to be more efficent than a <grid>.Refresh( ).
Buffering on the table level
You can probably see why grids have to be buffered as a set of records, usually when you're confirming the edit of the parent table edited in normal controls on the rest of the form. Buffering a row at a time doesn't seem right on a Grid. However, just in case you're not convinced that it's the correct or elegant solution, let me tell you that you'll also have horrid error-handling problems if you try to buffer the Grid alias table on a Row basis or not at all! In the illustrious words of Nike, "Just do it!"
Relative Rows, ordering the grid's data, and related events
The concept of relative rows is part of databinding in a grid, because it gives us an "alternative record number" based on the current order of the records rather than their physical order in the table. We've been waiting for it a long time, and now we have it, in the shape of the Grid's ActiveRow property. Be careful, however, if you evaluate ActiveRow inside the BeforeRowColChange or AfterRowColChange methods, since its new value may not be stored at the precise moment in the process that you expect.
In fact, I'm finding the two RowColChange methods much less useful than I first anticipated. For example, the AfterRowColChange may not fire if you move from the row to click in the unused portion of the grid, or even out of the grid itself.
I find that there are similar problems with all the Dynamic properties hosted by the Grid control. These controls, such as DynamicBackColor, require a character-type expression which will be evaluated to return a property value (in this case the Backcolor) of the grid. These properties are so dynamic that the expressions evaluated to get their values should make no assumptions about where the user will be when the they are re-evaluated.
For example, the grid in the Dynamic Colors, Refreshing, 1-to-Many Rel LSN_DEMO topic changes the grid colors if an invoice is unusually low or high. This should be a simple task. However, if the user chooses to navigate from the button in this sample screen, they are opening a BROWSE and returning to this dialog. This action calls forth the window' internal refreshing , including the Dynamic properties on the Grid.
Therefore the expressions on which the Dynamic properties rely must not use an expressions that assume the code is written from the Grid's "perspective", such as "THIS.Column1.Controlsource". Here's what I came up as an expression that wouldn't fail:
THISFORM.GrdMaster1.Column4.DynamicForeColor = ;
"IIF(TYPE('_SCREEN.ACTIVEFORM')='O' AND "+ ;
It looks clumsy and it is probably slow. But without know the actual variable reference name for the current object, it's the best we can do! Think of these dynamic properties as requiring almost as much care as referencing objects from your menu, even though they seem to be inside the objects themselves.
Grids and the DBC
If you bring in a default grid (ColumnCount = -1) and assign its Recordsource to a table in a Database Container, the grid will automatically look into the DBC to find out if any columns have long names (captions) that can be used in the headers of the Grid .
However, you already know that you will be constructing your own columns and headers. In this case, you can use the DBC yourself to check out the field captions and add them to your Grid object. Reference LSN_DEMO topic Grid Formatting Tips and Tricks. Here's the relevant code to do the job:
LOCAL lnIndex, llDBC,
lcField, lcTable, lnIndex
lcTable = SYS(2027,DBF(THIS.Recordsource))
lcTable = SUBSTR(lcTable,RAT("\",lcTable)+1)
lcTable = SUBSTR(lcTable,1,AT(".",lcTable)-1)
llDBC = INDBC(lcTable,"table")
FOR lnIndex = 1 TO THIS.ColumnCount
lcField = EVAL("THIS.Column"+ALLTR(STR(lnIndex))+".ControlSource")
lcField = SUBSTR(lcField,RAT(".",lcTable)+1)
STORE IIF(llDBC,DBGetProp(lcTable+"."+lcField,"Field", ;
"Caption"),lcField) TO ;
This code is fairly straightforward, and fairly generic. Note that you must make the proper DBC the current DBC for its attributes to be accessed.
Getting more out of the DBC
I've included another example of DBC-use that
you might useful to imitate, although I doubt you'll implement it
in exactly the way the example does it! I've added Format and
InputMask information into the DBC by storing it in lines in the
DBC's Comment field. Each line looks like this:
THIS.Format = "$"
There is one line like this in the Comment field for anything I'd like to adjust. Then, the Init( ) of the textbox in the grid can use DBGETPROP( ), as above, to get the Comment field and parse it for any lines beginning with THIS. It executes any such lines by macro-substitution.
If you decided to retrieve formatting information and more from the DBC, you should find out about Tom Rettig's public domain class library, EDC. EDC will extend the DBC and the attributes that DBGETPROP( ) can return to you. (I really don't think the Comment field is the right place, but it was already in the DBC and thus suitable for a short example.)
More refreshing and formatting
The example of this DBC-using technique in my sample code formats a Currency field. When you start working with Currency you realize that you should use Format and InputMask very strictly, so your money figures don't have extra 00's, both for display and editing.
In a Grid, the Sparse property is set ON by default, so formatting characteristics as well as complex controls are only applied to the Activerow. I find Sparse irksome for currency work, because the figures "jump" as they move in and out of focus, losing and gaining their final two characters. However, Sparse = .T. allows faster performance, so you'll have to make this decision on an individual basis.
If you toggle Sparse on and off in the sample I've provided, notice that rows without focus can show their contents in one of two ways:
The distinction is significant because in many cases you can remove the default Text1 textbox, leaving only your chosen control (such as the combo box for choosing different office numbers for a salesperson in my sample). This may not help performance as much as setting Sparse .T., but you can rest assured that your data will be viewable even without the textbox, and eliminating the extra control seems to help display speed a little.
Additional performance and display tips
Although I've had little success with it, many developers report a perceived performance boost by using the form's Lockscreen property, setting it .T. in BeforeRowColChange and .F. in AfterRowColChange. Lockscreen will limit the repainting that goes on while you're scrolling through records.
Grid controls have more than a few display peculiarities unrelated to performance. For example, when you tab to the rightmost column, you can be faced with a lot of extra space on the right side of the Grid as the columns move over, similar to the space at the bottom of the Grid when you reach EOF( ). I'm not sure why this doesn't happen when you tab leftwards to the leftmost column, and so far I haven't found a way to stop the columns from "sliding" over that far, except for giving the rightmost column a width equal to the Grid's width itself. Consider doing this if you experiment with color effects.
Is a Grid always best?
Don't get discouraged! Although even Microsoft admits that there are some things BROWSE did better, Grids are extremely flexible. We've already seen that an indirect reference allows us to cram fields from more than one table into the Grid when necessary, in imitation of a BROWSE. Almost everything else BROWSE does can be re-thought and re-designed into a Grid.
Replacing the Find dialog
One BROWSE capability I missed a lot was the ability to use the native FoxPro Find dialog within a BROWSE, to search for values across value types and across fields. I wrote a custom class to imitate this capability, called Locater, and I now use this class successfully with both Grids and BROWSEs by preference! See the LSN_DEMO topic Replace _MED_FIND for Grids. My Locater class improves on the native dialog by searching within memo fields. The text file LOCATAPI.TXT should explain how you integrate it into your programs.
Last, but not least, BROWSE NAME
The only serious deficiencies that Grid has, in my opinion, when you're used to dealing with BROWSEs, is in the lack of a true analog to WHEN and VALID /F row-level events. Before and After RowColChange don't perform exactly the same job.
Fortunately, you can get these capabilities back any time you want. Just use BROWSE!
You don't have to lose everything you get from a Grid, because BROWSE in 3.0 and Grids are fundamentally the same class underneath. Investigate the LSN_DEMO topic A Quick Look at BROWSE NAME for a demonstration of the new NAME clause, which gives you an object handle you can use to talk to your BROWSE almost the same way you talk to a Grid object. You'll see that BROWSE contains column objects, and you address these columns to set their properties just as you do for a Grid. A BROWSE without a FIELDS clause even grabs field captions from a DBC by default, just a Grid with ColumnCount set to -1 does.
... and Beyond
I have a long list of other possible examples I could have written. Grid has many possibilities untouched here.
For example, when and how would you use drag and drop to a Grid-contained control? What happens to a Grid when you adjust the properties of the DataEnvironment-contained Cursor that serves as its data source? Exactly when, in the event "stream" do the various components of Grid refreshing occur?
I think that you'll get a good start, however, if you concentrate on figuring out the jobs of each of the Grid member objects.
Use the Grid Builder, too. It takes away a lot of the drudgery of Grid-building work, but, more importantly, you can examine the choices it makes to understand how to roll your own Grids, especially with reference to data binding.
It's uphill work, but it's worth it. Be sure to let me know what you find out!
Lisa Slater Nicholls
Compuserve ID 71333,2565