Lookup table
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();
}
How to: Build Dynamic Lookup
Dynamics AX is flexible enough that the developer can create custom lookups by running them dynamically from the X++ code.Today I will show how to dynamically create a lookup through X++.
We will modify the Vendor account lookup on the Customers form to allow users to select only those vendors that use the same currency as the currently selected customer.
Keep in mind that we can create a lookup directly on form but it’s a good practice to do all we can on the Table.
1. Open the VendTable table in the AOT, and create a new method
public static void lookupVendorByCurrency(FormControl _callingControl,
CurrencyCode _currency)
{
Query query;
QueryBuildDataSource qbds;
QueryBuildRange qbr;
SysTableLookup lookup;
;
query = new Query();
qbds = query.addDataSource(tableNum(VendTable));
qbr = qbds.addRange(fieldNum(VendTable,Currency));
qbr.value(queryvalue(_currency));
lookup = SysTableLookup::newParameters(tableNum(VendTable),
_callingControl,
true);
lookup.parmQuery(query);
lookup.addLookupField(fieldNum(VendTable, AccountNum), true);
lookup.addLookupField(fieldNum(VendTable,Party));
lookup.addLookupField(fieldNum(VendTable,Currency));
lookup.performFormLookup();
}
2. In the AOT, open the CustTable form, and override the lookup() method of the VendAccount field on the CustTable data source with the following code:
public void lookup(FormControl _formControl, str _filterStr)
{
VendTable::lookupVendorByCurrency(_formControl,
CustTable.Currency);
}
3. To test this, open Accounts receivable > Customers > All customers, select any of the customers, and click on the Edit button in the action pane. Once the Customers form is displayed, expand the Vendor account lookup located in the Miscellaneous details tab page, under the Remittance group. The modified lookup now has an additional column named Currency, and vendors in the list should match the customer’s currency:
DAX Beginners 