Filtering Records (FireDAC)

From RAD Studio
Jump to: navigation, search

Go Up to Working with DataSets (FireDAC)


All the FireDAC datasets provide approaches to locally filter the records. After applying a filter, FireDAC does not require records, instead it filters records in the local dataset cache. Internally, FireDAC builds and maintains a list of filtered records. So, on large record volumes, the filter activation may take a long time, but the subsequent records navigation is as fast as without filtering.

Standard Filtering

The FireDAC datasets provide some options to filter records using a standard approach:

FDQuery1.Filter := 'OrderID in (10150, 10151, 10152)';
FDQuery1.Filtered := True;
  • The OnFilterRecord event handler allows you to implement filtering as a Delphi code. After specifying the OnFilterRecord handler, set the Filtered property to True to activate the filter. For example:
FDQuery1.OnFilterRecord := Form1FilterRecord;
FDQuery1.Filtered := True;

procedure TForm1.Form1FilterRecord(DataSet: TDataSet; var Accept: Boolean);
var
  iOrderID: Integer;
begin
  iOrderID := DataSet.FieldByName('OrderID').AsInteger;
  Accept := (iOrderID = 10150) or (iOrderID = 10151) or (iOrderID = 10152);
end;

Filtering by Range of Values

When the dataset is sorted using the field list, the application applies filtering by a range of field values. This is the most effective way to limit records, because it uses the dataset internal index structures.

The following methods control the filtering:

  • SetRangeStart -- brings the dataset to a state that allows setting the minimum range value and erasing the previous values.
  • EditRangeStart -- brings the dataset to a state that allows setting the minimum range value, preserving the previous values.
  • SetRangeEnd -- brings the dataset to a state that allows setting the maximum range value and erasing the previous values.
  • EditRangeEnd -- brings the dataset to a state that allows setting the maximum range value, preserving the previous values.
  • ApplyRange -- activates the range filtering after specifying minimum and maximum values.
  • SetRange -- combines SetRangeStart, SetRangeEnd, and ApplyRange into a single method.
  • CancelRange -- cancels the range filtering.

The following properties control the filtering:

  • IsRanged -- allows getting the current range filtering mode.
  • KeyExclusive -- gets or sets the inclusion of minimum and maximum values into a filtered range.
  • KeyFieldCount -- gets or sets the number of index fields to use in range filtering.

For example:

FDQuery1.IndexFieldNames := 'ORDERID;ORDERDATE';
FDQuery1.SetRangeStart;
FDQuery1.KeyExclusive := False;
FDQuery1.KeyFieldCount := 1;
FDQuery1.FieldByName('OrderID').AsInteger := 10150;
FDQuery1.SetRangeEnd;
FDQuery1.KeyExclusive := False;
FDQuery1.KeyFieldCount := 1;
FDQuery1.FieldByName('OrderID').AsInteger := 10152;
FDQuery1.ApplyRange;

Filtering by Record Status

The FilterChanges property allows filtering records, depending on their change status. This type of filtering can be used only in Cached Updates mode. For example, to show only modified and deleted records, use the following:

FDQuery1.FilterChanges := [rtModified, rtDeleted];

To filter records the ApplyUpdates call failed to process, use the FilterChanges property and set its value to rtHasErrors.

Other Options

There are other options that allow limiting the visible records, such as:

Also, note that FireDAC does not support filtering on fields of fkCalculated or fkLookup type. Instead, the application uses fkInternalCalc and fkAggregate fields in filtering.

TFDTable and Filtering

TFDTable in live data window mode uses server-side filtering (WHERE) in the following cases:

  • For the Filter property. Note that the Filter property content is sent to a DB as is. You may use FireDAC escape sequences to make an expression compatible with a DBMS and with the local expression engine.
  • For a detail TFDTable in the master-detail relationship.
  • When a range is applied to a TFDTable.

TFDTable in live data window mode uses client-side filtering for:

  • The OnFilterRecord event.
  • The FilterChanges property.

See Also

Samples