This chapter is from the book
This chapter is from the book
This chapter is from the book
Filters and Ranges
Filters and ranges provide a means of limiting the amount of data that is visible in the dataset, similar to a WHERE clause in a SQL statement. The main difference between filters, ranges, and the WHERE clause is that when you apply a filter or a range, it does not physically change which data is contained in the dataset. It only limits the amount of data that you can see at any given time.
Ranges
Ranges are useful when the data that you want to limit yourself to is stored in a consecutive sequence of records. For example, say a dataset contains the data shown in Table 3.4.
Table 3.4 Sample Data for Ranges and Filters
|
ID |
Name |
Birthday |
Salary |
|
4 |
Bill Peterson |
3/28/1957 |
$60,000.00 |
|
2 |
Frank Smith |
8/25/1963 |
$48,000.00 |
|
3 |
Sarah Johnson |
7/5/1968 |
$52,000.00 |
|
1 |
John Doe |
5/15/1970 |
$39,000.00 |
|
5 |
Paula Wallace |
1/15/1971 |
$36,500.00 |
The data in this much-abbreviated table is indexed by birthday. Ranges can only be used when there is an active index on the dataset.
Assume that you want to see all employees who were born between 1960 and 1970. Because the data is indexed by birthday, you could apply a range to the dataset, like this:
ClientDataSet1.SetRange(['1/1/1960'], ['12/31/1970']);
Ranges are inclusive, meaning that the endpoints of the range are included within the range. In the preceding example, employees who were born on either January 1, 1960 or December 31, 1970 are included in the range.
To remove the range, simply call CancelRange, like this:
ClientDataSet1.CancelRange;
Filters
Unlike ranges, filters do not require an index to be set before applying them. Client dataset filters are powerful, offering many SQL-like capabilities, and a few options that are not even supported by SQL. Tables 3.5–3.10 list the various functions and operators available for use in a filter.
Table 3.5 Filter Comparison Operators
|
Function |
Description |
Example |
|
= |
Equality test |
Name = 'John Smith' |
|
<> |
Inequality test |
ID <> 100 |
|
< |
Less than |
Birthday < '1/1/1980' |
|
> |
Greater than |
Birthday > '12/31/1960' |
|
<= |
Less than or equal to |
Salary <= 80000 |
|
>= |
Greater than or equal to |
Salary >= 40000 |
|
BLANK |
Empty string field (not used to test for NULL values) |
Name = BLANK |
|
IS NULL |
Test for NULL value |
Birthday IS NULL |
|
IS NOT NULL |
Test for non-NULL value |
Birthday IS NOT NULL |
Table 3.6 Filter Logical Operators
|
Function |
Example |
|
And |
(Name = 'John Smith') and (Birthday = '5/16/1964') |
|
Or |
(Name = 'John Smith') or (Name = 'Julie Mason') |
|
Not |
Not (Name = 'John Smith') |
Table 3.7 Filter Arithmetic Operators
|
Function |
Description |
Example |
|
+ |
Addition. Can be used with numbers, strings, or dates/times. |
Birthday + 30 < '1/1/1960' Name + 'X' = 'SmithX' Salary + 10000 = 100000 |
|
– |
Subtraction. Can be used with numbers or dates/times. |
Birthday - 30 > '1/1/1960' Salary - 10000 > 40000 |
|
* |
Multiplication. Can be used with numbers only. |
Salary * 0.10 > 5000 |
|
/ |
Division. Can be used with numbers only. |
Salary / 10 > 5000 |
Table 3.8 Filter String Functions
|
Function |
Description |
Example |
|
Upper |
Uppercase |
Upper(Name) = 'JOHN SMITH' |
|
Lower |
Lowercase |
Lower(Name) = 'john smith' |
|
SubString |
Return a portion of a string |
SubString(Name,6) = 'Smith' SubString(Name,1,4) = 'John' |
|
Trim |
Trim leading and trailing characters from a string |
Trim(Name) Trim(Name, '.') |
|
TrimLeft |
Trim leading characters from a string |
TrimLeft(Name) TrimLeft(Name, '.') |
|
TrimRight |
Trim trailing characters from a string |
TrimRight(Name) TrimRight(Name, '.') |
Table 3.9 Filter Date/Time Functions
|
Function |
Description |
Example |
|
Year |
Returns the year portion of a date value. |
Year(Birthday) = 1970 |
|
Month |
Returns the month portion of a date value. |
Month(Birthday) = 1 |
|
Day |
Returns the day portion of a date value. |
Day(Birthday) = 15 |
|
Hour |
Returns the hour portion of a time value in 24-hour format. |
Hour(Appointment) = 18 |
|
Minute |
Returns the minute portion of a time value. |
Minute(Appointment) = 30 |
|
Second |
Returns the second portion of a time value. |
Second(Appointment) = 0 |
|
GetDate |
Returns the current date and time. |
Appointment < GetDate |
|
Date |
Returns the date portion of a date/time value. |
Date(Appointment) |
|
Time |
Returns the time portion of a date/time value. |
Time(Appointment) |
Table 3.10 Other Filter Functions and Operators
|
Function |
Description |
Example |
|
LIKE |
Partial string comparison. |
Name LIKE '%Smith%' |
|
IN |
Tests for multiple values. |
-Year(Birthday) IN (1960, 1970, 1980) |
|
* |
Partial string comparison. |
Name = 'John*' |
To filter a dataset, set its Filter property to the string used for filtering, and then set the Filtered property to True. For example, the following code snippet filters out all employees whose names begin with the letter M.
ClientDataSet1.Filter := 'Name LIKE ' + QuotedStr('M%');
ClientDataSet1.Filtered := True;
To later display only those employees whose names begin with the letter P, simply change the filter, like this:
ClientDataSet1.Filter := 'Name LIKE ' + QuotedStr('P%');
To remove the filter, set the Filtered property to False. You don't have to set the Filter property to an empty string to remove the filter (which means that you can toggle the most recent filter on and off by switching the value of Filtered from True to False).
You can apply more advanced filter criteria by handling the dataset's OnFilterRecord event (instead of setting the Filter property). For example, say that you want to filter out all employees whose last names sound like Smith. This would include Smith, Smythe, and possibly others. Assuming that you have a Soundex function available, you could write a filter method like the following:
procedure TForm1.ClientDataSet1FilterRecord(DataSet: TDataSet;
var Accept: Boolean);
begin
Accept := Soundex(DataSet.FieldByName('LastName').AsString) =
Soundex('Smith');
end;
If you set the Accept parameter to True, the record is included in the filter. If you set Accept to False, the record is hidden.
After you set up an OnFilterRecord event handler, you can simply set TClientDataSet.Filtered to True. You don't need to set the Filter property at all.
The following example demonstrates different filter and range techniques.
Listing 3.4 contains the source code for the main form.
Listing 3.4 RangeFilter—MainForm.pas
unit MainForm;
interface
uses
SysUtils, Classes, QGraphics, QControls, QForms, QDialogs, QStdCtrls,
DB, DBClient, QExtCtrls, QGrids, QDBGrids;
type
TfrmMain = class(TForm)
DataSource1: TDataSource;
pnlClient: TPanel;
pnlBottom: TPanel;
btnFilter: TButton;
btnRange: TButton;
DBGrid1: TDBGrid;
ClientDataSet1: TClientDataSet;
btnClearRange: TButton;
btnClearFilter: TButton;
procedure FormCreate(Sender: TObject);
procedure btnFilterClick(Sender: TObject);
procedure btnRangeClick(Sender: TObject);
procedure btnClearRangeClick(Sender: TObject);
procedure btnClearFilterClick(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
frmMain: TfrmMain;
implementation
uses FilterForm, RangeForm;
{$R *.xfm}
procedure TfrmMain.FormCreate(Sender: TObject);
begin
ClientDataSet1.LoadFromFile('C:\Employee.CDS');
ClientDataSet1.AddIndex('bySalary', 'Salary', []);
ClientDataSet1.IndexName := 'bySalary';
end;
procedure TfrmMain.btnFilterClick(Sender: TObject);
var
frmFilter: TfrmFilter;
begin
frmFilter := TfrmFilter.Create(nil);
try
if frmFilter.ShowModal = mrOk then begin
ClientDataSet1.Filter := frmFilter.Filter;
ClientDataSet1.Filtered := True;
end;
finally
frmFilter.Free;
end;
end;
procedure TfrmMain.btnClearFilterClick(Sender: TObject);
begin
ClientDataSet1.Filtered := False;
end;
procedure TfrmMain.btnRangeClick(Sender: TObject);
var
frmRange: TfrmRange;
begin
frmRange := TfrmRange.Create(nil);
try
if frmRange.ShowModal = mrOk then
ClientDataSet1.SetRange([frmRange.LowValue], [frmRange.HighValue]);
finally
frmRange.Free;
end;
end;
procedure TfrmMain.btnClearRangeClick(Sender: TObject);
begin
ClientDataSet1.CancelRange;
end;
end.
As you can see, the main form loads the employee dataset from a disk, creates an index on the Salary field, and makes the index active. It then enables the user to apply a range, a filter, or both to the dataset.
Listing 3.5 contains the source code for the filter form. The filter form is a simple form that enables the user to select the field on which to filter, and to enter a value on which to filter.
Listing 3.5 RangeFilter—FilterForm.pas
unit FilterForm;
interface
uses
SysUtils, Classes, QGraphics, QControls, QForms, QDialogs, QStdCtrls,
QExtCtrls;
type
TfrmFilter = class(TForm)
pnlClient: TPanel;
pnlBottom: TPanel;
Label1: TLabel;
cbField: TComboBox;
Label2: TLabel;
cbRelationship: TComboBox;
Label3: TLabel;
ecValue: TEdit;
btnOk: TButton;
btnCancel: TButton;
private
function GetFilter: string;
{ Private declarations }
public
{ Public declarations }
property Filter: string read GetFilter;
end;
implementation
{$R *.xfm}
{ TfrmFilter }
function TfrmFilter.GetFilter: string;
begin
Result := Format('%s %s ''%s''',
[cbField.Text, cbRelationship.Text, ecValue.Text]);
end;
end.
The only interesting code in this form is the GetFilter function, which simply bundles the values of the three input controls into a filter string and returns it to the main application.
Listing 3.6 contains the source code for the range form. The range form prompts the user for a lower and an upper salary limit.
Listing 3.6 RangeFilter—RangeForm.pas
unit RangeForm;
interface
uses
SysUtils, Classes, QGraphics, QControls, QForms, QDialogs, QExtCtrls,
QStdCtrls;
type
TfrmRange = class(TForm)
pnlClient: TPanel;
pnlBottom: TPanel;
Label1: TLabel;
Label2: TLabel;
ecLower: TEdit;
ecUpper: TEdit;
btnOk: TButton;
btnCancel: TButton;
procedure btnOkClick(Sender: TObject);
private
function GetHighValue: Double;
function GetLowValue: Double;
{ Private declarations }
public
{ Public declarations }
property LowValue: Double read GetLowValue;
property HighValue: Double read GetHighValue;
end;
implementation
{$R *.xfm}
{ TfrmRange }
function TfrmRange.GetHighValue: Double;
begin
Result := StrToFloat(ecUpper.Text);
end;
function TfrmRange.GetLowValue: Double;
begin
Result := StrToFloat(ecLower.Text);
end;
procedure TfrmRange.btnOkClick(Sender: TObject);
var
LowValue: Double;
HighValue: Double;
begin
try
LowValue := StrToFloat(ecLower.Text);
HighValue := StrToFloat(ecUpper.Text);
if LowValue > HighValue then begin
ModalResult := mrNone;
ShowMessage('The upper salary must be >= the lower salary');
end;
except
ModalResult := mrNone;
ShowMessage('Both values must be a valid number');
end;
end;
end.
Figure 3.8 shows the RangeFilter application in operation.
){kind=link}
Figure 3.8 RangeFilter applies both ranges and filters to a dataset.