Multi Table Lookup on a Form Field in Axapta

Sometimes, our client requires a  lookup with many information from two or more tables. The recipe below will show how to use Multiple Tables for lookup on a form field.


 public void lookup()
{
    Query                   query = new Query();
    QueryBuildDataSource    qbdsItem;
    QueryBuildDataSource    qbdsInventTableModule;
    QueryBuildDataSource    qbdsInventItemLocation;
    QueryBuildRange         qbrModuleType;
    SysMultiTableLookup     sysTableLookup;
    ;
    qbdsItem = query.addDataSource(tableNum(InventTable));

    qbdsInventTableModule = qbdsItem.addDataSource(tableNum(InventTableModule), "Purch");
    qbdsInventTableModule.relations(true);
    qbdsInventTableModule.fetchMode(QueryFetchMode::One2One);
    qbrModuleType = qbdsInventTableModule.addRange(fieldNum(InventTableModule, ModuleType));
    qbrModuleType.value(queryValue(ModuleInventPurchSales::Purch));

    qbdsInventTableModule = qbdsItem.addDataSource(tableNum(InventTableModule), "Invent");
    qbdsInventTableModule.relations(true);
    qbdsInventTableModule.fetchMode(QueryFetchMode::One2One);
    qbrModuleType = qbdsInventTableModule.addRange(fieldNum(InventTableModule, ModuleType));
    qbrModuleType.value(queryValue(ModuleInventPurchSales::Invent));

    qbdsInventTableModule = qbdsItem.addDataSource(tableNum(InventTableModule), "Sales");
    qbdsInventTableModule.relations(true);
    qbdsInventTableModule.fetchMode(QueryFetchMode::One2One);
    qbrModuleType = qbdsInventTableModule.addRange(fieldNum(InventTableModule, ModuleType));
    qbrModuleType.value(queryValue(ModuleInventPurchSales::Sales));

    qbdsInventItemLocation = qbdsItem.addDataSource(tableNum(InventItemLocation));
    qbdsInventItemLocation.relations(true);
    qbdsInventItemLocation.fetchMode(QueryFetchMode::One2One);

    sysTableLookup = SysMultiTableLookup::newParameters(this, query); //Use the Query for lookup

    sysTableLookup.addLookupField(fieldNum(InventTable, ItemId));
    sysTableLookup.addLookupField(fieldNum(InventTable, ItemName));
    sysTableLookup.addLookupMethod(tableMethodStr(InventTable, ConfigActive), 1, "Is configuration active?");
    sysTableLookup.addLookupField(fieldNum(InventTableModule, TaxItemGroupId), 2, false, "Purch Item Tax Group");
    sysTableLookup.addLookupField(fieldNum(InventTableModule, TaxItemGroupId), 3);
    sysTableLookup.addLookupField(fieldNum(InventTableModule, TaxItemGroupId), 4);
    sysTableLookup.addLookupField(fieldNum(InventItemLocation, CountGroupId), 5);

    sysTableLookup.performFormLookup();

}

Happy Daxing :-)