Grid column and header tricks - how to make development with grids go faster
Many times when you try to make simple application to check if something is working for grid or how it will look, you can catch self that there are a lot of things required to set up for grid to get the desired looking of it. Specify RecordSource, specify ControlSource for each column, specify each header caption and so on. Sometimes you require to add some functionality to grid, like double click on the row to popup another window - this also require to add code for each control inside grid column. It is ok when you have only one grid in your application and little of columns in it. However, when you have a lot of grids in the application or grid with large number of columns, you will quickly get tired to do all these things for each grid and column.
It is easy to define a class for grid and for control in grid. This practice is used widely. However, there is also a good approach for organizing your base classes for grid using Object-Oriented programming that will make a good base for programming grids for all your applications.
First of all, define the grid base class that will provide a template for all functionality and abstract methods. Note that you require leave the ColumnCount property with the default value in your class, do not define any columns in the grid base class, otherwise it will be hard to handle them in the object instances of that class. Adjust grid appearance for the most common case in your applications. For example, in my applications usually I do not display Record Marks (they refresh incorrectly when grid is not focused, and current record is highlighted by another color) and Delete Marks in grid (grid is usually read-only control).
Then, define classes that will be used as controls in grid column. Usually this is a text box, but you might also want to have a checkbox, combobox, EditBox (to display memo field content) and, maybe, some other unusual custom controls. You will use these controls only inside of the grid and never use them in other places. In the classes you can organize better way the functionality that you want to be common for all columns in the grid. Do this by calling the abstract method of the base grid class. For example, to do this for double click, in the DblClick event of each class for grid column add following:
This.parent.parent.eventDblClick(this.parent)
In this sample eventDblClick is an abstract method in the grid base class - it contains no code except the 'lparameters' statement to accept a single parameter (reference to the column which was double clicked). Now what we will get from this? Without this we had to define the new method on the form with double click code for grid, then add call of that method from control DblClick event in each grid column. When you have a lot of columns, this is quite boring. With new approach you just write your code in the eventDblClick method of your grid class. Well, this also requires putting your own class into column instead of the default textbox, but it is an easy thing too. Put following code into your grid base class Init method:
LOCAL liColIndex FOR liColIndex=1 TO this.ColumnCount && loop through all grid columns WITH this.Columns(liColIndex) IF upper(.CurrentControl) == 'Text1' && replace only columns that have default textbox .Text1.Visible = .F. .AddObject('Text2','{GridTextBoxClass}') .Text2.Visible = .T. .CurrentControl = 'Text2' .RemoveObject('Text1') .Text2.Name = 'Text1' ENDIF ENDWITH ENDFOR
After you tried this simple thing in work, add more stuff. Define more events by forwarding event call in the column control to the grid method. Add more classes that you might want to use in the grid, then enhance the code for replacing the default control by adding the control of the class that is appropriate to the type of column control source (you can determine it using type function, for example: type(.ControlSource)). Usually checkbox is used for logical field and edit box for memo field.
Now grid is much more easy to use on your forms, but... What about grid headers? It is good idea to write a code for grid headers that will fill the caption property of header automatically based on the control source of the column. For example, get the user-friendly field caption from the database using DBGETPROP(.ControlSource, 'FIELD','Caption') after checking that ControlSource is really field and not expression. Code sample, in the Init of grid, is following:
LOCAL liColIndex, lcCaption FOR liColIndex=1 TO this.ColumnCount && loop through all grid columns WITH this.Columns(liColIndex) && check if header is not set up by developer IF upper(.Header1.Caption) == 'HEADER1' && check if control source is really field, not expression IF '.' $ .ControlSource AND ; FSIZE(substr(.ControlSource,at('.',.ControlSource)+1),.parent.RecordSource) > 0 && check if alias is from VFP database IF !empty(cursorgetprop('Database',.parent.RecordSource) lcCaption = DBGETPROP(.ControlSource,'FIELD','CAPTION') ELSE lcCaption = '' ENDIF IF empty(lcCaption) && assign just the field name if no other case lcCaption = substr(.ControlSource,at('.',.ControlSource)+1) ENDIF .Header1.Caption = lcCaption ENDIF ENDIF ENDWITH ENDFOR
You might want to do some more. For example, when using the field name as header caption, make some complicated transformations for it so it will look as good as possible, using the naming conventions for field names. As example of naming conventions we will take following: in database all fields except key field are prefixed by type character. Key field could be identified by 'ID' word in it. So we can just remove first character from the field name when it does not contain either 'ID_' or '_ID' or it is just 'ID'. Then replace all underscores by space using STRTRAN function and use the 'PROPER()' for result. This way 'cCust_Name' field will appear in the grid header as 'Cust Name'. You can have other naming conventions for fields, so look if you can improve the appearance. If you have something like StoneField Database Toolkit or your own database dictionary, you can do a query to the data dictionary to get the field caption and any other properties of field you want and apply them to the grid column headers and controls for data displaying. If the field is from view, you can get its source field from table to get its properties. You can get field name from the table appropriate to the view field only using expression like "DBGETPROP(.ControlSource, 'Field', 'UpdateName')". This is only for updateable fields, fields returned as result of expression in the query will have this setting empty.
Well, with captions for header - you can set up them in the Init of grid base class and forget about that. However, sometimes it is also useful to catch the header Click and other events. While it is easy to make a class for grid column controls, it is quite tricky for header class. You can organize the events system for headers in your base grid class by the same way; however, you can define your own header class easy way only by using the DEFINE CLASS structure in the program file, as in a sample below:
DEFINE CLASS MyHeaderClass AS HEADER PROCEDURE DblClick This.parent.parent.eventHeaderDblClick(this.Parent) ENDPROC ENDDEFINE
IF upper(.Header1.Class) == 'HEADER' && replace only columns that have default header class lcOldCaption = .Header1.Caption && store the caption to forward it to new header .AddObject('Header2','MyHeaderClass') && this will remove old header automatically .Header2.Name = 'Header1' .Header1.Caption = lcOldCaption ENDIF
In the most generic case, it is recommended to move code for replacing controls in grid by your own classes into a separate method of the grid. There are reasons exist why you might want it - grid reconstruction. There are a few situations when grid reconstruction is required. After grid reconstruction you can call the method of grid to set up your own classes again, so even when you show different data in the same grid control, it will still behave by the same way. This is because, despite reconstruction, the main code for events is now in the grid control methods, so it is not lost after reconstruction. More, after you run code for replacing the grid controls by your own classes, the grid will still have required functionality. So instead of creating several grid classes for each kind of record source to show them on the same form, you can now put a single grid and use reconstruction without much lose in the functionality; then enhance number of different record sources without creating new grid classes for each data source.
The bad thing is that Click event of the header is fired also when grid column is resized or moved. There are Moved and Resized events of the column, however... When it is easy to replace the default column header class by your own header class, it is quite hard to do with columns, and it will work slowly even when organized correctly. So it is not good idea to use column class for that. There is, however, good solution for that. Prepare OldColumnWidth and OldColumnOrder properties in your new header class. Than in the Init of the header set them to initial values from column Width and column ColumnOrder properties. In the Click event code compare the current Width of the column with OldColumnWidth property and ColumnOrder with OldCoumnOrder property of your header class. When values were different from old values, then column was resized or moved. When resized, just update the OldColumnWidth for current column. When column moved - you will require to update OldColumnOrder property for header in all columns of the grid. Well, column width or order could be changed programmatically in a few situations, so it might be also good idea to do not do this directly, but use special custom method in the grid class, called, for example, 'ChangeColumnWidth(ColumnNumber)', or 'ChangeColumnOrder(ColumnNumber)'. This way you assure OldColumnWidth and OldColumnOrder are always in synch with current column settings. And you now have opportunity to fire the code in your grid class custom method on column movement or resizing event using described approach - without really touching any method of the column! To speed up fields entering into the ControlSource of the columns, you can use simple approach. Define a property in your grid class (ControlSourceList) that will hold a list of the fields for each column separated by semicolon. Use semicolon and not comma because you might want to use expression in the control source of some column that could contain comma. I the Init event parse the string in that property, if it is not empty, and fill ContolSource of each column using items in the list in that string. Note that ANY refresh of the grid should not preceed this operation, because grid initialized by default using the fields from alias in the phisical order of fields (re-binding of columns - see in the previous article). When you have custom controls in grid, it is possible field of incorrect type is automatically used for column with that control and as a result type mismatch error will appear during refresh (Refresh is rare in the Init event of grid, however, in complex class code it might be reached at some moment). Well, the length of property in the property sheet in design time is limited to 255 characters, so this will not work for grids with large number of columns. In such case when you use grid on the form it is possible to do it by code by assigning the long string to property in the Init event of the grid and then calling parent class using DODEFAULT(). You can save space also by not specifying the alias for fields; alias will be assigned automatically by grid. But expressions in ControlSource should contain alias in any case.
Finally, some words are here about the different record source types. When record source is alias, it is ok. However, when you open a table record source that have name started from number, it is opened in another alias. SQL SELECT statement in RecordSource also is a thing that require solution for grid. Fortunately, there is an easy way to determine the correct alias for grid. The alias could be determined from the ControlSource of any column. Grid automatically adds the "{alias}." to any field in column control sources. You can strip out the alias name from the column control sources when they're in simple form (not expression). (How to check if control source is a field reference I already described.) Well, this could not work in case when grid display data from several related by SET RELATION aliases, however, related tables are displayed in grids usually by using the alias record source type. Then, instead of using RecordSource in my programs, I use my custom property in grid (MainAlias) that holds a real name of the alias used by grid. I also define RecordSource_Assign code to catch record source change and update my custom property.
Exact positioning over grid header or cell
The functionality of the control in the grid could be different than functionality of the control outside of the grid. In fact there are certain omissions in VFP related to this that cause certain functionality do not work correctly in the grid. I will not describe here all of these differences, but will describe the good solution for that - put the control outside of the grid and display it over the grid cell for data editing. This way control will edit data as a separate control, not as control in the grid column. Now do this for every column and you're not depended on the grid. However, there are problems related to position the control correctly over the grid cell. This requires calculate the exact position of the cell inside of the grid to cover it by your control. This task is not easy, columns order could be changed, grid could be scrolled, only focused grid allows calculate the row number etc. Here I will describe how to calculate that position in generic way.
Another reason why you might want to calculate the exact position of grid column is sorting mark placing over or above the header to indicate that grid is sorted on the data from that column. For this only column position is required to calculate.
The position should be re-calculated each time grid is scrolled, some column width is changed, some column is moved, record marks or delete marks visibility status changed, header height or row height is changed, grid is split (Partition property of grid). You can catch each of these events:
Following algorithm in the code below determines the right edge of the column specified and is approved that it works. It works by as quick approach as possible and was developed after a lot of experimenting. It does account all settings of grid except the grid that is split. There are comments in this code that describe the algorithm.
The position of the current grid row could be determined correctly only in case when grid is focused. This is because we can use only RelativeRow property of the grid for calculations that is always zero when grid is not focused. This is way more simple and fits into a single expression:
* calculate top position in pixels of the grid row relative to the grid rectangle area * returns zero in case position is outside of the visible grid area Local lnRow m.lnRow = this.RelativeRow m.lnRow = this.RelativeRow if m.lnRow=0 return 0 esle return this.HeaderHeight + this.RowHeight*(this.RelativeRow-1) endif
Below is a picture that displays grid properties and what they rule. Take a look to it to understand better these 2 code samples.
Grid tips
Format property of the grid column accepts 'Z' option to do not display zero values in column when Sparse=.T.
Got tired by 'Memo' displayed in the grid column with memo field? You can display memo field values by using expression like "PADR({MemoField},200)". You can allow edit them using EditBox in the column with column sparse set to .F. EditBox will allow displaying larger text than when using expression in the column ControlSource. Scrollbar in such EditBox in grid will appear only in case when Grid
RowHeight property is larger than required to fit 3 lines of text in the edit box.
When you do not have horizontal scroll bar in grid and all columns fit into visible area of grid, you can get rid of automatic scrolling of grid when last column is focused by setting any column width 1 point less than required to fit all columns.
Grid warnings
Grid refresh cause auto-update when 2-d or 3-d buffering (row buffering) used. This is because internally grid scans records in alias to display data that cause record pointer movement, and thus automatic update. When working with grids, alias should be in table buffering mode.
In certain cases grid can crash or behave in unusual way when ControlSource of column return character values longer than 200 characters and column sparse property is .T. Use expression as ControlSource in such case to display data like "PADR(...,200). For editing in such case use EditBox in the grid column instead of TextBox and set the Sparse property of column to .F. Record just deleted will disappear from grid only after record pointer is moved when SET DELETED is ON. VFP keeps record visible to anything, include grid, after deleting when current record pointer is set to that record.
VFP Grids, Next Parts
The upcoming articles should discuss the events model of grid, what is a cover shape and how to use it, the power of Dynamic* properties of column, sorted grids, sorting marker and more tips and warnings!
