Home > Articles > Data

  • Print
  • + Share This
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.

Figure 3.8 RangeFilter applies both ranges and filters to a dataset.

  • + Share This
  • 🔖 Save To Your Account