Dynamics Community Forum Thread Details

SysDa Classes (Pull sysDaqueryobject Query field to compare with SyaDaUpdateObject query field)

Hi,

The goal is to get Taxitemgroupidsales from InventItemGroup table and update TaxItemGroupId for every item in InventTableModule (sales i.e. ModuleType =2) where TaxItemGroupId field is blank (not updated)

So, Using SysDaQueryobject joined 3 tables (inventTablemodule, inventitemgroupitem, inventitemgroup tables).

Then, how to write where clause and setting clause by calling SysDaUpdateObject class to update TaxItemGroupId field in InventTablemodule(sales)

SysDaUpdateObject updateObj;
InventItemGroupItem itemgroupitem;
InventTableModule InventTableModuleSales, TaxModuleSales;
InventItemGroup TaxinventItemGroup;
ItemGroupId TaxItemGroupId;

// Step 1: Subselect to get ItemGroupId from InventItemGroupItem

SysDaQueryObjectBuilder Itemgroupbuilder =
SysDaQueryObjectBuilder::from(itemgroupitem)
.innerJoin(InventTableModuleSales)
.where(itemgroupitem, fieldStr(InventItemGroupItem, ItemId)).isEqualTo(InventTableModuleSales, fieldStr(InventTableModule, ItemId))
.where(itemgroupitem, fieldStr(InventItemGroupItem, DataAreaId)).isEqualTo(InventTableModuleSales, fieldStr(InventTableModule, DataAreaId));

SysDaQueryObject itemGroupSubSelect = Itemgroupbuilder .toSysDaQueryObject();

itemGroupSubSelect.whereClause(new SysDaEqualsExpression(
new SysDaFieldExpression(InventTableModuleSales, fieldStr(InventTableModule, ModuleType)),
new SysDaValueExpression(2)));

itemGroupSubSelect.whereClause(new SysDaEqualsExpression(
new SysDaFieldExpression(InventTableModuleSales, fieldStr(InventTableModule, TaxItemGroupId)),
new SysDaValueExpression(' ')));

// Step 2: Subselect to get TaxItemGroupIdSales from InventItemGroup
// using ItemGroupId obtained above

SysDaQueryObject TaxItemGroupSubSelect = new SysDaQueryObject(TaxinventItemGroup);

SysDaQueryObject joinTaxitemGroupid = TaxItemGroupSubSelect.joinClause( SysDAJoinKind::InnerJoin, itemGroupSubSelect);

joinTaxitemGroupid.whereClause(new SysDaEqualsExpression(
new SysDaFieldExpression(TaxinventItemGroup, fieldStr(InventItemGroup, ItemGroupId)),
new SysDaFieldExpression(itemgroupitem,fieldStr(InventItemGroupItem,ItemGroupId))
));

updateObj = new SysDaUpdateObject(TaxModuleSales);

Thank you,

Lakshmi

Categories:

Dynamics 365 Finance

Quick Links