DBLookupComboBox in FireMonkey, or a crutch for a red monkey

Good day to all!

Not so long ago I was faced with the need to work with a database from an FMX application.

Those who have already "felt" Delphi XE should be aware of the lack of such favorite VCLs in FMX as:

• TDBGrid
• TDBLoockupComboBox

And if the problem with DBGrid is solved quite intuitively, by visual binding, then with TDBLoockupComboBox not everything is so simple.
In any case, Google could not tell me anything sensible.

I solved the problem; I want to share the solution with the community, maybe someone will come in handy.

So, let's start by sketching a demo project.
For simplicity we’ll take SQLite.

Let's create the main plate, secondary, and the table of communication between them.
Well, we’ll throw a little notes for the test.
tbl_main, tbl_status, main_st.



Next, prepare the project in Delphi.
Main form, status change form, module for working with the database (DataModule).

On the form of the data module, we will throw the necessary components for working with data.
I prefer FireDAC, so I put in TFDPhysSQLiteDriverLink, TFDGUIxWaitCursor, TFDConnection and three TFDQuery.
Put TGrid on the main shape, stretch it to the whole shape.
We’ll put TComboBox on the status editing form, and we will use it to select the status.
By double-clicking on the main form grid, we will open the status editing form.



Also in the grid dataset, add a lookup-field “Status”, which will refer to the qStMain dataset:

And it will substitute in the grid column the name of the status associated with this record.

Now let's move on to the dataset that will fill the combo box:


Initial training completed.
Let's move on to the most interesting - a form of status change.
It will look like this:


Form preparation.

The very first thing to do is to attach the dataset to the combo box.
To do this, select the combo box in the designer and double-click on the LiveBindings line in the object inspector.
In the drop-down menu, select “Bind Visually”:


After that, in the lower part, the bindings window will be displayed:


For a lookup to the database, we need an intermediate field.
In this case, the Tag property, a combo box, is well suited.
In this field we will store the current id value of the selected dataset string.
Therefore, poke at three points and select Tag to display:


Do not forget to register our datamodule in uses forms.
After that, datasets appear in possible binds.


In binding, we extend connections from the fields of the dataset to combo boxing.
name drag in Item.Text, id drag in Item.LookupData.

This results in automatic creation on the form TBindSourceDB, TBindingsList and TLinkFillControlToField.


Already at the moment, if we do not forget to open the dataset, when you open the form, in the combo box there will be a list of values ​​from the dataset.


It would seem that everything is wonderful, but there are two points.

• When opening, it would be nice to position the dataset and combo box on the record that we clicked in the grid.
• When you select a value in a combo box, the cursor does not move on the dataset, but you have to!

Let's solve the first problem.
Let's create the Init () method in the form, into which we will pass the status id in the selected line.
Accordingly, having an id, we can do locate in the dataset and position ourselves on the record.
This action does not lead to any changes in the combo box, so you need to select the desired value in it.

procedure TfrmStChange.init(id: string);
begin
  with DM do
  begin
    if qStatuses.Active then
      qStatuses.Close;
    qStatuses.Open();
    qStatuses.Locate('id', id, []);
    ComboBox1.Tag := id.ToInteger;
    ComboBox1.ItemIndex := ComboBox1.Items.IndexOf
      (qStatuses.FieldByName('NAME').AsString);
  end;
end;

Wait, but in the grid we do not have id status!
Of course, you can add it to the request, but we are not looking for easy ways.

Let's go back to the main form and grid.
At the OnDblClick event of the grid, we’ll hang a display of the status change form:

procedure TfrmMain.Grid1DblClick(Sender: TObject);
var
  frm: TfrmStChange;
begin
  frm := TfrmStChange.Create(self);
  DM.qStMain.Locate('id_main', DM.FDQuery1.FieldByName('id').AsString, []);
  frm.init(DM.qStMain.FieldByName('id_status').AsString);
  frm.ShowModal;
  frm.Free;
end;

Now, by double-clicking on the grid line, a form for changing the status will open, with a combo box positioned at the desired status.

There is a problem two. Whatever status we choose in the combo box, the dataset attached to it will not move the cursor.

There are two solutions to the problem.
I googled a lot on this topic, but have not found a solution.
I had to come up with it myself.
Put on the TPrototypeBindSource status selection form.
In the "Structure" window, open the list for this object and click RMB -> "AddItem" on the line "FieldDefs":


Select its type "ftInteger" - this is id, and name it accordingly:


In LiveBindings Designer we stretch the connections from ComboBox1.SelectedValue to PrototypeBindSource1.id, then from PrototypeBindSource1.id to ComboBox1.Tag.


Oddly enough, from ComboBox1.SelectedValue we can not get id.
But after the procedure, from ComboBox1.Tag - we can!
Whoever does not believe can put a label on the form and write the Tag value of the combo box into it at the moment of onChange.
Or try to get id in any other way.

So, in the end, we have the record id in the dataset, this can and will be limited to by reading the value of the Tag field after closing the modal form.
But again, this is not our method.
It is necessary to move the cursor of the dataset bound to the combo box to the selected record.

If you look at the “Structure” window, you can notice the “LinkPropertyToFieldTag” element that appears:


Click on it and create an onAssignedValue event handler:

procedure TfrmStChange.LinkPropertyToFieldTagAssignedValue(Sender: TObject;
  AssignValueRec: TBindingAssignValueRec; const Value: TValue);
begin
  with DM do
  begin
    qStatuses.Locate('id', ComboBox1.Tag.ToString, []);
  end;
end;

Now, when selecting a row in a combo box, the dataset also moves the cursor!

And we can rightfully supplement the OnDblClick method of the main form grid:

procedure TfrmMain.Grid1DblClick(Sender: TObject);
var
  frm: TfrmStChange;
begin
  frm := TfrmStChange.Create(self);
  DM.qStMain.Locate('id_main', DM.FDQuery1.FieldByName('id').AsString, []);
  frm.init(DM.qStMain.FieldByName('id_status').AsString);
  frm.ShowModal;
  if (frm.ModalResult = mrOk) then
  begin
    DM.qStMain.Edit;
    DM.qStMainid_status.AsInteger := DM.qStatusesid.AsInteger;
    DM.qStMain.Post;
    refresh;//метод обновления грида, простой close\open датасета
  end;
  frm.Free;
end;

We launch, check, rejoice!
I hope this information helps someone get around the limitations imposed by fmx.

PS:

• I will gladly accept any constructive criticism.
• If someone knows the method better - tell me, sprinkle ashes on my head and admit wrong.
• The demo project is written only for the demo, any eye-cutting names of objects should be considered random and irrelevant.
• Well, do not judge strictly, if that ...