Create Lookup from multiple tables

Posted on January 30, 2014 by Christian Fleishmann Silva — 3 Comments

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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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