Tutorial 10 - Using Filter Conditions

This tutorial describes how to restrict the recordset by imposing a filter condition. Along with range conditions (Tutorial 6) and SQL parameters (Tutorial 7), this is another technique that you can use to implement a master-detail relationship. Filter conditions can also supplant the WHERE clause of an SQL statement, but for performance reasons this is not recommended for database data sources (with DataMode = 0 - DataSource), as filter conditions are tested on every record retrieved.

Start a new project.
Place the following controls on the form (Form1) as shown in the figure: two TData controls (TData1, TData2), a DataGrid control (DataGrid1), a TextBox control (Text1), a label (Label1) and a button (Command1).

Set up TData2 as a memory array data source with one field as follows:

Open the True DataControl property pages for TData2. On the DataSource property page select 1 - MemoryArray in the DataMode combo box. Select the Fields property page. Create a new field by clicking the right mouse button and choosing New Field from the menu. Change the default FIELD_0 name to Interval by typing it in the Name text box. Select 2 - Integer in the Data Type combo box.

Set the properties of the other controls as follows (you can use the DataSource property page to set TData data source properties, as described in Tutorial 2):

Example Title	Copy Code
TData1.ConnectionString Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\Program Files\ComponentOne Studio\Common\TDDEMO.MDB TData1.CommandType 2 – adCmdTable TData1.RecordSource Orders DataGrid1.DataSource TData1 Text1.DataSource TData2 Text1.DataField Interval Label1.Caption RequiredDate – ShippedDate > Command1.Caption Apply Filter

Example Title

Copy Code

TData1.ConnectionString

Provider=Microsoft.Jet.OLEDB.3.51;Persist Security

Info=False;Data Source=C:\Program Files\ComponentOne Studio\Common\TDDEMO.MDB

TData1.CommandType   2 – adCmdTable

TData1.RecordSource  Orders

DataGrid1.DataSource TData1

Text1.DataSource     TData2

Text1.DataField      Interval

Label1.Caption       RequiredDate – ShippedDate >

Command1.Caption     Apply Filter

Open the True DataControl property pages for TData1. On the General property page, select TData2 in the Master combo box and select 0 - None in the AutoRefresh combo box.

Go to the Filters property page of TData1. Create a new filter by right-clicking the filter list on the left side of the page and choosing New from the context menu (alternatively, you can use the New button). This enables the expression editing controls on the right side of the page. Type the following text into the Filter Expression box:

Example Title	Copy Code
RequiredDate - ShippedDate > TData2.Interval

Type the following text into the Condition box:

Example Title	Copy Code
Not IsEmpty(TData2.Interval)

As described in previous tutorials, you can also use the True DataControl Expression Editor to build the expression.

You have created a filter condition that reads as:

Example Title	Copy Code
(if (Not IsEmpty(TData2.Interval)) RequiredDate - ShippedDate > TData2.Interval

This means that:

The condition will be applied only if TData2.Interval is not empty.
In this case, TData1 will only include records where RequiredDate - ShippedDate > TData2.Interval.

More than one filter condition may be imposed. Usually, each filter is accompanied by a condition specifying its applicability, as in this example.

Go to the General property page and check the Filtered check box.

IMPORTANT! If you omit this step, all filters will be ignored!

Add the following code to Command1:

Example Title	Copy Code
Private Sub Command1_Click() TData1.Refresh End Sub

The Refresh method and its connection to the AutoRefresh property were discussed in Tutorial 8.

Run the Program and Observe the Following:

Enter a number (for example, 20) into the edit control at the top of the form, then click on the Apply Filter button. The grid includes only the rows that satisfy the condition. Enter different numbers (for example, 1) into the edit control and see how they affect the record display.
Clear the edits control and click on Apply Filter button. The grid now shows all orders because the filter was specified as applicable only if TData2.Interval is not empty.

Close the program. You have successfully completed Tutorial 10.