data source
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();
}
How to: Create a lookup without Data Source
Good holidays everyone,
This post is dedicated to everyone who already made the question “How the hell do I insert a combo box here?” This happens a lot when you want to use a combo box as a filter.
It should be something simple, maybe it is and I haven’t found yet, but many of us have tried to drag and drop a table to data source and then using the lookup field or using a combo box control.
None of it will work and the only solution I have found so far is to create a lookup without data source.
To illustrate my example I have this form:
As you can see, I would like to use ItemId as a filter but I have no data source.
1. On the form Design, I have created a new StringEdit Control and renamed it to StringEdit_ItemId. See form structure below.
2. Expand the StringEdit_ItemId control and right click on Methods > Override Method > lookup. Like the image below:
3. Now, insert the following code and we are done!
// Override the method lookup()
public void lookup()
{
Query query;
QueryBuildDataSource qbds;
SysTableLookup lookup;
;
// Create the query for the lookup
query = new query();
qbds = query.addDataSource( tableNum(InventTable));
// Instantiate sysTableLookup object using table which will provide the visible fields
lookup = SysTableLookup::newParameters( tableNum(InventTable), this);
// Add fields that will be shown in the lookup as columns
lookup.addLookupfield( fieldNum(InventTable,ItemId));
lookup.addLookupMethod( tableMethodStr(InventTable,itemGroupId));
lookup.addLookupMethod( tableMethodStr(InventTable,defaultProductName));
lookup.addLookupfield( fieldNum(InventTable,NameAlias));
lookup.addLookupfield( fieldNum(InventTable,ItemType));
lookup.addSelectionField( fieldNum(InventTable,Product));
// Add the query to the lookup form
lookup.parmQuery(query);
// Perform the lookup
lookup.performFormLookup();
}
DAX Beginners 