Grid sorting by header click
Last time, sorting of information by a click on the header is popular in the lists and grids. This feature is very useful to search something among the hundreds of rows in the list. VFP grid does not have such feature built-in, however, as usual, it could be programmed.
There are a lot of approaches exists to organize this. We will not cover all of them, but just will describe the most generic approach that is useful to build a grid class with sorting capabilities. This means described approach could be used in any other case by cutting off too complex and useless parts from it. It is recommended to make this feature as a part of your grid class in the application so you will be able to re-use it in many other applications.
First of all, prepare a Click event of the header to fire sorting at the correct moment. Click event also fired when column moved or resized, so you will need to use something similar to what was described in the VFP Grids Part II article in previous release of UT magazine - make header class that will track resizing and moving of the column to separate just a pure click on the header. Of course, this will require all other things in the grid to support and maintain the replacing of default grid headers by new header class. This is needed anyway, if you want to use sorting feature for any grid. When column is resizable, click on the resizing area without resizing of column should be caught too - it is not good idea to sort column when mouse cursor is the resizing arrows, this will confuse users (as well as we hold this for another functionality described in the chapter 3. of this article). The resizing area of grid header is 11 pixels wide (6 pixels to left from the line between headers and 4 pixels to right from the header right edge). When you click on the resizing area without resizing, even when mouse is over the next header, mouse and click events are fired only for header that resizable by that resizing area. In the sample you can see how it is tracked in the header class - by MouseUp event we get the coordinates of the mouse pointer during and check if it was just a single click on the resizing area. If yes, set special flag so in the Click and DblClick events we can check if mouse is not in the resizing area.
Once pure click separated from all other actions, do the sorting. It consists of three parts - sorting manager, sorting routine and refreshing of grid after sorting.
Sorting manager tracks which column is currently sorted, define properties for sorting and holds a current state of the sorting. To do this, following properties are used:
In the most complex case columns could be removed from the grid, so SortedColumn property might require updating after such process. For such case, it is recommended to have a method in the grid to scan all columns and update the SortedColumn value properly by index of the column, which contains non-zero sorting state. Well, you can organize another way to track the index of column that is currently sorted - by using a simple loop that returns index of currently sorted column.
Sorting routine is the main part of this feature. There are several ways to organize sorted data in the grid: - Store data in the array and show array content in the grid, sort array using asort() function. This way somewhat slower and number of records is limited to maximum number of array elements. - Re-query the view or SQL Pass-Through result set with another option for sorting. Slow for large data sets, however, the most easily to organize sorting by multiple columns. - Use indexes for the result set created on the fly or use existing indexes. This approach is the most quick because it does not store any intermediate result in the memory and does not require query of data from server again, but it require some programming.
In this article we will discuss only approach with use of indexes. Other approaches could be adapted hereby easy way by replacing the way data set is sorted in the sorting routine, and the way grid is refreshed after sorting.
In the sorting routine following properties are used:
Last two properties give more options for programming of sorting. For example, when grid contain 2 columns First Name and Last Name, it is good idea to sort data by First Name + Last Name no matter which of these two columns are clicked. Of course, it might require additional sorting indicator and programming, so better leave this property for case when default sorting routine is not capable to sort column properly. We will discuss multiple columns sorting later.
Sorting routine check if SortignTag or CurrentTag properties are not empty, and use these tags for sorting. CurrentTag is not cleared till the end of the header object life, or till the special situation occurred that require deleting of this temporary tag (this is done by setting the property lEventSwitchOffSorting to .T.). This is some sort of reusability - we create index only once (this can take some time), store tag name of created index in this property, and then just re-use it to do not spend time for indexing next time. When CurrentTag property is empty, routine will create an index tag for the record source and store tag name in this property.
When expression for indexing is specified, routine will just use it without any verification (though basic error-tracking could be done anyway). Otherwise sorting routine will attempt to build the expression with checking of all circumstances, include NULL values and SET COLLATE settings. Remember that maximum index expression length is 240, but it is dropped to 120 with SET COLLATE setting is set to something other than "MACHINE". Index does not accepts NULL values, so we add NVL() function to assure no null values appear in the result set. For long character and memo fields we use PADR() function. Finally, when ControlSource value contains expression, not the field, we use PADR() for character values anyway to assure length of the expression result is always the same. Of course, we cannot sort by fields of General type. There is also a big difference for indexing for different record sources. For views, cursors and SQL Pass-Through cursors we can create structural index tag without any problem, because the file with these tags will be deleted from the disk automatically as soon as record source is closed. However, view with table buffering could not be indexed by INDEX command. We can quickly switch to the row buffering temporarily, index the view, then set buffering to table buffering again. However, changing of the buffering mode cause error if view contains uncommitted changes. Well, you can tell user that grid on that form could not be sorted when data changed, or just prepare indexes just after view opening and specify prepared index tags in the SortingTag property of each header, then set buffering mode to table buffering.
It is bad idea to create structural index tag for a table, because expression in ControlSource could contain reference to other fields and even to related tables, so other user that will open that table after our indexing could have errors because that. In addition, creating of structural index require exclusive access rights to that table. For such case we use non-structural index tags stored in the temporary CDX file with the same name as a tag name. This creates some other potential problems. "BEGIN TRANSACTION" command cannot start transaction in the data session that contains alias with opened non-structural index. Before this command you require to close all non-structural indexes. Really, it is better to do not show user entire data set with tens of thousands of records. Prepare a filter condition and select into the grid only little subsets of the data from large table. This will work much more quickly, and no problems with direct access to table, like problem with non-structural index tags in this case. In addition, indexing of the large table accessed through network is quite slow. Anyway, in case you require sorting for table, try to use for sorting as many existing index tags in the table as possible, and then switch off sorting for columns that have no existing index tag in the table. Another solution - before transaction starting, use Grid.SetAll("lEventSwitchOffSorting",.T.) so grid will delete non-structural index tags if any. This will require special attention for transactions in the child forms called from the form with such grid.
When record source contains more than 1000 records, we display the message using the DispSortingMessage method of the header, that by default show a simple "WAIT WINDOW" message for duration of the sorting. You might want to prepare the progress bar to display a progress of indexing or some other way to indicate the indexing (sorting) in progress. In addition, it is possible to display sorting progress by using custom function call inside of the index expression: function will be called for each indexed record, code in function update graphical progress bar (speed of indexing will be worse in such case).
Speed of sorting is amazing when sorting this way view, cursor or SQL Pass-Through cursor. Sorting of the table is quick when table is on the local disk, but usually it is slow because access to database and its tables through network. The result cursor of the VFP SELECT statement could be sorted too. However, use NOFILTER option for result set, otherwise indexing might be slow because direct access to the table through network (as well as use of non-structural indexes is not a good idea, as already described).
After sorting, we require to refresh grid properly. There are situations when changing of sorting direction can cause strange effects with grid rows appearance. For example, sort grid in ascending order, put record at first record in the current sorting order, and then sort again in descending order. Usually grid after this show only single row - the last row in descending sorting order, and empty space below it. User anytime can use scrollbar to return grid to good-looking state, however, the strangest thing is that grid often show this behavior when all rows in the record source fit into the visible area of grid. Imagine situation when you see all rows from data source in grid, click sorting, and then you see only one row... This usually confuse user - why grid is scrolled when all records could be visible at once? To fix this behavior of grid, we use one extra refreshing of grid by setting record to the first record in current sorting order, then set it back to the current record. This assures record is centered in the current visible area of grid or all rows are shown in grid when all rows fit into the visible area. This also could be improved even more - when record at the end of the record source in new sorting order, show last records by such way that there are as little space left without records at bottom of grid as possible. To do this, in case record is at bottom of grid, move record pointer to top, move it back to current record minus half of the number of records that fit in current visible area of grid, then set record pointer back to the required record. Note that this is useful only when record pointer is currently in the few last records. Moving record pointer back and forth could be slow for certain record sources, specially with large number of record or filter set for record source, so this is not a good idea for a generic case.
Above approach is good for sorting only by one column. In case you want to organize multi-column sorting, you will require collecting all sorting expressions from columns that are currently joined to sorting by multiple columns, and that have sorting state not zero. This is required to do even in case user switch off sorting for this particular column to keep other columns sorted correctly. Re-use of existing index tag is very hard to organize in such case, because single column use several sorting expressions now. Usually this requires converting of all expressions to the character type and cut them by limitation of 240 characters (or even 120 character when collating sequence is used). In addition, it is hard to organize sorting expression by such way that one column is sorted in ascending order and another - in descending. It requires to inverse the value of expression for the column with descending order. Inversing is different for different data types. For example, numeric values are easy to invert just by using the "-" sign operator. In our case we will work only with character values. In such case we need to change "A" to "z" and vise versa. You can use chrtran() function to do this quickly with use of 2 prepared strings. For example:
"Control" > "Binding" chrtran("Control", cAllChars, cAllReverseChars) < chrtran("Binding", cAllChars, cAllReverseChars) cAllChars and cAllReverseChars are prepared by following way: cAllChars = "" cAllReverseChars = "" for nCharIndex = 0 to 255 cAllChars = cAllChars + chr(nCharIndex) cAllReverseChars =cAllReverseChars + chr(255-nCharIndex) endfor
NVL(LastName,space(35)) + chrtran(NVL(FirstName,space(35)), cAllChars, cAllReverseChars)
There are few different models of sorting by multiple columns.
Multi-column sorting also requires special approach for displaying sorting indicators. In such case this require to maintain as many controls as could be displayed at once to indicate sorting for all colum,nd currently sorted. For dynamic sorting the number of indicators will match to the number of the columns in grid that could be sorted.
In the sample there is no such complex thing as multiple-column sorting, however, you can see most of the things discussed here for sorting.
Grid sorting marker (indicator)
There are many ways to show user that certain column of the grid is sorted. The simplest approach is to change the background or foreground color of the header caption, or add a special arrow-like character to the header caption to indicate sorting (usually characters "^" and "v". This does look acceptable but not very good. It is possible to use a separate control to indicate sorting, but this is not an easy task. Well, putting the arrow-like control above the grid just above the header caption is as easy task as putting of the control over the grid cell - all rules apply here too (see VFP Grids Part II article in previous Universal Thread Magazine). However, in many applications you can see a little arrow inside of the header. In VFP it is hard to organize such thing and this require some workarounds. There are two approaches.
First is to use a separate window defined by the command "DEFINE WINDOW ... IN WINDOW ... NAME ..." and changing that window shape by using Windows API function SetWindowRgn() to make it look like an arrow, and even show something under arrow imitating the raise or sunken effect using a set of colored lines. The second approach is putting a usual transparent contianer control over the grid and using a set of lines to organize arrow. Instead of the control with lines simple drawing on the form could be used to draw the lines over the header using form methods for graphics, but this require even more effort for refreshing such sorting indication.
The problem with putting something over the grid header is very frustrating - grid header redraw self always above any other VFP control despite you just used ZOrder method to put your control to top to cover all other controls. There are reports that this behavior does not appear when running VFP application on W2K system; under Windows NT it always appear. Grid header will draw self over other controls as soon as certain events occur that cause header refresh. Following is a list of such events and events that require to refresh the sorting indicator:
Well, all these things, as you can see, could be caught by a simple way except the last one. Losing of focus for grid do not cause any grid event firing after grid headers refreshed. This could be caught by putting grid inside of the container, that is not good for certain cases, specially for generic grid class.
Windowed indicator is good unless it cause no conflicts for modal windows. In some cases another window defined by "DEFINE WINDOW ... IN WINDOW" command inside of the modal VFP form can cause a lot of side effects. Specially frustrating that such window does not fire any events and does not function properly by mouse clicks (it would be good to forward mouse clicks from sorting indicator to the grid header). For modeless forms such window gets focus, that also bad thing because a lot of events firing and this require special workarounds. To refresh sorting indicator from several places in code, separate grid class method is used. Refreshing calculates the correct position of indicator and places it. It contains method to refresh the indicator - to indicate different sorting directions, and set colors for arrow properly to the background color of the header. The coloring algorithm uses a color brightness distribution percentage to organize shadowed lines to simulate the raise effect. It also assure that arrow is visible anyway on the very bright or dark header background.
It is good idea to use the indicator control only in the run-time. In the Grid it is created in the Init event and destroyed it in the Destroy event of the grid. For multi-column sorting you probably will require an array, which contains references to all indicator controls created for sorted columns, or even create indicator for each column by storing reference to it in the header.
Resizing of grid column by double click to the width of data
You can see a useful feature in the applications with grids or lists - double click on the resizing area of the column header resize column automatically to the width of the information in that column. In the sample you can see in the header DblClick and MouseUp events how we separate double click on the header resizing area from all other events. This fires the auto-resizing algorithm. It gets the field size from the control source, and calculate the width of the column in pixels using TxtWidth() function for character 'O' (a character with average width) replicated to number of that field size in characters. Certain fields like memo field and fields with long space for characters usually contain a lot of extra space that is not used completely. In addition, expression could be used in the COntrolSource of the column that gives no idea about maximum number of characters displayed. It is good idea to do not expand column to the maximum size, just show the information by as good as possible way - to fit most rows information and do not take much space by the column. For such case algorithm scans records around current record position and calculate maximum value of the TxtWidth() function return for result of the expression. That will become a width of the column. However, if all rows around current record contain empty value, it is good idea to specify the default column width, for example, in the DefaultWidth property of header (that could be used also in some other cases).
Grid tips
You can show anything in the grid cell by using the container control. To refresh container accordingly to each row data, in the expression in the Dynamic* property you can use function call. Function will be called for each grid row, so in code inside this function you will be able to change anything in container and refresh it for that row. During such function call record pointer in the record source is at the appropriate record.
To make header caption multi-line, in VFP7 use WordWrap property of the header. In VFP6 multi-line headers could be made using either labels over header, or container controls that simulate grid headers (there are downloads for this in the Universal thread Downloads section).
To show tool tips for different parts of the grid, show your own control in the timer event that checks if mouse is not moved for certain time using MROW() and MCOL() function. The part of the grid could be determined using GridHitTest method of the grid. (Multiline/Fancy ToolTip Control working that way, you can modify it for this purpose; it is located in the Downloads section at the Universal thread site.)
Grid warnings
Do not use grid column control's "Value" property for calculations. To get data for calculations, do direct access to the grid alias data instead. This is because control in the inactive column is usually used for refreshing of the grid layout, and thus its Value property is not appropriate to the current row value.
Headers always cover VFP controls on form no matter how they arranged or located. Use separate window control that has also some disadvantages.
Vertical Scroll Bar position calculated using RecCount() and current RecNo(), not the really displayed records count. When record source is filtered or contains a lot of records marked for deletion, scroll bar often confuse users by inappropriate positioning. In such case use of the query or view is recommended.
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, saving and restoring of the grid layout, dynamic columns removing/adding, and more tips and warnings!
Download the samples for this article
