Use Multiple table in Custom Lookup in Ax

Description:-

In this article we will see how to use multiple tables in custom lookup. Simply we can use single table in SysTableLookup to popup the data but if we want to retrieve and join with other table then we can do it? Yes we can do it. Here is the example to understand how to use multiple tables in custom lookup.

Syntax:-

publicvoid lookup()
{
  //This lookup basis the list criteria on information in 2 tables
  //All display fields come from Table1, Table2 is used for limiting criteria
  //super();
  //This Next Line initializes the thesysTableLookupClass
  sysTableLookupsysTableLookup  = SysTableLookup::newParameters(tableNum(<TABLENAME1>), this);
  Query                   query = NewQuery();
  QueryBuildDataSource    qbdsTbl1, qbdsTbl2;
  QueryBuildRange         qbr;
  ;
  //Add Tables to the data source
  qbdsTbl1     = query.addDataSource(tableNum(<TABLENAME1>));
  qbdsTbl2     = qbdsTbl1.addDataSource(tableNum(TABLENAME2));

  //Add Query Ranges to limit List records 
  qbr = qbdsTbl1.addRange(fieldNum(<TABLENAME1>,<RANGEFIELDNAME1>));
  qbr.value(<CRITERIAVALUE>);
  qbr = qbdsTbl1.addRange(fieldNum(<TABLENAME1>,<RANGEFIELDNAME2>));
  qbr.value(<CRITERIAVALUE>);
  qbr = qbdsTbl1.addRange(fieldNum(<TABLENAME1>,<RANGEFIELDNAME3>));
  qbr.value(<CRITERIAVALUE>);

  //Since we have multiple tables in the query turn on relations
  qbdsTbl1.relations(true);

  //Add Ranges to the Table2
  qbr = qbdsTbl2.addRange(fieldNum(<TABLENAME2>, <RANGEFIELDNAME4>));
  qbr.value(<CRITERIAVALUE>);
  qbdsTbl2.relations(true);

  //The next 2 lines actualy adds the fields that will be displayed in the lookup grid
  sysTableLookup.addLookupfield(fieldNum(<TABLENAME1>, <DISPLAYFIELDNAME>));
  sysTableLookup.addLookupfield(fieldNum(<TABLENAME1>, <DISPLAYFIELDNAME>));

  //Hand the created query to the sysTableLookupClass
  sysTableLookup.parmQuery(query);

  //Display the drop down
  sysTableLookup.performFormLookup();
}

Here is the demo example for use multiple tables in custom lookup.

Example Like:

publicvoid lookup()
{
  //This lookup basis the list criteria on information in 2 tables
  //All display fields come from Table1, Table2 is used for limiting criteria  
  //super();  

  //This Next Line initializes the thesysTableLookupClass
  sysTableLookupsysTableLookup  = SysTableLookup::newParameters(tableNum(A_PurchaseOrder_Old), this);
  Query                   query           = NewQuery();
  QueryBuildDataSource    qbdsTbl1, qbdsTbl2;
  QueryBuildRange         qbr;
  ;

  //Add Tables to the data source
  qbdsTbl1      = query.addDataSource(tableNum(A_PurchaseOrder_Old));
  qbdsTbl2     = qbdsTbl1.addDataSource(tableNum(A_PurchaseOrder));

  //Add Query Ranges to limit List records 
  qbr = qbdsTbl1.addRange(fieldNum(A_PurchaseOrder_Old,Purchase_ID));
  qbr.value(A_PurchaseOrder.Purchase_ID);
  //qbr = qbdsTbl1.addRange(fieldNum(A_PurchaseOrder, Status));
  //qbr.value(enum2str(PO_Status::Close));
  //qbr = qbdsTbl1.addRange(fieldNum(A_PurchaseOrder, Purchase_Date));
  //qbr.value(date2str(A_PurchaseOrder.Purchase_Date,123,2,1,2,1,4));

  //Since we have multiple tables in the query turn on relations
  qbdsTbl1.relations(true);

  //Add Ranges to the Table2
  qbr = qbdsTbl2.addRange(fieldNum(A_PurchaseOrder, Purchase_ID));
  qbr.value(A_PurchaseOrder.Purchase_ID);
  //qbr = qbdsTbl2.addRange(fieldNum(A_PurchaseOrder, Vender_Code));
  //qbr.value(A_PurchaseOrder.Vender_Code);
  qbdsTbl2.relations(true);

  //The next 2 lines actualy adds the fields that will be displayed in the lookup grid
  sysTableLookup.addLookupfield(fieldNum(A_PurchaseOrder_Old, Purchase_ID));
  sysTableLookup.addLookupfield(fieldNum(A_PurchaseOrder_Old, Vendor_Code));
  sysTableLookup.addLookupfield(fieldNum(A_PurchaseOrder_Old, TodayDate));

  //Or else you can Display Other table Data
  //sysTableLookup.addLookupfield(fieldNum(A_PurchaseOrder, Purchase_ID));
  //sysTableLookup.addLookupfield(fieldNum(A_PurchaseOrder, Vender_Code));
  //sysTableLookup.addLookupfield(fieldNum(A_PurchaseOrder, Status));
  //sysTableLookup.addLookupfield(fieldNum(A_PurchaseOrder, Purchase_Date));

  //Hand the created query to the sysTableLookupClass
  sysTableLookup.parmQuery(query);
  //info(query.toString());

  //Display the drop down
  sysTableLookup.performFormLookup();
}

Author : Umesh Patel

Related Posts