Tuesday, October 24

AX 2012 - Multi Table lookup - Display Fields from Multiple table in a lookup

Multi Table lookup - Where you can set lookup field from multiple table by joining multiple table and create a multi table lookup using SysMultiTableLookup. 

here is the Demo of the below code:




    Query                   query;
    QueryBuildDataSource    InventTrans;
    QueryBuildDataSource    GroupItem;
    QueryBuildDataSource    EcoRes;

    SysMultiTableLookup sysTableLookup;
    query = new Query();

    InventTrans = query.addDataSource(tableNum(InventTable));
    InventTrans.addRange(fieldNum(InventTable, SL_IncludeInSchemes)).value(queryValue(NoYes::Yes));

    //join the translation table so we can get a description of the UOM
    EcoRes = InventTrans.addDataSource(tableNum(EcoResProductTranslation));
    EcoRes.joinMode(JoinMode::InnerJoin);
    EcoRes.relations(false);
    EcoRes.addLink(fieldNum(InventTable,Product),fieldNum(EcoResProductTranslation,Product));

    //join the translation table so we can get a description of the UOM
    GroupItem = InventTrans.addDataSource(tableNum(InventItemGroupItem));
    GroupItem.joinMode(JoinMode::InnerJoin);
    GroupItem.relations(false);
    GroupItem.addLink(fieldNum(InventTable,ItemId),fieldNum(InventItemGroupItem,ItemId));
    GroupItem.addLink(fieldNum(InventTable,DataAreaId),fieldNum(InventItemGroupItem,ItemDataAreaId));

    //filter by the unit class being passed
    GroupItem.addRange(fieldNum(InventItemGroupItem, ItemGroupId)).value(queryValue(smmParametersTable::find().SL_SchemeReleaseItemGroup));

    //define multiple table lookup query
    sysTableLookup  = SysMultiTableLookup::newParameters(_formControl, query);
    //add which fields will be displayed to the user (symbol + desc.)
    sysTableLookup.addLookupfield(fieldNum(InventTable, ItemId), true);
    sysTableLookup.addLookupfield(fieldNum(EcoResProductTranslation, Name), 2);
    //do not use for multi table
    //sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();

1 comment:

Popular Posts