Home > OS >  Visibility of filtered out records in TDataSet OnFilterRecord event
Visibility of filtered out records in TDataSet OnFilterRecord event

Time:09-24

which run first, TDataSet OnFilterRecord or OnCalcFields event?
Are records set to Accept = false are still visible in OnCalcFields event?
if it is, is there a property to check record visibility?

the code situation is like, when dataset has more records like 3k,
OnRecordFilter has manual filter on string fields for records visibility on grid (Accept = true / false),
OnCalckFields has extra columns lookup to other datasets,
the function that sum the amount columns is so slow with or without filter.
when i disable the OnCalcFields event, the execution was so fast.

DataSet is TFDQuery, loaded initial data is free date range so user can view like 3 or more year date range.
ui looks like this https://i.stack.imgur.com/0XyrN.png

CodePudding user response:

You can test this for yourself.

  1. Create a new VCL project and add a TFDMemTable, TDataSource, TDBGrid, TCheckBox (called cbUseFilterExpr) and TButton to the form.

  2. Connect up the FDMemTable, TDataSource and TDBGrid as you normally would.

  3. Add an OnFilterCalls integer form field and event handlers shown below.

  4. Compile and run.

Code

procedure TForm1.FormCreate(Sender: TObject);
var
  AField : TField;
  i : Integer;
begin
  AField := TIntegerField.Create(Self);
  AField.FieldName := 'ID';
  AField.DataSet := FDMemTable1;

  AField := TStringField.Create(Self);
  AField.FieldName := 'Name';
  AField.DataSet := FDMemTable1;

  FDMemTable1.CreateDataSet;

  for i := 1 to 100 do
    FDMemTable1.InsertRecord([1, 'Name'   IntToStr(i)]);
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  OnFilterCalls := 0;

  FDMemTable1.Filtered := False;
  if cbUseFilterExpr.Checked then
     FDMemTable1.Filter := 'Name= ''Name1'''
   else
     FDMemTable1.Filter := '';
   FDMemTable1.Filtered := True;
   ShowMessage('OnFilterCalls '   IntToStr(OnFilterCalls));
end;

procedure TForm1.FDMemTable1FilterRecord(DataSet: TDataSet;
  var Accept: Boolean);
begin
  Inc(OnFilterCalls);
end;

The app populates FDMemTable1 with 100 records. The OnFilterCalls variable will count the number of times OnFilterRecord is called when filtering is activated.

Clicking Button1 sets a filter on FDMemTable1 which differs depending on whether cbUseFilterExpr is checked or not: If it is, filtering uses a filter expression which only matches record ID=1. The result displayed by ShowMessage is 1, iow, the OnFilterRecord event is called only once. If cbUseFilterExpr is not checked ShowMessage displays the value 100.

Conclusion: For FDMemTable (and, I confidently predict, other FireDAC dataset types) the OnFilterRecord event is called once for each record which matches the FDMemTable's Filter expression, if any, or once for each record in the dataset if the Filter expression is blank. Iow, OnFilterRecord is only called for records which match the Filter expression, if there is one, so it behaves as if OnFilterRecord is called "after" filtering via the Filter expression, so the answer to your q in FireDAC's case is "No", expression-filtered records are not visible in the OnFilterRecord event.

As mentioned in a comment, TDataSet does not define how a dataset processes filtering, rather it is implementation-specific and may differ between different dataset component libraries.

Update You still haven't provided any details of what exactly you are doing in your code (and on reflection your q should probably have been closed for lacking debugging details), but I think you can satisfy yourself that what I have said above also applies to your situation. Simply put a debugger breakpoint on the end in TForm1.FDMemTable1FilterRecord(DataSet: TDataSet. Run the app and check the cbUseFilterExpr checkbox. When the bp triggers, repeatedly single-step the debugger by pressing F8 until you land in the unit FireDAC.DatS, in the method TFDDatSView.Rebuild. You will see that your are in a for loop,

      for i := iBegin to iEnd do begin
        ...

This is the loop which is executed when the filtering is applied to the dataset, once for each record in the dataset, and from the for-loop's contents it will be a straightforward matter to satify yourself that the OnFilterRecord event is only called for any record which is visible because it satifies any filter expression which is in effect.

CodePudding user response:

Here's workaround i did to this OnCalcEvent difficulty while no exact answer to my query.

I created a public variable to skip the OnCalcEvent before calling the Sum() then free the variable after, then re-set the dataset bookmark to force OnCalcEvent execution to refresh the calc fields. Not re-setting bookmark leaves calcfields columns empty.

code is like :

TdtmdlMain::OnCalcEvent(TDataSet *DataSet){
  if (dstSkipCalc == DataSet) return;

  .... codes that update columm lookup values ...
}

TfrmUnit::Sum(){
   TDataSet dst = grd->DataSource->DataSet;
   dtmdlMain->dstSkipCalc = dst;

   ... code that sum columns ....

   dstSkipCalc = NULL;
   TBookmark bkmrk = GetBookmark(dst);
   SetBookmark(dst, bkmrk);
}

it works for me, for now.

another idea is to create pagination instead.

  • Related