Setting filters on tables, views, and query result sets is accomplished through several properties of the TEDBTable, TEDBQuery, TEDBScript, and TEDBStoredProc components. These properties include the Filter, FilterOptions, and Filtered properties. The OnFilterRow event is used to assign a code-based filter event handler that can be used to filter rows using Delphi, C++Builder, or Lazarus 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 row pointer does not fall into the conditions specified by an expression filter, then the current row pointer will be moved to the nearest row that falls within the filtered set of rows. Expression filters may be combined with ranges, master-detail links, and/or code-based filters to further filter the rows in the table or query result set.

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


Please see the Identifiers, Types and Operators, Numeric Functions, String Functions, Date/Time Functions, Interval Functions, and Conversion Functions topics for more information.

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

{
   // Set the filter expression
   MyTable->Filter="(LastSaleDate >= DATE "+Engine->QuotedSQLStr("1998-01-01")+") "+
              "and (LastSaleDate <= DATE "+Engine->QuotedSQLStr("1998-12-31")+") "+
              "and (TotalSales > 10000)';
   TFilterOptions FilterOptions;
   FilterOptions->Clear();
   MyTable->FilterOptions=FilterOptions;
   MyTable->Filtered=true;
}

ElevateDB attempts to optimize all expression filters, and the filter optimization process is the same as that used for optimizing SQL WHERE conditions. Please see the Optimizer topic for more information.

Setting a Code-Based Filter

---

The OnFilterRow event and the Filtered property are used together to set a code-based filter. The steps to set a code-based filter include assigning an event handler to the OnFilterRow event and then making the code-based filter active by setting the Filtered property to True. You can turn off or disable a code-based filter by setting the Filtered property to False. If the current row pointer does not fall into the conditions specified within the code-based filter, then the current row pointer will be moved to the nearest row that falls within the filtered set of rows.

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

void __fastcall TMyForm::TableFilterRow(TDataSet *DataSet,
   bool &Accept);
{
   Accept=false;
   if (Pos("Hardware",
         DataSet->ColumnByName("CustomerName")->AsString) > 0))
      {
      Accept=true;
      }
}

Code-based filters implemented via an OnFilterRow event handler are always completely un-optimized. However, ElevateDB only incrementally calls the OnFilterRow event handler for the row or rows necessary for any data-aware controls or for positioning on a desired row (if data-aware controls are not being used). For example, if you positioned a table with an active code-based filter on a new row using the Locate method, then ElevateDB will call the OnFilterRow event handler for the current row and any subsequent rows using the active index order until it has found a row that satisfies the event handler (Accept=True). ElevateDB then stops and does not attempt to filter any further rows. The OnFilterRow event handler can, therefore, be used to filter large numbers of rows incrementally without a large amount of overhead.