Icon Setting Filters on Tables and Query Result Sets

Introduction
Setting filters on tables and query result sets is accomplished through several properties of the TDBISAMTable and TDBISAMQuery components. These properties include the Filter, FilterOptions, Filtered, and FilterOptimizeLevel properties. The OnFilterRecord event is used to assign a callback filter event handler that can be used to filter records using Delphi or C++ code. All filter operations are completely independent of any active index order.

Setting an Expression Filter
The Filter, FilterOptions, Filtered, and FilterOptimizeLevel properties are used to set an expression filter. The steps to set an expression filter include setting the filter expression using the Filter property, specifying any filter options using the FilterOptions property, and then making the expression filter active by setting the Filtered property to True. You can turn off or disable an expression filter by setting the Filtered property to False. If the current record pointer does not fall into the conditions specified by an expression filter, then the current record pointer will be moved to the nearest record that falls within the filtered set of records. Expression filters may be combined with ranges, master-detail links, and/or callback filters to further filter the records in the table or query result set.

DBISAM's expression filters use the same naming conventions, operators, and functions as its SQL implementation. The only differences are as follows:

DifferenceDescription
Correlation NamesYou cannot use table or column correlation names in filter expressions.
Aggregate functionsYou cannot use any aggregate functions like SUM(), COUNT(), AVG(), etc. in filter expressions.

Please see the Naming Conventions, Operators, and Functions topics in the SQL Reference for more information.

Information Unlike with SQL, you may also use use the asterisk (*) character to specify a partial-length match for string field comparisons in a filter expression. However, this only works when the foNoPartialCompare element is not included in the FilterOptions property.

The following example shows how to set an expression filter where the LastSaleDate field is between January 1, 1998 and December 31, 1998 and the TotalSales field is greater than 10,000 dollars:

begin
   with MyTable do
      begin
      { Set the filter expression  }
      Filter:='(LastSaleDate >= '+QuotedStr('1998-01-01')+') '+
              'and (LastSaleDate <= '+QuotedStr('1998-12-31')+') '+
              'and (TotalSales > 10000)';
      FilterOptions:=[];
      Filtered:=True;
      end;
end;

DBISAM attempts to optimize all expression filters. This means that DBISAM will try to use existing indexes to speed up the filter operation. The FilterOptimizeLevel property indicates what level of optimization was, or will be, achieved for the expression filter and can be examined after the Filtered property is set to True to execute the filter. The following example displays a message dialog indicating the level of optimization achieved for the expression filter:

begin
   with MyTable do
      begin
      { Set the filter expression, in this case for
        a partial-match, case-insensitive filter  }
      Filter:='CustomerName = '+QuotedStr('A*');
      FilterOptions:=[foCaseInsensitive];
      Filtered:=True;
      case FilterOptimizeLevel of
         foNone: ShowMessage('The filter is completely unoptimized');
         foPartial: ShowMessage('The filter is partially optimized');
         foFull: ShowMessage('The filter is completely optimized');
         end;
      end;
end;

Information The foCaseInsensitive filter option can affect the optimization level returned by the FilterOptimizeLevel, so you should make sure to set any filter options before examining the FilterOptimizeLevel property so as to avoid any confusion.

Please see the Filter Optimization topic for more information.

Setting a Callback Filter
The OnFilterRecord event and the Filtered property are used together to set a callback filter. The steps to set a callback filter include assigning an event handler to the OnFilterRecord event and then making the callback filter active by setting the Filtered property to True. You can turn off or disable a callback filter by setting the Filtered property to False. If the current record pointer does not fall into the conditions specified within the callback filter, then the current record pointer will be moved to the nearest record that falls within the filtered set of records.

The following example shows how to write a callback filter event handler where the CustomerName field contains the word "Hardware" (case-sensitive):

procedure TMyForm.TableFilterRecord(DataSet: TDataSet;
   var Accept: Boolean);
begin
   Accept:=False;
   if Pos('Hardware',
          DataSet.FieldByName('CustomerName').AsString) > 0) then
      Accept:=True;
end;

Information Callback filters implemented via the OnFilterRecord event are always completely un-optimized. In order to satisfy the filter requirements, DBISAM must always read every record to determine if the record falls into the desired set of records. You should only use OnFilterRecord on small sets of data, or large sets of data that have been reduced to a small number of records by an existing range and/or expression filter.
Image