All AnyDAC datasets are offering few approaches to locally filter the records. After applying a filter, AnyDAC will not requery records, but will filter records in local dataset cache. Internally AnyDAC builds and maintains a list of filtered records. So, on large record volumes the filter activation may be long, but subsequent records navigation will be the same fast as without filtering. 

AnyDAC datasets offer few options to filter records using a standard approach:

• Filter property allows to specify an conditional expression as a string. After specifying Filter value set Filtered property to True to activate the filter. For example:

ADQuery1.Filter := 'OrderID in (10150, 10151, 10152)';
ADQuery1.Filtered := True;

• OnFilterRecord event handler allows to implement filtering as a Delphi code. After specifying Filter value set Filtered property to True to activate the filter. For example:

ADQuery1.OnFilterRecord := Form1FilterRecord;
ADQuery1.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;

When dataset is sorted using the field list, then application may apply filtering by a field values range. This is most effective way to limit records, as it is using the dataset internal index structures. 

The following methods control the filtering:

• SetRangeStart - brings dataset to a mode allowing to set range minimal values and erasing the previous values;
• EditRangeStart - brings dataset to a mode allowing to set range minimal values preserving the previous values;
• SetRangeEnd - brings dataset to a mode allowing to set range maximal values and erasing the previous values;
• EditRangeEnd - brings dataset to a mode allowing to set range maximal values preserving the previous values;
• ApplyRange - activates range filtering after specifying minimal and maximal values;
• SetRange - combines SetRangeStart, SetRangeEnd and ApplyRange into a single method;
• CancelRange - cancels the range filtering.

And properties:

• IsRanged - allows to get the current range filtering mode.
• KeyExclusive - get / set the inclusion of minimal and maximal values into filtered range;
• KeyFieldCount - get / set the number of index fields to use in range filtering.

For example: 

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

The FilterChanges property allows to filter records, depending on their change status. This filtering may be used only in Cached Updates mode. For example, to show only modified and deleted records: 

ADQuery1.FilterChanges := [rtModified, rtDeleted];

To filter records, failed to process at ApplyUpdates call, use FilterChanges with rtHasErrors. 

There are other options allowing to limit the visible records:

• Using master-detail datasets relation;
• Combine sorting and filtering using local dataset indexes.

Also note, AnyDAC does not support filtering on fields of the fkCalculated and fkLookup kinds. But application may use fkInternalCalc and fkAggregate fields in filtering. 

TADTable in live data window mode uses server side filtering (WHERE) for:

• Filter property. Note, that the Filter property content is sent to a DB as is. You may use AnyDAC escape sequences to make an expression compatible with a DBMS and with the local expression engine;
• a detail TADTable in the master-detail relationship;
• when a range is applied to a TADTable.

And client side filtering for:

• OnFilterRecord event;
• FilterChanges property.

Writing Expressions, Sorting Records, Finding a Record, Calculated and Aggregated Fields, Master-Detail Relationship, Browsing Table

See the following demos for additional details:

• AnyDAC\Samples\Comp Layer\TADQuery\Filter
• AnyDAC\Samples\Comp Layer\TADMemTable\MasterDetail