In LiveBindings situation, how can I display the labels contained in a AdapterBindSource to a column of a grid?

Question

We have a Members table containing a field (as integer) to define the "status of membership" by his id. By example: 0 for Inactive, 1 for Active, 2 for LifeTime, 3 for Retired, 4 for Exceptional Achievement, etc,

We have a StringGrid attach to the Members table with LiveBindings. So, in the grid we see the id number of the status (1 or 2 or Etc.). But we want to see the "Labels" of that status (ex: LifeTime) and not his id.

For information, the "status label" cannot be store in a Lookup table because it have to be translated in different language.

I've created an object containing the status ObjMemberStatus:TObjectList<TMemberStatus>;
that hold membership labels using a TAdapterBindSource.

I was able to assign this information to a ComboBox using a TDataGenerator. I think it's the way to do with ComboBox in that situation, and this is working very well for that.

My question is: how can I display the label contained in a AdapterBindSource to a column of a grid ?

I've perform some search and I did not see how to do that directly with LiveBindings. Maybe I've missied somethings.

I've also seen in the help that we can add that method: Lookup(scope lookup, key fields, key values, result fields) to the CustumFormat property. But no example show how to use that CustumFormat Method. I've search on the web for that and it seem to be undocumented.

Sub-Questions, there is a way to bypass or hook some code into the LiveBindings data assignation to component process ?

Answer 1

As I've said in comments I have done this with a lookup field linked with a memtable containing the code and localized description.

But lately I've used another approach, that could maybe be easier to implement in your case. I hook up the code field's GetText and SetText events with something like this:

Procedure TSomeLookupFieldHandler.LookupFieldHandleGetText(Sender: TField; Var Text: String; DisplayText: Boolean);
Var
  v: Variant;
Begin
  Text := '';
  If (DataSet <> Nil) And Not Sender.IsNull Then Begin
    DataSet.Active := True;
    v := DataSet.Lookup(KeyFieldName, Sender.Value, ResultFieldName);
    If Not VarIsNull(v) And Not VarIsEmpty(v) Then
      Text := v;
  End;
End;

Procedure TSomeLookupFieldHandler.LookupFieldHandleSetText(Sender: TField; Const Text: String);
Var
  v: Variant;
Begin
  If Text.IsEmpty Or (DataSet = Nil) Then
    Sender.Clear
  Else Begin
    DataSet.Active := True;
    v := DataSet.Lookup(ResultFieldName, Text, KeyFieldName);
    If Not VarIsNull(v) And Not VarIsEmpty(v) Then
      Sender.Value := v
    Else
      Sender.Clear;
  End;
End;

The TSomeLookupFieldHandler object has a reference to the DataSet it can use to do the lookup (I have a lot of fields that work like this). I instantiate one such object per lookup I need.

I'm not sure that the SetText event handler is needed if you want the field to be readonly. Mine aren't.

The localized descriptions still need to be in some dataset, like a TFDMemTable... but you can easily revise this to do the lookup on a TStringList or a TObjectList or some other container.

To use this you need to instantiate an object of this class and then hook up the field or fields:

lfh := TSomeLookupFieldHandler.Create(<Some parameters to setup the lookup dataset>);

SomeField.GetText:=lfh.LookupFieldHandleGetText;
SomeField.SetText:=lfh.LookupFieldHandleSetText;

SomeDataSet.FieldByName('code').GetText:=lfh.LookupFieldHandleGetText;
SomeDataSet.FieldByName('code').SetText:=lfh.LookupFieldHandleSetText;

If you have many fields to hook up, then its' easier to add a new method on TSomeLookupFieldHandler that takes a TField parameter and does the setup.

Answer 2

You seem to be making heavy weather of this.

I appreciate that there is the complication of translation, but the most straightforward thing to do is simply to apply the KISS principle and add a calculated field to the dataset which provides a textual representation of the membership status.

To handle the translation, the cleanest way would of course be to have a separate table of membership status texts for different languages and select the language to use at runtime. If you don't want to be bothered with that, just use the Values property of a TStringList to look up the translations in the grid DataSet's OnCalcFields event.

Update It is incorrect to say that you can add calculated fields to an FDQuery but not to an FDMemtable. The following code works fine:

procedure TForm1.FDMemTable1CalcFields(DataSet: TDataSet);
begin
  DataSet.FieldByName('Description').AsString := IntToStr(DataSet.FieldByName('ID').AsInteger);
end;

procedure TForm1.FormCreate(Sender: TObject);
var
  F : TField;
begin
  F := TIntegerField.Create(Self);
  F.FieldName := 'ID';
  F.FieldKind := fkData;
  F.DataSet := FDMemTable1;

  F := TStringField.Create(Self);
  F.Size := 80;
  F.FieldName := 'Description';
  F.FieldKind := fkInternalCalc;
  F.DataSet := FDMemTable1;

  FDMemTable1.CreateDataSet;
  FDMemTable1.InsertRecord([1]);
  FDMemTable1.InsertRecord([2]);
end;

and correctly displays both columns, ID and Description, in a live-bound StringGrid.