What is the grid and when to use it?
Grid control is a set of the VFP objects that allow representing data in a grid-like scrollable list. Grid consist of the grid object itself and a set of the columns. Each column must have a header object and a control that represent a data in the grid column. The control in grid column is used to display and edit the data. Grid appears as a rectangle with the linear grid, headers at top of each column, scrollbars and some other useful things, like record marks, delete marks, split bar etc.
Really, representing of data is not as free as, for example, in the Excel sheet. Grid requires the VFP record source (alias) to display something. Grid column data type is hard to make different for each row. Grid row have the same height for all rows, and column have the same width for all rows in grid. There are also certain other limitations that are quite strange and weird unless we remember that grid is really is a control which code is based on the browse window that comes from earlier versions of the FoxPro. This fact makes an answer to many 'Why' about grid strange things and behaviours. Despite all troubles, grid is quite useful control and it is popular between VFP programmers, so there are also a lot of workarounds and solutions for grids that allow breaking the limitations and making really great things with grids. Limitations or strange things should not be a key thing in the decision whether to use grid control.
Grid control is useful for displaying (browsing) data in a compact form (huge amount of data per one page). Grid is good as a popup or separate page list for search and navigation. It is bad idea to use grid as a persistent control for navigation through data, because grid usually takes a lot of space. Many applications have grid as a main controlling form - complex grid with a lot of functionality that brings child forms for data editing.
Grid is useful for any kind of regular read-only data. It is bad idea to use grid for data editing. Data editing in grids is good and easy to organize for administrative applications only. Data editing in grid has a lot of troubles when trying to break through VFP grid limitations and weirdness. Anyway, such forms, as invoice or order items list, are better and more comfortable for users when represented as grid for editing. Such forms are an exception; do not put the grid on every form just because it is simple to edit data in it. When you do so, you will quickly find self in trouble because grid is such a complex control that easy can went out of control. You will require then put a lot of effort to figure out problem, find a solutions and fix further problems. Usually data are edited using a form with controls for each field and a navigation bar for moving through records, data saving/reverting and some other buttons specific for that form. Making such form as a grid is a way to spend a lot of time. Well, if you have that time and like to play around something weird, grid is an excellent control for spending time to find solutions and workarounds.
Grid automatic re-binding of columns
Because RecordSource property of grid is changed in design time. After that all ControlSource values are cleared. Make a backup of these values for such case, usually in the Comment property of the column. Because RecordSource property of grid is changed in run-time. Well, if you want to do this, save all control sources, then restore. Possible cause is that grid is reconstructed. Next chapter is about this.
Because RecordSource property of grid is changed in run-time. Well, if you want to do this, save all control sources, then restore.
Possible cause is that grid is reconstructed. Next chapter is about this.
It is not that sudden as the side effects of this behaviour. Usually there are no differences, unless grid has some complex functionality and architecture. For example, column values displayed by expression - expression will be lost and field is just displayed. ID field (key field) column displayed in grid is also bad thing for users. The most dangerous is when you have some unusual controls in the grid. When column has checkbox control, but grid decide to use character type field as a control source for that column, VFP throws error about data type mismatch: 'Type is not supported by control'. Despite all above, there are situations when record source should be altered anyway, for example, for avoiding grid reconstruction behaviour.
Following are code samples of saving the control sources of all columns and restoring them using Comment property of the column.
&& backup the ControlSource of each column with {grid} local nColumnIndex for m.nColumnIndex = 1 to .ColumnCount .Columns(m.nColumnIndex).Comment = .Columns(m.nColumnIndex).ControlSource endfor endwith && restore the ControlSource of each column with {grid} local nColumnIndex for m.nColumnIndex = 1 to .ColumnCount if !empty(.Columns(m.nColumnIndex).Comment) .Columns(m.nColumnIndex).ControlSource = .Columns(m.nColumnIndex).Comment endif endfor endwith
In above code {grid} is a reference to the grid object.
It is nice idea to make these code snippets as a methods of the grid class. It is good also to call the restoring of control sources in the Init method of the grid class for case when ControlSource values are lost because record source change in design time.
Significant note: do not do any refreshing of the visible controls or grid on the form before full restore of the control sources. Otherwise you will meet a problem with the error message like 'Type is not supported by control' in case you use custom controls in grid columns. This because after spoiling control sources incorrect field types might be used for column as already mentioned before.
Grid automatic reconstruction
1. Grid reconstructs self always when RecordSource alias closed. If this is a view, reconstruction usually does not happen when you requery view. If it is SQL statement, it happens when you assign another SQL statement or just close alias used to store results of the query for grid. It also happens when you use SQL Pass-Through to query data again into the alias that used as a record source of grid even when you use it by the same way as view.
To avoid reconstruction when refreshing grid record source, you require assign empty string (not a one space - " ", but empty string - "") to the Record source before any of grid record source refresh actions described above. Check your code accurately if you do that in correct order or any other thing does not spoil the correct order of refreshing process. (You can track the code line that causes reconstruction by putting the breakpoint or SET STEP ON command in the BeforeRowColChange event of the grid.) After record source refresh, assign record source to grid again. Reconstruction in such case does not happen, however, automatic re-binding of columns happens because record source assignment. Following is a sample of how to fix that by little of code.
In above code {grid} is a reference to the grid object, {RecordSourceName} is a name of the alias used as a record source or SQL statement. In generic case with SQL statement as a record source alias name could be get from the ControlSource of the column because VFP always change it to be represented in form 'Alias.FieldName' despite in design time only field name is specified. Above way is also useful to change the SQL statement for grid.
Significant note: do not do any refreshing of the visible controls or grid on the form after statement 'RecordSource=""' and up to full restore of the control sources. Otherwise you will meet a problem with the error message like 'Type is not supported by control' in case you use custom controls in grid columns. This because after spoiling control sources incorrect field types might be used for column. For example, when you have a checkbox in the grid column, after refresh of record source column with checkbox often get a character field control source. If then you refresh a grid, you will get an error or something weird might happen like crashes or bad refresh.
Another approach to prevent grid reconstruction is to use BeforeRowColChange event of the grid. BeforeRowColChange event is fired each time when grid is going to be reconstructed. It happens in any case include when grid alias closed, SQL Pass-Through cursor requeried etc. despite grid visibility, focus and grid configuration. The most amazing is that putting NODEFAULT in this event for duration of data changes prevents grid reconstruction at all. However, in such case grid can show weird behaviour when done inaccurately.
thisform.GridRefreshing = .T. && tell all grid controls that && grid data will to be requeried ... do the data requery thisform.Grid.RecordSource = thisform.Grid.RecordSource thisform.Refresh && or grid refresh DOEVENTS && if needed - just test without this command && after this moment grid stops to reconstruct self> thisform.GridRefreshing = .F.
In the BeforeRowColChange of grid class event put following:
if PEMStatus(thisform,"GridRefreshing",5) AND thisform.GridRefreshing nodefault return endif
Unfortunately, there are no way to know the reason why BeforeRowColChange event is called to distinguish if it is called for reconstruction or it is called for movement between cells or grid got focus. Use a flag for that as in the sample. If you have a time, you can also make a grid class with the transparent cover shape that catch all mouse events and forward them to the grid after pre-processing. This way grid will know that BeforeRowColChange is fired because row or column is going to change. This requires also catching of the KeyPress event (better do it on the form using KeyPreview=.T. for form). Finally, grid should be put into container to catch the moment when grid gets focus (BeforeRowColChange is fired also when grid gets focus).
Being frustrated by grid reconstruction behavior, programmers sometimes make create and maintain the cursor as the Record Source of the grid, then refresh such cursor by data deleting and copying. In case you already have such cursor, it is not hard to delete all records from cursor and add them again.
2. Reconstruction happens when grid is initialised and record source property is empty or record source does not exist (alias is not open). In this case grid reconstructs self and use current alias as a record source if opened (or keep self empty if no alias opened in current work area, but all columns destroyed anyway). If you need to open record source in some other event than Load event of the form (before grid initialising), use following techniques.
In the Load event of form create an empty cursor with the same structure as a record source for grid; record source property of grid should use that empty cursor alias name. Then, when you open real data, assign empty string to grid's record source, open data and then assign real data alias again as described for the case A. Another workaround is to put an invisible custom control that will create empty cursor in its Init event. However, assure that Init event of that control fires BEFORE Init event of the grid, otherwise reconstruction will happen. Second approach is to add grid to the form on the fly. Make a grid class and do not put it onto the form in design time. In the Init event code put an AddObject method call for container that should contain that grid to add the grid after required alias is ready for use.
3. Grid reconstructs self when column count changed to 0 or -1. I hope you never do this, do you? ;) Anyway, it could be used in the simple administrative form that allows opening any table and browsing it in a grid. However, because reconstruction, such grid can have limited functionality, or all grid functionality should be put in the classes added to grid in run-time just after reconstruction.
4. Grid reconstructs self when record source goes out of scope. This usually happens when record source assigned in one data session, but grid really initialised in another data session, so when it tries to refresh self, another data session used where record source does not exists. This may occur also in other situations when programmer uses data sessions switching extensively.
Reconstruction could not be prevented by making the additional reference to each grid column - columns are just detached from grid. In addition, making grid with columns already defined in the class does not prevent reconstruction too - this is exception for the rule that child objects of the container class could not be deleted when using that class on the form.
Another popular approach to eliminate the grid reconstruction problem at all is dynamic grid creation. Make a custom grid class with all your code and columns definitions. When requery data, remove grid control from form, requery data, then add grid to form again in run-time and position it again. This requires handling the adding of grid first time, set some properties of grid etc etc. However, this approach is bad because requires creating the class for each particular grid on each form, know that class name, have the routine for each grid - a lot of code that could be avoided.
You can also create grid object in run-time completely and populate it by the custom controls using code. However, after grid reconstructs self, you need to add these custom controls to grid again. This approach used in case when grid reconstruction is not avoidable, for example, in the administrative programs - to show any table content in the same grid, but also allow some functionality in grid like edit boxes to see memo fields, sorting by click on headers etc.
Lock (freeze) column to be always visible despite scrolling
There is a nice property LeftColumn in the grid that is a column order number of the leftmost column currently visible. When grid is scrolled horizontally, this value changes. We can use this to froze column in the Scrolled event:
if nDirection>3 this.Columns(1).ColumnOrder = this.LeftColumn endif
this.Columns(1).ColumnOrder = this.LeftColumn
Grid tips
The first tip I want to make is related not only to grids. Search the UT site for information about grids. You will find a lot of useful things! And you can always ask any question there. When use SQL SELECT statement as a record source, always add 'INTO CURSOR …' at the end of this statement, otherwise browse window will appear at the form loading or just when that query runs. You can use the expression as a column source! Just make column as read-only and specify the expression to display in the column ControlSource. Unfortunately, the result of expression should be the same type for all grid rows; otherwise you can get weird formatting, asterisks or some other strange effects. For different data types better use the container in grid column… To add new control to grid column, select the column in the combobox in the properties window, click in the edited form header to select it, select required control and drop it onto the grid. To remove control, select that control in the properties window, select the form by clicking on the form header, then hit 'Del' button. When there are no horizontal grid lines, the grey lines on their place when Sparse=.F. or HighlightRow=.F. are just whatever is under the grid (usually the form surface). Put the white shape under the grid and they will become white. You can change the mouse cursor for column by setting the mouse pointer in the MouseMove event of the column.
When use SQL SELECT statement as a record source, always add 'INTO CURSOR …' at the end of this statement, otherwise browse window will appear at the form loading or just when that query runs.
You can use the expression as a column source! Just make column as read-only and specify the expression to display in the column ControlSource. Unfortunately, the result of expression should be the same type for all grid rows; otherwise you can get weird formatting, asterisks or some other strange effects. For different data types better use the container in grid column…
To add new control to grid column, select the column in the combobox in the properties window, click in the edited form header to select it, select required control and drop it onto the grid. To remove control, select that control in the properties window, select the form by clicking on the form header, then hit 'Del' button.
When there are no horizontal grid lines, the grey lines on their place when Sparse=.F. or HighlightRow=.F. are just whatever is under the grid (usually the form surface). Put the white shape under the grid and they will become white.
You can change the mouse cursor for column by setting the mouse pointer in the MouseMove event of the column.
Grid warnings
Do not leave Record Source property empty before grid initialising.
Events and properties of the control in grid column are used only for current cell when column sparse is .T. When Sparse is .F., properties are used for displaying, but events are used only for current cell, anyway.
Scrolled event is fired only by scrollbars. When grid is scrolled by keyboard or programmatically in certain cases, Scrolled event is not fired.
RelativeRow and ActiveRow properties are available only when grid is focused. ActiveRow is always zero when current record is out of the visible records.
VFP Grids, Next Parts
