Dynamics Community Forum Thread Details

I am facing an issue related to saving dynamic column values in a Sales Order scenario.

I have two related screens:

First screen

This screen is used to store locations related to a Sales Order.
- The number of locations is dynamic and not fixed per Sales Order.
- Locations are saved correctly in this screen.

Second screen

In this screen:
- The previously stored locations are dynamically converted into columns.
- The rows represent the Sales Order items (lines).
- Each cell should store the quantity/value of a specific item in a specific location.

Problem Description

When saving data from the second screen:

The values entered for all rows are incorrectly saved as the same values of the last row only.

As a result, in the SQL table, all item lines end up having identical values, matching the last inserted/updated row.

This behavior occurs despite the UI showing different values per row before saving.

I have attached:

The screen code used for handling the dynamic columns and saving the data.

Screenshots showing:
- The dynamic locations setup
- The dynamic grid layout
- The incorrect data stored in the SQL table

Question

What is the correct approach in Dynamics 365 to handle saving data from a grid with dynamic columns, ensuring that each row is saved with its own correct values and not overwritten by the last row?

Any guidance, best practices, or examples would be highly appreciated.

//////////////////////////////////////////////////////

[Form]
public class GIG_PartialShipmentItems extends FormRun
{
SalesId salesId;
Map locationControlMap;

public void init()
{
super();

salesId = element.args().parm();
locationControlMap = new Map(Types::String, Types::Class);
}

public void run()
{
super();

GIG_PartialShipmentItemsTmp_ds.executeQuery();

this.populateTmp();

GIG_PartialShipmentItemsTmp_ds.research(true);

this.buildDynamicColumns();
this.fillValues();
}

private void populateTmp()
{
SalesLine salesLine;
GIG_PartialShipmentItemsTmp tmp;

ttsbegin;

delete_from tmp where tmp.SalesId == salesId;

while select salesLine
where salesLine.SalesId == salesId
{
tmp.clear();
tmp.SalesId = salesLine.SalesId;
tmp.LineNum = salesLine.LineNum;
tmp.ItemId = salesLine.ItemId;
tmp.ItemName = salesLine.ItemName();
tmp.Qty = salesLine.QtyOrdered;
tmp.RemainingQty = salesLine.RemainSalesPhysical;
tmp.insert();
}

ttscommit;
}

private void buildDynamicColumns()
{
GIG_PartialShipmentDrivers drivers;
FormRealControl ctrl;
int idx = 1;

while select drivers
where drivers.SalesId == salesId
{
ctrl = ItemsGrid.addControl(
FormControlType::Real,
strFmt("dyn_%1", idx)
);

ctrl.label(drivers.Location);
ctrl.width(80);
ctrl.allowEdit(true);

locationControlMap.insert(drivers.Location, ctrl);
idx++;
}
}

private void fillValues()
{
GIG_PartialShipmentItemsTmp tmp;
GIG_PartialShipmentItemQty itemQty;
MapEnumerator mapEnum;
FormRealControl ctrl;
real totalQty;

while select tmp
where tmp.SalesId == salesId
{
mapEnum = locationControlMap.getEnumerator();

while (mapEnum.moveNext())
{
ctrl = mapEnum.currentValue();

select sum(Qty) from itemQty
where itemQty.SalesId == tmp.SalesId
&& itemQty.LineNum == tmp.LineNum
&& itemQty.Location == mapEnum.currentKey();

totalQty = itemQty.Qty;

ctrl.realValue(totalQty);
}
}
}

[Control("CommandButton")]
class btnSave
{
public void clicked()
{
super();

GIG_PartialShipmentItemQty itemQty;
GIG_PartialShipmentItemsTmp tmp;
MapEnumerator mapEnum;
FormRealControl ctrl;
FormDataSource tmp_ds = GIG_PartialShipmentItemsTmp_ds;

ttsbegin;

delete_from itemQty
where itemQty.SalesId == salesId;
tmp_ds.first();

while select tmp
{
mapEnum = locationControlMap.getEnumerator();

while (mapEnum.moveNext())
{
ctrl = mapEnum.currentValue();

if (ctrl.realValue() != 0)
{
itemQty.clear();
itemQty.SalesId = tmp.SalesId;
itemQty.LineNum = tmp.LineNum;
itemQty.Location = mapEnum.currentKey();
itemQty.Qty = ctrl.realValue();
itemQty.insert();
}
}
tmp_ds.next();
}

ttscommit;

info("Saved successfully");

}

//////////////////////////////////////////////////////

Categories:

Dynamics 365 Finance

Dynamics 365 Fraud Protection

Dynamics 365 Project Operations

Dynamics 365 Intelligent Order Management

Microsoft Dynamics AX

Dynamics 365 Human Resources

Dynamics 365 Project Service Automation