Create Lookup from multiple tables

Today I will be complementing our knowledge about custom lookup, if you still need to learn the basics please see my post about?

Sometimes, our client requires a ?lookup with many information from two or more tables. The recipe below will show how to create a lookup with two data sources that are often used on Dynamics, VendTable and DirPartyTable. I will not post how do it step-by-step with images as I usually do, as I said, you can check my posts to learn the basic first.

If your lookup requires a complex query with many joins I still recommend to use Form Lookup Instead.

Lookup Method:

public void lookup()

{

????Query?????????????????? query = new Query();

????QueryBuildDataSource??? qbds;

????QueryBuildDataSource??? qbdsJoin;

????SysTableLookup????????? sysTableLookup = sysTableLookup::newParameters( tableNum(VendTable), this);

????;

?

????qbds= query.addDataSource( tableNum(VendTable));

????qbdsJoin= qbds.addDataSource( tableNum(DirPartyTable));

????qbdsJoin.relations( false);

????qbdsJoin.fields().dynamic(NoYes::Yes);

????qbdsJoin.addLink( fieldNum(VendTable, Party), fieldNum(DirPartyTable, RecId));

????qbdsJoin.joinMode(JoinMode::InnerJoin);

?

????sysTableLookup.parmQuery(query);

????sysTableLookup.addLookupfield( fieldNum(VendTable, AccountNum), true);

????sysTableLookup.addLookupfield( fieldNum(VendTable, VendGroup), true);

????sysTableLookup.addLookupfield( fieldNum(VendTable, Party));

????sysTableLookup.performFormLookup();

}

------------------------Lookup Event Handler:------------------

/// <summary>

???///

???/// </summary>

???/// <param name="sender"></param>

???/// <param name="e"></param>

???[FormControlEventHandler(formControlStr(HcmPosition, HcmPositionDetail_SLD_Zone), FormControlEventType::Lookup)]

???public static void HcmPositionDetail_SLD_Zone_OnLookup(FormControl sender, FormControlEventArgs e)

???{

??????

?

???????Query query = new Query();

???????QueryBuildDataSource qbds;

???????QueryBuildRange qbr;

?

???????SysTableLookup sysTableLookup;

??????

???????sysTableLookup = SysTableLookup::newParameters(tableNum(OMOperatingUnit), sender);

???????sysTableLookup.addLookupfield(fieldNum(OMOperatingUnit, Zone));

???????sysTableLookup.addLookupfield(fieldNum(OMOperatingUnit, HcmWorker));

?

???????qbds = query.addDataSource(tableNum(OMOperatingUnit));

???????qbds = query.joinda

???????qbds.addRange(fieldNum(OMOperatingUnit,SLD_Zone)).value(sysquery::valueNotEmptyString());

?

???????sysTableLookup.parmQuery(query);

???????sysTableLookup.performFormLookup();

?

????

???}

Happy learning!!

Usama Mehmood