EhLib
ContentsIndexHome
PreviousUpNext
Using DBGridEh for sorting and filtering data in the DataSet

DBGridEh can not sort or filter data by itself. But it can pass action for sorting or filtering to the specal object that will do it in DataSet. You can adjust grid for two types of sorting (local or server), using property SortLocal and two type of filtering (also, local and server), but real possibility to apply each type of operation depends on the type of DataSet. For instance, TBDEDataSet does not suppot sorting locally (inside DataSet). so you can not sort data locally when grid is linked to TQuery or TTable. Furthermore, EhLib has only special objects for standard types of DataSet (not for a third party DataSet's). 

Below you can see a table of special object and DataSet in which those objects can sort or filter data.

Unit 
DataSet 
Local sorting 
Server sorting 
Local filtering 
Server filtering 
TQuery,
TTable 
TADOQuery,
TADODataSet 
TClientDataSet 
TSQLQuery,
TSQLDataSet 
TIBQuery,
TIBDataSet 
TMemTableEh 
Y* 
Y* 

* If Grid is configured for Server sorting of Filtering (SortLocal=False, STFilter.Local=False), then sort operation is transferred to the TMemTableEh.DataDriver object. 

When grid is adjusted for a local filtering, the special object will build expression and assign it to a property Filter of DataSet. So we need also to set Filtered to True value manually. 

When grid is adjusted for a server filtering, the special object will build SQL:ORDER BY expression and it will replace ORDER BY string in SQL or Command property of DataSet and it will reopen DataSet. 

When grid is adjusted for a local sorting, the special object will perform a sorting using specific type of DataSet. 

When the grid is adjusted for a server sorting, the special object will build expression for SQL 'WHERE' clause in SQL string of dataset. It will try to find string that begins from key string '/*Filter*/' in SQL expression and adds filter expression after this string in same line (You can change this key string using global variable - SQLFilterMarker) . So your SQL query must have a line that begins from '/*Filter*/'. For instance: 

' select * 

from table1 

where 

/*Filter*/ 1=1 ' 

For automatically filtering/sorting data in dataset you need to add one of the units EhLibXXX (EhLibADO, EhLibBDE, EhLibCDS ... depend on the type of dataset where the grid is linked to) to 'uses' clause of any unit of your project. This units have code to register TDatasetFeaturesEh based classes (special object) for respective DataSet's. For other third party DataSet's you have to write and register your own special object or you have to write OnApplyFilter or/and OnSortMarkingChanged events for every required grid. Moreover, you can write OnApplySorting and/or OnApplyFilter for the global variable DBGridEhDataService. These events have TNotifyEvent type where Sender is TCustomDBGridEh. 

Adjusting grid for sorting data: 

TDBGridEh allows showing special sortmarking bitmaps (small triangles) in the right part of title cell. In order to automatically mark the title by sortmarking bitmaps you have to add dghAutoSortMarking to OptionsEh property. Add dghMultiSortMarking to OptionsEh in order to allow sortmarking in several columns simultaneously. Set Column.Title.TitleButton to true value for titles which will have possibility to change sortmarkers at run time. Or you can set ColumnDefValues.Title.TitleButton to allow setting sortmarking for all columns where Column.Title.TitleButton is not changed. At runtime, clicking on title will change sortmarking. Holding Ctrl key allows to mark several columns simultaneously. After user changes sormarking grid will call OnSortMarkingChanged event or will pass action to special object, if it is registered. Special object will use Grid.SortLocal property to determine how to sort data: locally or on the server. If you write OnSortMarkingChanged, you can use TDBGrid.SortMarkedColumns property to access the sortmarked columns and TColumnEh.Title.SortMarker property to get state of the sortmarker. 

Adjusting grid for filtering data: 

TDBGridEh allows to show special row (under title) where user enters expressions in cells for filtering records in dataset.

(Expression in the cell can look like: "1", ">=1", ">1 AND <=5", "%TestStr%"). Set STFilter.Visible to True value to show this special row. At run time, when cursor locate in the filter cell, they can press ENTER to apply filter. Grid will parse expression in every not empty filter cell and call TDBGridEh.ApplyFilter. And it calls OnApplyFilter event or passes action to special object, if it is registered. 

For normally parsing, the expression in cell have to have next format: 

[Operator1] Operand1 [)] [AND|OR Operator2 Operand2 [)] ] 

Where OperatorX is one of [ = | <> | != | > | < | >= | <= | ~ | !~ | in ( ] 

OperandX is a numeric, date or string value or for the operator 'IN' it is a list of values divided by comma. 

For instance: 

'1' 

'>=1' 

'>1 AND <=5' 

'~ TestStr%' 

'!~ TestStr_' 

'in (1,2,3,4,5,6) and >100' 

'in (Str1,Str2,Str3)' 

Grid allows to show dropped down list of STFilter in title cells. Set STFilter.Location to stflInTitleFilterEh to active this mode. Use item ‘(Custom…)’ to enter string for filtering or press checkboxes above required values. Global variable DBGridEhShowInTitleFilterBox contain key combination for opening list of filtering values. By default it contains 'Ctrl-T'.

 
 

When grid is connected to the list of uncial values of column is formed automatically. In addition, list is formed with tacking in account of filter applied in other columns. To form list of values and command for filtering, use next events and methods: Events - TDBGridEh.OnFillSTFilterListValues, TColumnEh.OnFillSTFilterListValues, TDBGridEhCenter.OnFillSTFilterListValues or rewrite method TDatasetFeaturesEh.FillSTFilterListDataValues and register new class MyDatasetFeaturesEh for interaction between grid and dataset. 

Grid call event OnFillSTFilterListValues every time when it is needed to fill list of STFilter, that have next type:

TDBGridEhFillSTFilterListValuesEvent = procedure(Sender: TCustomDBGridEh; Column: TColumnEh; Items: TStrings; var Processed: Boolean) of object;

This event presents in TDBGridEh, TColumnEh, TDBGridEhCenter. 

Every item of Items list must consists of two values: String - Items[i] and Object Items.Objects[i].Items of object type must be inherited from TPopupListboxItemEh type and they must be declared as global variables. There are already next global variables in DBGridEh:

Variable name 
Variable type 
Description 
PopupListboxItemEhSortAsc 
PopupListboxItemEhSortAsc 
Item of the list show sorting item Asc and executes sorting in ascending order. 
PopupListboxItemEhSortDes 
TPopupListboxItemEhSort 
Item of the list show sorting item Desc and executes sorting in descending order. 
PopupListboxItemEhLine 
TPopupListboxItemEhLine 
Item of the list show horizontal line. On choosing doesn't perform any actions. 
PopupListboxItemEhClearFilter 
TPopupListboxItemEhSpec 
 
PopupListboxItemEhAll 
TPopupListboxItemEhSpec 
 
PopupListboxItemEhEmpties 
TPopupListboxItemEhSpec 
 
PopupListboxItemEhNotEmpties 
TPopupListboxItemEhSpec 
 
PopupListboxItemEhDialog 
TPopupListboxItemEhSpec 
 
PopupListboxItemEhApplyFilter 
TPopupListboxItemEhSpec 
 
PopupListboxItemEhData 
TPopupListboxItemEhData 
Item of the list shows text that defines text of filtering. When it is selected, it is checked by checkbox and it is put in the list of items of values that is needed be in the filtered records. 

You can call next methods in the event to fill values by default

TDBGridEh.DefaultFillSTFilterListValues(Column: TColumnEh; Items: TStrings)
TDBGridEh.DefaultFillSTFilterListCommandValues(Column: TColumnEh; Items: TStrings)
TDBGridEh.DefaultFillSTFilterListDataValues (Column: TColumnEh; Items: TStrings)

 

Next code show OnSTFilterListValues event where sorting items are deleted from list:

procedure TForm1.DBGridEh1FillSTFilterListValues(Sender: TCustomDBGridEh;
  Column: TColumnEh; Items: TStrings; var Processed: Boolean);
var
  Index: Integer;
begin
  Sender.DefaultFillSTFilterListValues(Column, Items);
  Index := Items.IndexOfObject(PopupListboxItemEhSortAsc);
  if Index >= 0 then
    Items.Delete(Index);
  Index := Items.IndexOfObject(PopupListboxItemEhSortDes);
  if Index >= 0 then
    Items.Delete(Index);
  Processed := True;
end;

Now library can fill list of unical values only for DataSet of TMemTableEh type. To fill values when grid is connected to other type of DataSet, it is required rewrite TDatasetFeaturesEh.FillSTFilterListDataValues method and , if it is need, it is required to register class that inherited from TDatasetFeaturesEh. Read information about TDatasetFeaturesEh class in EhLib users guide and EhLibMTE.Pas. 

New properties STFilterDefaultStringOperator, STFilterDefaultNumberOperator, STFilterDefaultDateTimeOperator, STFilterDefaultBooleanOperator are added in TDBGridEhCenter class. Use this properties to set default operator that will be used when string in the STFilter doesn't contain operator. The operators can be assigned apart for String fields, Numeric fields, DataTime fields and logical fields.

Copyright (c) 1998-2013. All rights reserved.
What do you think about this topic? Send feedback!