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:


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


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;

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;