TFDMemTable lost/clearing data after filtering

Question

I have created a FDMemTable with following structure:

Cds_NaMenu := TFDMemTable.Create(nil);
Cds_NaMenu.FieldDefs.Add('ID', ftInteger);
Cds_NaMenu.FieldDefs.Add('MN_TELA_CODIGO', ftInteger);
Cds_NaMenu.FieldDefs.Add('MN_MENU_PESQUISA', ftString, 500);
Cds_NaMenu.FieldDefs.Add('DISPONIBILIDADE', ftInteger);
Cds_NaMenu.IndexDefs.Add('Ordem', 'MN_TELA_CODIGO', []);
Cds_NaMenu.CreateDataSet;
Cds_NaMenu.LogChanges := False;
Cds_NaMenu.IndexName := 'Ordem';

I put data in TFDMemTable like this:

Cds_NaMenu.Append;
Cds_NaMenu.FieldByName('DISPONIBILIDADE').AsInteger := 1;
Cds_NaMenu.Post;

Well... The problem ocurrs when a set filtered property to True and back to False. The RecordCount property goes to 0; None data has found in, even I use saveToFile procedure. Aparently the data was losted.

_recCount := Cds_NaMenu.RecordCount; // Result = 867;
Cds_NaMenu.Filter := 'DISPONIBILIDADE=1 AND MN_MENU_PESQUISA like ' + QuotedStr('%' + sTexto + '%');
Cds_NaMenu.Filtered := True;
_recCount := Cds_NaMenu.RecordCount; // Result = 0;
Cds_NaMenu.Filtered := False;
Cds_NaMenu.Filter := '';
_recCount := Cds_NaMenu.RecordCount; // Result = 0;

PS: With ClientDataSet, this code works perfectly

Code which relies on datasets' RecordCounts returning meaningful values is asking for trouble. Apart from anything else, different dataset types behave in different ways, as you're discovering. Sounds like your actual problem is an X/Y one. — MartynA, Aug 01 '17 at 22:03

Victoria · Answer 1 · 2017-08-02T01:47:15.813

If you're working with a pure memory table, there should not be any problem to query record count by the RecordCount property. Maybe you expect having NULL and empty value records included in a filtered view when having filter Value LIKE '%%', but it's not so. When having dataset like this:

ID | Value
1  | NULL
2  | ''
3  | 'Some text'

And applying filter like this:

var
  S: string;
begin
  S := '';
  FDMemTable.Filtered := False;
  FDMemTable.Filter := 'Value LIKE ' + QuotedStr('%' + S + '%');
  FDMemTable.Filtered := True;
  { ← FDMemTable.RecordCount should be 1 here for the above dataset }
end;

The empty and NULL value records should not be included in the view. Here is a short proof:

var
  S: string;
  MemTable: TFDMemTable;
begin
  MemTable := TFDMemTable.Create(nil);
  try
    MemTable.FieldDefs.Add('ID', ftInteger);
    MemTable.FieldDefs.Add('Value', ftString, 500);
    MemTable.IndexDefs.Add('PK_ID', 'ID', [ixPrimary]);
    MemTable.CreateDataSet;

    MemTable.AppendRecord([1, NULL]);
    MemTable.AppendRecord([2, '']);
    MemTable.AppendRecord([3, 'Some text']);

    S := '';
    MemTable.Filtered := False;
    MemTable.Filter := 'Value LIKE ' + QuotedStr('%' + S + '%');

    ShowMessage(Format('Total count: %d', [MemTable.RecordCount])); { ← should be 3 }
    MemTable.Filtered := True;
    ShowMessage(Format('Filtered count: %d', [MemTable.RecordCount])); { ← should be 1 }
    MemTable.Filtered := False;
    ShowMessage(Format('Total count: %d', [MemTable.RecordCount])); { ← should be 3 }
  finally
    MemTable.Free;
  end;
end;

I totally agree with you! The problem is: The .RecordCount does not back to 3 after Filtered setted to False. — Francisco Thiago, Aug 02 '17 at 18:04

MartynA · Answer 2 · 2017-08-01T22:43:34.293

0

I think this is just a minor FD quirk. The code below works as expected, with Cds_NaMenu declared as a TFDMemTable (though it would have been nice if you could have dropped the Cds_ to avoid confusion).

The key difference, I think, is the call to .Locate after the filter is cleared. The reason I put it there is because it causes the dataset to scroll and, I imagine, to recalculate its RecordCount as a result. Probably any other operation which causes a scroll would have the same effect, even MoveBy(0) - try it.

procedure TForm1.FormCreate(Sender: TObject);
var
  _recCount : Integer;
  ID : Integer;
  sTexto : String;
begin
  sTexto := 'xxx';  // added

  Cds_NaMenu.FieldDefs.Add('ID', ftInteger);
  Cds_NaMenu.FieldDefs.Add('MN_TELA_CODIGO', ftInteger);
  Cds_NaMenu.FieldDefs.Add('MN_MENU_PESQUISA', ftString, 500);
  Cds_NaMenu.FieldDefs.Add('DISPONIBILIDADE', ftInteger);
  Cds_NaMenu.IndexDefs.Add('Ordem', 'MN_TELA_CODIGO', []);
  Cds_NaMenu.CreateDataSet;
  Cds_NaMenu.LogChanges := False;
  Cds_NaMenu.IndexName := 'Ordem';


  Cds_NaMenu.Append;
  Cds_NaMenu.FieldByName('ID').AsInteger := 666;  // added
  Cds_NaMenu.FieldByName('DISPONIBILIDADE').AsInteger := 1;
  Cds_NaMenu.Post;


  _recCount := Cds_NaMenu.RecordCount; // Result = 1

  ID := Cds_NaMenu.FieldByName('ID').AsInteger;  // added

  Cds_NaMenu.Filter := 'DISPONIBILIDADE=1 AND MN_MENU_PESQUISA like ' + QuotedStr('%' + sTexto + '%');
  Cds_NaMenu.Filtered := True;
  _recCount := Cds_NaMenu.RecordCount; // Result = 0;
  Cds_NaMenu.Filtered := False;
  Cds_NaMenu.Filter := '';

  // Now force the dataset to scroll
  if Cds_NaMenu.Locate('ID', ID, []) then;  // added
  _recCount := Cds_NaMenu.RecordCount; // Result = 1;
  Caption := IntToStr(_recCount);  // added
end;

edited Aug 01 '17 at 22:43

answered Aug 01 '17 at 22:22

MartynA

30,454
4
32
73

If it's setup of a pure memory table, then there is no need for "scrolling to fetch", because everything is on the client side and the described problem should not happen. – Victoria Aug 02 '17 at 00:58
@Victoria, sure I realise there should be no need for scrolling but have come across a number of situations, not just with FireDAC, where problems like this one can be worked around by forcing a scroll, even if it does nothing, like `MoveBy(0)`. Btw, am I right in guessing that if I go to Help | About and do the Alt-Team trick the name of someone who is not keen on Firebird scrolls into view ;=)? – MartynA Aug 02 '17 at 06:35
Having just applied a filter, and having to force a scroll, wouldn't it be easier to read and sufficient to just call First? – Frazz Aug 02 '17 at 07:15
@Frazz:Easier, why? I used `Locate` because it means the user doesn't lose his position in the dataset. – MartynA Aug 02 '17 at 07:34
Ok. I hadn't considered that the user applying a different filter might want to preserve the positioned row. – Frazz Aug 02 '17 at 08:06
@MartynA, you can modify `RecordCountMode` option, but it's not necessary for memory table. That one queries the source view which is completely in memory (thus _fetched_). There's no need to do anything. – Victoria Aug 02 '17 at 09:24
@Victoria: Thanks for the info. I wasn't meaning to talk in terms of **fetching** data from anywhere. I was thinking in terms of the changes in the internal state of the dataset's state machine, which is defined by TDataSet, when the dataset's logical curor is moved. According to the behaviour TDataSet defines, this can happen in many circumstances without necessarily fetching any records at all. See, e.g. TDataSet.Resync. I'm sure you follow what I mean (?). – MartynA Aug 02 '17 at 09:59
@MartynA, yes, I do. But this time `SourceView.Rows.Count` is queried. And `SourceView` is a fully initialized memory collection. No data are lost and `RecordCount` should return always the same no matter what you set in `RecordCountMode`. My best guess is misunderstanding here. The OP shows a record that has NULL value and is filtered out by the `LIKE` expression. – Victoria Aug 02 '17 at 10:15
1

@Victoria. Sounds like yours is a good guess, I've +1ed your answer. – MartynA Aug 02 '17 at 10:26

TFDMemTable lost/clearing data after filtering

2 Answers2