Get Item Supplementary items in D365 FO
Views (4478)
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
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
This was originally posted here.
*This post is locked for comments