Delphi Programming

  1. Home
  2. Computing & Technology
  3. Delphi Programming
Data filtering
Chapter eight of the free Delphi Database Course for beginners. Using Filters to narrow the scope of data that is presented to the user.
 More of this Feature
Printer friendly versionPrinter friendly version
 Join the Discussion
"Post your views and comments to this chapter of the free Delphi database Programming Course"
Discuss!
 Related Resources
• free DB Course.TOC
• Incremental searching
• Coloring DBGrid
• more Database articles

As stated in one of the previous chapters, both TADOQuery and TADODatSet (as dataset components) share the same set of common methods and events. On of the features exposed by those datasets is the ability to narrow the scope of data that is presented to the user.

Consider that you might have a database table with thousands of records, but your users are interested in seeing or working on only a small subset of the table data.

To follow the article, set up the data form with the core components (data-access and data-aware) as described in the previous chapters. The next code examples will assume that you are working with the ADOTable component pointing to the Applications table in our working Access database.

   Filtering
Filtering is the method by which some data from the dataset is excluded from view by displaying only those records that meet specific criteria. Filtering permits you to present varying views of the data stored in a dataset without actually affecting that data. This criteria is set through the Filter property of the dataset component (TADOTable or TADOQuery), it can be set at both design and run time. Filter property represents a string that defines the filter criteria.
For example, if you want to limit the displayed data (from the Applications table) to freeware applications (cost $0.00), a filter such as the following will only display records that meet the condition: 

  ADOTable1.Filter := 'Cost = 0';

You can also add a value for Filter based on the text entered in a control. If the filtered dataset should only display free applications and you want to enable users to supply the type of the applications, a filter could be set as follows: 

 ADOTable1.Filter :=
  'Cost = 0 AND
   Type = ' + QuotedStr(Edit1.Text);

By using combinations of the following operators, you can create quite sophisticated filters.

Operator Meaning
< Less than
> Greater than
>= Greater than or equal to
<= Less than or equal to
= Equal to
<> Not equal to
AND Tests two statements are both True
NOT Tests that the following statement is not True
OR Tests that at least one of two statements is True

Filtered, FilterOptions, FilterGroup, OnFilterRecord
The Filtered property is a Boolean value (True or False) that determines if the string in the Filter property is used to filter the dataset. When Filtered is False, the filtering is ignored and the complete dataset is available to the application.

The FilterOptions is a set of two values - both used when filtering string fields. If the foCaseInsensitive is included in the FilterOptions, comparison between the literal in the Filter property string and the field values are case-insensitive. The foNoPartialCompare forces Delphi to treat the asterisks (*) as a literal character rather than as wildcard. By default, FilterOptions is set to an empty set.

The OnFilterRecord event fires each time the filtering is enabled and a new record becomes the current one. You will generally use this event to filter records using a criterion that can't be (easily) implemented using the Filter property. 

procedure TForm1.ADOTable1FilterRecord
  (DataSet: TDataSet; var Accept: Boolean);
var AppZipSize : Single;
begin
  AppZipSize := ADOTable1.FieldByName('size').Value;
  Accept := (AppZipSize < 10);
end;

The key element here is the Accept parameter. You set the Accept parameter to True for any rows that you want to show. The preceding code sets Accept to True for any rows in which the Size field contains a value that is less than 10 (all apps whose download size is less than 10 Kb).

The FilterGroup set property allows you to filter records depending on their status.

   To filter or not to filter
Note that

  • the Filter property behaves much like a WHERE clause in a SQL statement.
  • you can have multiple conditions, specified in the Filter property, separated by AND and OR operators.
  • generally one should avoid Filters unless the fetched recordset is small. A filter is done on the fly, and may or may not use the current index (filters are applied to every record retrieved in a dataset).
  • filters are rarely used with client/server databases, a SQL query (TADOQuery) should be used to achieve the same effect that filters have on local databases.
  • you should generally not use filtering with datasets on data modules. In a specific situation when filtering a table that is never viewed from any other form, or a table that makes use of a range, or sort order that is not used anywhere else in the application - data modules *should* be avoided.
  • to search a filtered dataset, you can use the FindFirst, FindNext, FindPrior, and FindLast methods. These methods are the best way to search a filtered dataset because the filter is reapplied each time one of these methods is called. Therefore, if records that previously did not match the filter have been modified so that they now match the filter, they will be included in the dataset before the search is performed.

       To the next chapter
    If you need any kind of help so far, please post to the Delphi Programming Forum where all the questions are answered and beginners are treated as experts.

    DB Course Next Chapter >>
    >> Chapter 9: Searching for data

    • Explore Delphi Programming

    About.com Special Features

    Browse All About.com

    Delphi Programming

    1. Home
    2. Computing & Technology
    3. Delphi Programming

    ©2009 About.com, a part of The New York Times Company.

    All rights reserved.