How to: 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 How to: Build Dynamic Lookup.

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.

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();
}