how to use TDBLookupComboBox with xDataClient for a foreign key

BIZ TMS XData
1

Hi
I'm trying to use TDBLookupComboBox to populate list item from a table that provides foreign key on other table
"User" belong to a "Departement"
image
All is exposed through an RestAPI with xData and works fine.

But for end user UI I would like to present details of objet "User" about "departement" through a TDBLookupComboBox
I created 2 AureliusDataSet (Users and Departements)
image

Departements_List        : TList<Tecl_departement>;
Users_List               : TList<Tecl_utilisateur>;
. . .
  ADS_Users.close;
  FreeAndnil(Users_List);
  Users_List :=  DM_DataAccess.ClientXData.List<Tecl_utilisateur>;
  ADS_Users.SetSourceList(Users_List);
  ADS_Users.Open;
  //----
  ADS_Departements.close;
  FreeAndnil(Departements_List);
  Departements_List :=  DM_DataAccess.ClientXData.List<Tecl_departement>;
  ADS_Departements.SetSourceList(Departements_List);
  ADS_Departements.Open;

About the TDBLookupComboBox I defines

  • DataSource = DS_Users
  • DataField = departement_id
  • ListSource = DS_Departements
  • ListField = departement_id;departement_nom
  • KeyField = departement_id

But I get an error as the departement_id from DS_Users is the ID of object not the ID of foreign key.
image
It seems comparing "142207584" to KeyField = departement_id in place of "4" which is the real ID of Primary key from linked table.

How through xDataClient I should process?

Thanks

2

The associations are objects, thus DS_Users.departement_id is an object, not an integer id (despite the name).
The KeyField should then point to Self, which holds the department object. The 142207584 is the pointer to the department object. Also note that since you are retrieving users separated from departments, such lookup field will not appear selected when you retrieve data, because the department instance in DS_Users will be different than the one from DS_Departements. More info here: How to create lookup fields using XDataClient? - #6 by wlandgraf.

Also note that if you simply want to display the department name associated with the customer, you can use create a string field with name departement_id.name (like you did with department_id.department_id).

3

Thanks Wagner
I look into example you provide but can you explain me how to create instance of TObjectMap, MapUser and MapDepartement, please (MapUser := TObjectMap.Create(??)) ?

I'm preparing code like

var
  User        : Tecl_utilisateur; // Aurelius Class
  departement : Tecl_departement; // Aurelius Class
  MapUser        : TObjectMap;    // unit = Aurelius.Engine.ObjectMap
  MapDepartement : TObjectMap;
begin
  FreeAndnil(Users_List);
  Users_List  :=  DM_DataAccess.ClientXData.List<Tecl_utilisateur>;
  for User in Users_List do
     if not MapUser.IsIdMapped(User.departement_id) then
        MapUser.Add(User.departement_id);
  ADS_Users.close;
  ADS_Users.SetSourceList(Users_List);
  ADS_Users.Open;
  //----
  
  FreeAndnil(Departements_List);
  Departements_List :=  DM_DataAccess.ClientXData.List<Tecl_departement>;
  for departement in Departements_List do
     if not MapDepartement.IsIdMapped(departement.departement_id) then
        MapDepartement.Add(departement.departement_id);
  ADS_Departements.close;
  ADS_Departements.SetSourceList(Departements_List);
  ADS_Departements.Open;

I guess, next will be to remplace code by

  ADS_Users.close;
  ADS_Users.ObjectClass  := Tecl_utilisateur;
  ADS_Users.SetSourceList(MapUser.GetList);
  ADS_Users.Open;
. . .
  ADS_Departements.close;
  ADS_Departements.ObjectClass  := Tecl_departement;
  ADS_Departements.SetSourceList(MapDepartement.GetList);
  ADS_Departements.Open;

Thanks

4

Actually I see now that the idea is a little bit different. You should add mapping only for departments:

  ADS_Departements.Close;
  FreeAndnil(Departements_List);
  Departements_List :=  DM_DataAccess.ClientXData.List<Tecl_departement>;
  for departement in Departements_List do
     if not Map.IsIdMapped(departement) then
        Map.Add(departement);
  ADS_Departements.SetSourceList(Departements_List);
  ADS_Departements.Open;

Then after this, for customers, what you should do is replace the departments that are already mapped. Something like this:

  ADS_Users.close;
  FreeAndnil(Users_List);
  Users_List  :=  DM_DataAccess.ClientXData.List<Tecl_utilisateur>;
  for User in Users_List do
     if Map.IsIdMapped(User.departement_id) then
       User.departement_id := Tecl_departement(Map.GetById(Tecl_departement, User.departement_id.id));
  ADS_Users.SetSourceList(Users_List);
  ADS_Users.Open;

That should ok. You only need one Map for all objects, and you create it by passing the TMappingExplorer object:

  Map := TObjectMap.Create(TMappingExplorer.Default);
5

Hi Wagner
Thanks for all details
I tried to follow your advice about Keyfield for DBLookupComBox
I change property to

  • KeyField = Self

But unfortunately an error "field Self not found" is fired like it doesn't attach this object reference to something existing. I guess it was something to create.
To help others TMS users, I had to defined here for ADS_Departement a field named "Self" as Integer.
It will refer to ID of object in list from ADS_Departement

Then DBLookupComBox with :

  • DataSource = DS_Users
  • DataField = departement_id
  • ListSource = DS_Departements
  • ListField = departement_id;departement_nom
  • KeyField = Self (the one from new specific field)

The ComBox is well populated with name of department for each users and when changed it updates the id of foreign key in users table.

Wagner, I guess will be useful to get a such example in xData doc, as it's a really powerful feature from this framework.

Many thanks Wagner.

1 Like
6

Maybe you have disabled the property CreateSelfField to false in your dataset?