TTMSFNCDataGrid filtering on text rather than value

Burton_Richard · June 12, 2025, 9:53am

Hi,

I have connected a dataset to my datagrid.
In my dataset, I have a an OnGetText event to give my data a display value.

When I apply a filter to the grid on the relevant column, it filters on the text rather than the underlying data value.

For example, if I have a column 'Nationality' with the data 'English' and the OnGetText event sets the text to 'ENG', I would expect a filter of

Filter.add(NationalityColumnIndex, gftEqual, 'English')

to work, but it doesn't; it only works if I supply the AValue as 'ENG'.

Is there a way to set the filter to use the data value rather than the text property?

Thanks!

Pieter · June 12, 2025, 3:16pm

Do you have a database adapter with loadmode = almAllRecords? Then it filters on the grid data, if you have almBuffered, then it filters directly on the dataset. Filtering on the grid data is not possible with the original value. You can still filter directly on the dataset as well if you wish to keep LoadMode = almAllRecords.

Burton_Richard · June 12, 2025, 4:00pm

Thanks for the reply.

Yes, I have almAllRecords set. That's the cause.

In which case, is there an easy way of filtering on the dataset?

Thanks again

Pieter · June 12, 2025, 6:02pm

So you have a couple of options. I'll add them in separate posts

Pieter · June 12, 2025, 6:04pm

Option 1, use the original value as cell data, use the display text of the field as display value of the grid. (Sample based on biolife.xml / client-dataset), test with category 3 letter copy, for example (Triggerfishy -> TRI, Snapper -> SNA)

unit Unit4;

interface

uses
  System.SysUtils, System.Types, System.UITypes, System.Classes, System.Variants,
  FMX.Types, FMX.Controls, FMX.Forms, FMX.Graphics, FMX.Dialogs,
  FMX.TMSFNCTypes, FMX.TMSFNCUtils, FMX.TMSFNCGraphics, FMX.TMSFNCGraphicsTypes,
  System.Rtti, FMX.TMSFNCDataGridCell, FMX.TMSFNCDataGridData,
  FMX.TMSFNCDataGridBase, FMX.TMSFNCDataGridCore, FMX.TMSFNCDataGridRenderer,
  FMX.Controls.Presentation, FMX.StdCtrls, Data.DB, FMX.TMSFNCCustomComponent,
  FMX.TMSFNCDataGridDatabaseAdapter, Datasnap.DBClient, FMX.TMSFNCCustomControl,
  FMX.TMSFNCDataGrid;

type
  TForm4 = class(TForm)
    TMSFNCDataGrid1: TTMSFNCDataGrid;
    ClientDataSet1: TClientDataSet;
    TMSFNCDataGridDatabaseAdapter1: TTMSFNCDataGridDatabaseAdapter;
    DataSource1: TDataSource;
    Button1: TButton;
    ClientDataSet1SpeciesNo: TFloatField;
    ClientDataSet1Category: TStringField;
    ClientDataSet1Common_Name: TStringField;
    ClientDataSet1SpeciesName: TStringField;
    ClientDataSet1Lengthcm: TFloatField;
    ClientDataSet1Length_In: TFloatField;
    ClientDataSet1Notes: TMemoField;
    ClientDataSet1Graphic: TGraphicField;
    procedure ClientDataSet1CategoryGetText(Sender: TField; var Text: string;
      DisplayText: Boolean);
    procedure TMSFNCDataGridDatabaseAdapter1FieldToData(Sender: TObject;
      ACell: TTMSFNCDataGridCellCoord; AMasterField, AField: TField;
      var ACellValue: TTMSFNCDataGridCellValue; var Handled: Boolean);
    procedure TMSFNCDataGrid1GetCellDisplayValue(Sender: TObject;
      ACell: TTMSFNCDataGridCellCoord; AData: TTMSFNCDataGridCellValue;
      var AValue: string);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form4: TForm4;

implementation

{$R *.fmx}

procedure TForm4.ClientDataSet1CategoryGetText(Sender: TField; var Text: string;
  DisplayText: Boolean);
begin
  Text := Copy(Sender.AsString, 1, 3).ToUpper;
end;

procedure TForm4.TMSFNCDataGrid1GetCellDisplayValue(Sender: TObject;
  ACell: TTMSFNCDataGridCellCoord; AData: TTMSFNCDataGridCellValue;
  var AValue: string);
var
  f: TField;
  dbc: TTMSFNCDataGridDatabaseAdapterColumn;
  o: Integer;
begin
  if TMSFNCDataGridDatabaseAdapter1.IsValidDataCell(ACell) then
  begin
    dbc := TMSFNCDataGridDatabaseAdapter1.ColumnAtField['Category'];
    if Assigned(dbc) and Assigned(dbc.GridColumn) and (dbc.GridColumn.Index = ACell.Column) then
    begin
      f := TMSFNCDataGridDatabaseAdapter1.FieldAtColumn[dbc.Index];
      if Assigned(f) then
      begin
        o := TMSFNCDataGridDatabaseAdapter1.DataLink.ActiveRecord;
        try
          if not TMSFNCDataGridDatabaseAdapter1.SetActiveRecord(ACell.Row) then
            Exit;

          AValue := f.DisplayText;
        finally
          TMSFNCDataGridDatabaseAdapter1.DataLink.ActiveRecord := o;
        end;
      end;
    end;
  end;
end;

procedure TForm4.TMSFNCDataGridDatabaseAdapter1FieldToData(Sender: TObject;
  ACell: TTMSFNCDataGridCellCoord; AMasterField, AField: TField;
  var ACellValue: TTMSFNCDataGridCellValue; var Handled: Boolean);
begin
  if AField.FieldName = 'Category' then
  begin
    ACellValue := AField.AsString;
    Handled := True;
  end;
end;

end.

Pieter · June 12, 2025, 6:06pm

Options 2, Filter directly on TDataSet

unit Unit4;

interface

uses
  System.SysUtils, System.Types, System.UITypes, System.Classes, System.Variants,
  FMX.Types, FMX.Controls, FMX.Forms, FMX.Graphics, FMX.Dialogs,
  FMX.TMSFNCTypes, FMX.TMSFNCUtils, FMX.TMSFNCGraphics, FMX.TMSFNCGraphicsTypes,
  System.Rtti, FMX.TMSFNCDataGridCell, FMX.TMSFNCDataGridData,
  FMX.TMSFNCDataGridBase, FMX.TMSFNCDataGridCore, FMX.TMSFNCDataGridRenderer,
  FMX.Controls.Presentation, FMX.StdCtrls, Data.DB, FMX.TMSFNCCustomComponent,
  FMX.TMSFNCDataGridDatabaseAdapter, Datasnap.DBClient, FMX.TMSFNCCustomControl,
  FMX.TMSFNCDataGrid;

type
  TForm4 = class(TForm)
    TMSFNCDataGrid1: TTMSFNCDataGrid;
    ClientDataSet1: TClientDataSet;
    TMSFNCDataGridDatabaseAdapter1: TTMSFNCDataGridDatabaseAdapter;
    DataSource1: TDataSource;
    Button1: TButton;
    ClientDataSet1SpeciesNo: TFloatField;
    ClientDataSet1Category: TStringField;
    ClientDataSet1Common_Name: TStringField;
    ClientDataSet1SpeciesName: TStringField;
    ClientDataSet1Lengthcm: TFloatField;
    ClientDataSet1Length_In: TFloatField;
    ClientDataSet1Notes: TMemoField;
    ClientDataSet1Graphic: TGraphicField;
    procedure ClientDataSet1CategoryGetText(Sender: TField; var Text: string;
      DisplayText: Boolean);
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form4: TForm4;

implementation

{$R *.fmx}

procedure TForm4.Button1Click(Sender: TObject);
begin
  ClientDataSet1.Filter := '[Category] = ''Trigger*''';
  ClientDataSet1.Filtered := True;
end;

procedure TForm4.ClientDataSet1CategoryGetText(Sender: TField; var Text: string;
  DisplayText: Boolean);
begin
  Text := Copy(Sender.AsString, 1, 3).ToUpper;
end;

end.

Pieter · June 12, 2025, 6:09pm

Option 3, use the filter builder to create more complex filter operations, makes it easier to use

unit Unit4;

interface

uses
  System.SysUtils, System.Types, System.UITypes, System.Classes, System.Variants,
  FMX.Types, FMX.Controls, FMX.Forms, FMX.Graphics, FMX.Dialogs,
  FMX.TMSFNCTypes, FMX.TMSFNCUtils, FMX.TMSFNCGraphics, FMX.TMSFNCGraphicsTypes,
  System.Rtti, FMX.TMSFNCDataGridCell, FMX.TMSFNCDataGridData,
  FMX.TMSFNCDataGridBase, FMX.TMSFNCDataGridCore, FMX.TMSFNCDataGridRenderer,
  FMX.Controls.Presentation, FMX.StdCtrls, Data.DB, FMX.TMSFNCCustomComponent,
  FMX.TMSFNCDataGridDatabaseAdapter, Datasnap.DBClient, FMX.TMSFNCCustomControl,
  FMX.TMSFNCDataGrid;

type
  TForm4 = class(TForm)
    TMSFNCDataGrid1: TTMSFNCDataGrid;
    ClientDataSet1: TClientDataSet;
    TMSFNCDataGridDatabaseAdapter1: TTMSFNCDataGridDatabaseAdapter;
    DataSource1: TDataSource;
    Button1: TButton;
    ClientDataSet1SpeciesNo: TFloatField;
    ClientDataSet1Category: TStringField;
    ClientDataSet1Common_Name: TStringField;
    ClientDataSet1SpeciesName: TStringField;
    ClientDataSet1Lengthcm: TFloatField;
    ClientDataSet1Length_In: TFloatField;
    ClientDataSet1Notes: TMemoField;
    ClientDataSet1Graphic: TGraphicField;
    procedure ClientDataSet1CategoryGetText(Sender: TField; var Text: string;
      DisplayText: Boolean);
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form4: TForm4;

implementation

{$R *.fmx}

uses
  TMS.TMSFNCFilterBuilder;

procedure TForm4.Button1Click(Sender: TObject);
var
  fb: TTMSFNCFilterBuilder;
begin
  fb := TTMSFNCFilterBuilder.Create;
  try
    fb.FormatType := fftDelphiDataSet;
    fb.Filter.AddExpression('Category', feoStartsWith, 'Trigger');
    ClientDataSet1.Filter := fb.FilterText;
    ClientDataSet1.Filtered := True;
  finally
    fb.Free;
  end;
end;

procedure TForm4.ClientDataSet1CategoryGetText(Sender: TField; var Text: string;
  DisplayText: Boolean);
begin
  Text := Copy(Sender.AsString, 1, 3).ToUpper;
end;

end.

Pieter · June 12, 2025, 6:11pm

Note that Option 1 also works when you are filtering with the grid filter dropdown

Burton_Richard · June 13, 2025, 10:19am

Wow, thanks for the comprehensive reply!
This is now working as expected!

system · June 14, 2025, 10:19am

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.

Topic		Replies	Views
TMSFNCDataGrid Filter do not work with Numbers TMS FNC UI Pack	16	99	March 10, 2025
TMSFNCDataGrid Sorting issue TMS FNC UI Pack	21	141	March 27, 2025
TTMSFNCDataGrid w/ lookup fields TMS FNC UI Pack	14	61	October 31, 2024
Multicolumn filtering on TTMSFMXGrid TMS FMX UI Pack	10	502	October 11, 2013
FNCDataGrid filter questions TMS FNC UI Pack	11	41	June 25, 2025

TTMSFNCDataGrid filtering on text rather than value

Related topics