Creating Filters

Previous  Top  Next

There are two ways to create a filter for an active dataset:

·	Set the Filter property. Filter is especially useful for creating and applying filters at runtime.

·

Write an OnFilterRecord event handler for simple or complex filter conditions. With OnFilterRecord, you specify filter conditions at design time. Unlike the Filter property, which is restricted to a single string containing filter logic, an OnFilterRecord event can take advantage of branching and looping logic to create complex, multi-level filter conditions.

The main advantage to creating filters using the Filter property is that your application can create, change, and apply filters dynamically, (for example, in response to user input). Its main disadvantages are that filter conditions must be expressible in a single text string, cannot make use of branching and looping constructs, and cannot test or compare its values against values not already in the dataset.

The strengths of the OnFilterRecord event are that a filter can be complex and variable, can be based on multiple lines of code that use branching and looping constructs, and can test dataset values against values outside the dataset, such as the text in an edit box. The main weakness of using OnFilterRecord is that you set the filter at design time and it cannot be modified in response to user input. (You can, however, create several filter handlers and switch among them in response to general application conditions.)


Setting the Filter property

To create a filter using the Filter property, set the value of the property to a string that contains the filter conditions. The string contains the filter's test condition. For example, the following statement creates a filter that tests a table's State field to see if it contains a value for the state of California:

ABSTable1.Filter := 'State = ' + QuotedStr('CA');

you can also supply a value for Filter based on the text entered in a control. For example, the following statement assigns the text from an edit box to Filter:

ABSTable1.Filter := Edit1.Text;

you can also create a condition for boolean fields:

ABSTable1.Filter := 'Married = TRUE';

you can also create a string based both on hard-coded text and on data entered by a user in a control:

ABSTable1.Filter := 'State = ' + QuotedStr(Edit1.Text);
you can also compare field values to literals, and to constants using the following logical and comparison operators:

Operator	Meaning
<	Less than
>	Greater than
>=	Greater than or equal to
<=	Less than or equal to
=	Equal to
<>	Not equal to
AND	Tested statements are both True
NOT	Tested statement is not True
OR	At least one of two statements tested is True
[NOT] LIKE	TRUE if the operand matches a pattern with '%', '_' wildcards.
IS [NOT] NULL	TRUE if Boolean expression is UNKNOWN
IN	TRUE if the operand is equal to one of a list of expressions.
BETWEEN	TRUE if the operand is within a range.

Using combinations of the above listed operators you can create fairly sophisticated filters. For example, the following statement checks if the two test conditions meet when searching for a record:

(LastName LIKE 'Sm%') AND (Salary > 1500)


Setting filter options

The FilterOptions property enables you to specify whether or not a filter that compares string-based fields accepts records based on partial comparisons and whether or not string comparisons are case-sensitive. FilterOptions is a set property that can be an empty set (the default), or that can contain either or both of the following values:

Value	Meaning
foCaseInsensitive	Ignore case when comparing strings.
foPartialCompare	Disable partial string matching (i.e., do not match strings ending with an asterisk (*)).

For example, the following statements set up a filter that ignores case when comparing values in the State field:

FilterOptions := [foCaseInsensitive];

Filter := 'State = ' + QuotedStr('CA');

Enabling filter

Set the Filtered property to True.
When filtering is enabled, only those records that meet the filter criteria are available to an application. Filtering is always a temporary condition. You can turn off filtering by setting the Filtered property to False.