How to internally process filtered tDataSet records not to be shown on tDBGrid the result

Question

In the following tFDMemTable I try to sum value of records whose ID field starting letter A. A1, A2 and the result should be 4.

type
  TForm1 = class(TForm)
    FDMemTable1: TFDMemTable;
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    Button1: TButton;
    Button2: TButton;
    procedure FormCreate(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  end;

procedure TForm1.FormCreate(Sender: TObject);
var
  _FieldDef: TFieldDef;
begin
  _FieldDef := FDMemTable1.FieldDefs.AddFieldDef;

  _FieldDef.Name := 'ID';
  _FieldDef.DataType := ftString;
  _FieldDef.Size := 5;

  _FieldDef := FDMemTable1.FieldDefs.AddFieldDef;

  _FieldDef.Name :='value';
  _FieldDef.DataType := ftInteger;

  FDMemTable1.CreateDataSet;

  FDMemTable1.Append;

  FDMemTable1.FieldValues['ID'] := 'A1';
  FDMemTable1.FieldValues['value'] := 1;

  FDMemTable1.Append;

  FDMemTable1.FieldValues['ID'] := 'B1';
  FDMemTable1.FieldValues['value'] := 2;

  FDMemTable1.Append;

  FDMemTable1.FieldValues['ID'] := 'A2';
  FDMemTable1.FieldValues['value'] := 3;

  FDMemTable1.Append;

  FDMemTable1.FieldValues['ID'] := 'B2';
  FDMemTable1.FieldValues['value'] := 4;
end;

I wrote the following code but it changes tDBGrid as filtered. What I want is just an internal process that tDBGrid should stay without any change.

procedure TForm1.Button1Click(Sender: TObject);
var
  _ValueSum: Integer;
  i: Integer;
begin
  FDMemTable1.Filter := 'ID like ' + QuotedStr('A%');

  FDMemTable1.Filtered := True;

  _ValueSum := 0;

  FDMemTable1.FindFirst;

  for i := 0 to FDMemTable1.RecordCount - 1 do
  begin
    _ValueSum := _ValueSum + FDMemTable1.FieldValues['value'];

    FDMemTable1.FindNext;
  end;

  Button1.Caption := IntToStr(_ValueSum);
end;

I know tDataSet.Locate doesn't allow NEXT SEARCH that I tried a primitive way like this. It works fine but seems a little stupid.

procedure TForm1.Button2Click(Sender: TObject);
var
  _ValueSum: Integer;
  i: Integer;
begin
  _ValueSum := 0;

  FDMemTable1.First;

  for i := 0 to FDMemTable1.RecordCount do
  begin
    if Copy(FDMemTable1.FieldValues['ID'], 1, 1) = 'A' then
    begin
      _ValueSum := _ValueSum + FDMemTable1.FieldValues['value'];
    end;

    FDMemTable1.FindNext;
  end;

  Button2.Caption := IntToStr(_ValueSum);
end;

When I disconnect tFDMemTable and tDBGrid or set inactive before filtering to hold the last grid status, the grid changes to blank one. Is the last code the best solution or is there any better way which shows not filtered result while the filtering is working?

Answer 1

There are several things which, if not "wrong", are not quite right with your code.

1. You should be using Next, not FindNext to move to the next row in the dataset. Next moves to the next row in the dataset, whereas FindNext moves to the next row which matches search criteria you have already set up e.g. using DataSet.SetKey; ... - read the online help for FindKey usage.

2. You should NOT be trying to traverse the dataset using a For loop; use a While not FDMemData.Eof do loop. Eof stands for 'End of file' and returns true once the dataset is on its last row.

3. You should be calling FDMemTable1.DisableControls before the loop and FDMemTable1.EnableControls after it. This prevents db-aware controls like your DBGrid from updating inside the loop, which would otherwise slow the loop down as the grid is updating.

4. Unless you have a very good reason not to, ALWAYS clear a dataset filter in the same method as you set it, otherwise you can get some very confusing errors if you forget the filter is active.

5. Try to avoid using RecordCount when you don't absolutely need to. Depending on the RDMS you are using, it can cause a lot of avoidable processing overhead on the server and maybe the network (because with some server types it will cause the entire dataset to be retrieved to the client).

Change your first loop to

procedure TForm1.Button1Click(Sender: TObject);
var
  _ValueSum : Integer;
begin
  _ValueSum := 0;

  FDMemTable1.Filter := 'ID like ' + QuotedStr('A%');

  try
    FDMemTable1.DisableControls;
    FDMemTable1.First;
    while not FDMemTable1.Eof do begin
      _ValueSum:= _ValueSum + FDMemTable1.FieldByName('Value').AsInteger;
      FDMemTable1.Next;
    end
  finally
    FDMemTable1.Filter := '';
    FDMemTable1.Filtered := False;
    FDMemTable1.EnableControls;
  end;
   Button1.Caption := IntToStr(_ValueSum);
end;

If you do that, you don't need your Button2Click method at all.

As noted in a comment, you can use a TBookMark to record your position in the dataset before the loop and return to it afterwards, as in

var
  _ValueSum : Integer;
  BM : TBookMark;
begin
  _ValueSum := 0;

  BM := FDMemTable.GetBookMark;

  FDMemTable1.Filter := 'ID like ' + QuotedStr('A%');

  try
    [etc]
  finally
    FDMemTable1.Filter := '';
    FDMemTable1.Filtered := False;
    FDMemTable1.GotoBookMark(BM);
    FDMemTable1.FeeBookMark(BM);
    FDMemTable1.EnableControls;
  end;

By the way, you can save yourself some typing and get more concise code by using the InsertRecord method as in

FDMemTable1.InsertRecord(['A1', 1]);
FDMemTable1.InsertRecord(['B1', 2]);
FDMemTable1.InsertRecord(['A2', 3]);
FDMemTable1.InsertRecord(['B2', 4]);

Btw#2: The time to use FindKey is after you've set up a key to find, using by calling SetKey than then setting the key value(s).

For ordinary navigation of a dataset, use the standard navigation methods, e.g. Next, Prior, First, Last, MoveBy etc.

Answer 2

FireDAC has another interesting option - Aggregates:

procedure TForm1.Button1Click(Sender: TObject);
begin
  FDMemTable1.Aggregates.Clear;
  with FDMemTable1.Aggregates.Add do
  begin
    Name := 'SUM';
    Expression := 'sum(iif(ID like ''A%'', value, 0))';
    Active := True;
  end;
  FDMemTable1.AggregatesActive := True;
  FDMemTable1.Refresh;
  Button1.Caption := VarToStr(FDMemTable1.Aggregates[0].Value));
end;