Get Item Supplementary items in D365 FO

Hi,

Dynamics AX/ D365 FO has a feature of adding supplementary items (Sales/Purchase) against a particular item (Release product) through supplementary item group defined in Sell/Purchase tab of a released product.


View the supplementary(Sales) items associated with this supplementary item group.




We have a requirement of getting list of all supplementary (Sale/Purchase) items associated to a particular item (MB-CLS350) in our case. Here is the code snippet that will do our task. Here, we are just targeting the supplementary sale items. We can modify the code a little bit to get the supplementary purchase items as well.

Code Snippet:

        QueryBuildDataSource    qbdsSuppItemTable, qbdsInventTable, qbdsInventItemGroupItem;
        QueryBuildRange             range;
        SuppItemGroupId             suppItemGroupId;
        str                                      criteriaStr = '((%1 == %2) && (%3 == "%4"))';
     InventTable                    inventTableParent = InventTable::find("MB-CLS350"); // The item for which supplementary sales items needs to be fetched.
        Query                                query = new Query();
        QueryRun                         queryRun;
        SuppItemTable                 suppItemTable;

        qbdsInventTable      = query.addDataSource(tablenum(InventTable));
        qbdsSuppItemTable = qbdsInventTable.addDataSource(tablenum(SuppItemTable));
        qbdsSuppItemTable.relations(false);
        qbdsSuppItemTable.joinMode(JoinMode::InnerJoin);
        qbdsSuppItemTable.addLink(fieldNum(InventTable, ItemId), fieldNum(SuppItemTable, SuppItemId));
        qbdsSuppItemTable.fetchMode(QueryFetchMode::One2One);

        qbdsInventItemGroupItem = qbdsInventTable.addDataSource(tablenum(InventItemGroupItem));
        qbdsInventItemGroupItem.relations(true);
        qbdsInventItemGroupItem.fetchMode(QueryFetchMode::One2One);

        suppItemGroupId     = inventTableParent.inventTableModuleSales().SuppItemGroupId;
        qbdsSuppItemTable.clearRanges();

       qbdsSuppItemTable.addRange(fieldnum(SuppItemTable,Module)).value(queryValue (ModuleInventCustVend::Cust));
       
        // Supplementary items for all items
        range =  qbdsSuppItemTable.addRange(fieldnum(SuppItemTable, ItemCode));
        range.value(strfmt(criteriaStr,
                        fieldid2name(qbdsSuppItemTable.table(), fieldnum(SuppItemTable, ItemCode)),
                        any2Int(TableGroupAll::All),
                        fieldid2name(qbdsSuppItemTable.table(), fieldnum(SuppItemTable, ItemRelation)),
                        ''));

        // Supplementary items for relative supplementary group
        if (suppItemGroupId)
        {
            range =  qbdsSuppItemTable.addRange(fieldnum(SuppItemTable, ItemCode));
            range.value(strfmt(criteriaStr,
                            fieldid2name(qbdsSuppItemTable.table(), fieldnum(SuppItemTable, ItemCode)),
                            any2Int(TableGroupAll::GroupId),
                            fieldid2name(qbdsSuppItemTable.table(), fieldnum(SuppItemTable, ItemRelation)),
                            suppItemGroupId));
        }
   
        // Supplementary items for specific item
        if (inventTableParent.ItemId)
        {
            range =  qbdsSuppItemTable.addRange(fieldnum(SuppItemTable, ItemCode));
            range.value(strfmt(criteriaStr,
                            fieldid2name(qbdsSuppItemTable.table(), fieldnum(SuppItemTable, ItemCode)),
                            any2Int(TableGroupAll::Table),
                            fieldid2name(qbdsSuppItemTable.table(), fieldnum(SuppItemTable, ItemRelation)),
                            inventTableParent.ItemId));
        }

        queryRun = new  QueryRun(query);

        while (queryRun.next())
        {
            suppItemTable = queryRun.get(tableNum(SuppItemTable));
            info(suppItemTable.SuppItemId);
        }



Happy DAXing