Ax 2012: Lookup with Joins

//This is for lookup with InnerJoin ////Write in Form Level Design>Field>Lookup

public void lookup()
{
   // create the query for the lookup
   Query                   query                = new Query();
   QueryBuildDataSource    cpj;

   QueryBuildDataSource    bol;
   QueryBuildRange         qbr1;
 
  // Intantiantes a SysTableLookup object telling it which control activated the lookup, and
 // what table should be displayed inside the lookup form.(root table)
 //"Failed to execute query because no root data source on the form matches the root datasource on            the query"
 //This error can occur when the wrong TableId is passed in SysTableLookup::newParameters().
 //Make sure you use the table you want to do the lookup in (not the table you're doing for).
 
   SysTableLookup sysTableLookup = sysTableLookup::newParameters(tableNum(LTCustomerDetails),this);

   cpj   =   query.addDataSource(tablenum(LTCustomerDetails));
   qbr1  = cpj.addRange(fieldnum(LTCustomerDetails,LTCustomerID));

   qbr1.value(queryvalue('3'));

   //Join Table
   bol   = cpj.addDataSource(tablenum(LTPurchaseDetails));
   bol.relations(false);
   bol.joinMode(joinmode::InnerJoin);
   bol.addLink(fieldnum(LTCustomerDetails,LTCustomerID),fieldnum(LTPurchaseDetails,LTCustomerID));

   //Add the query to the lookup form
   sysTableLookup.parmQuery(query);

   // Add fields that will be shown in the lookup as columns
   // Specify the fields to show in the form.
   sysTableLookup.addLookupfield(fieldNum(LTCustomerDetails,LTCustomerID));
   sysTableLookup.addLookupfield(fieldNum(LTPurchaseDetails,LTCustomerName));
 
   // Perform the lookup
   sysTableLookup.performFormLookup();

}

example


public void lookup() //on form level
{
    Query query = new Query();
    QueryBuildDataSource queryBuildDataSource;
    QueryBuildDataSource queryBuildDataSource2;
    QueryBuildRange      queryBuildRange;

    SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(InventTable), this);

    sysTableLookup.addLookupField(fieldNum(InventTable, ItemId));
    sysTableLookup.addLookupField(fieldnum(InventTable,NameAlias));
 
    queryBuildDataSource = query.addDataSource(tableNum(InventTable));
    queryBuildDataSource2   = queryBuildDataSource.addDataSource(tablenum(EcoResProduct));
    queryBuildDataSource2.relations(false);
    queryBuildDataSource2.joinMode(joinmode::InnerJoin);
    queryBuildDataSource2.addLink(fieldnum(EcoResProduct,RecId),fieldnum(InventTable,Product));          
       
    queryBuildRange = queryBuildDataSource2.addRange(fieldNum(EcoResProduct,LTPurchaseOrderType));
    queryBuildRange.value(enum2str(PurchTable.LTPurchaseOrderTypes));


    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();
}