The database modification command
update_recordset gives you nice possibility to modify data in a fast way.
Unfortunately this kernel command a some little bug if you are using an array element of a joint table for value assignment. The follow
update_recordset will be partial ignored, specific from the assignement from the array element
custTableRead.Dimension[2]. This assignment and any following field value assignment will be left out (so the assignment of NameAlias with the value "any Alias" will just fail too):
CustTable custTableUpdate;
CustTable custTableRead;
;
ttsbegin;
update_recordset custTableUpdate
setting Street =
custTableRead.Street,
Name = custTableRead
.Dimension[2],
NameAlias = "any Alias"
where custTableUpdate
.AccountNum
== "00000001"
join custTableRead
where custTableRead
.AccountNum
== "00000002";
info(strfmt("%1", custTableUpdate
.RowCount()));
select firstonly custTableUpdate
where custTableUpdate.AccountNum
== "00000001";
// works fine so far
info(custTableUpdate.Street);
// you didn't get what you expect
info(custTableUpdate.Name);
// you didn't get what you expect
info(custTableUpdate.NameAlias);
ttscommit;
But all works fine as long as you code it without any array field:
update_recordset custTableUpdate
setting Name = custTableRead.Name,
NameAlias = "any Alias"
where custTableUpdate.AccountNum
== "00000001"
join custTableRead
where custTableRead.AccountNum
== "00000002";
Also works fine too, if the array field is hosted by the same table as the table which is selected for update.
update_recordset custTableUpdate
setting Name = custTableUpdate
.Dimension[2],
Street = custTableRead
.Street
where
custTableUpdate
.AccountNum
== "00000001"
join custTableRead
where custTableRead
.AccountNum
== "00000002";
To make a assignment from
custTableRead.Dimension[2] to
custTableUpdate.Name to work, you have to do it the old fashion way: select first, then update. Well, the execution of this code is slower, but it works (and that's prior in my eyes :).
ttsbegin;
select firstonly Dimension
from custTableRead
where custTableRead.AccountNum
== "00000002"
join forupdate custTableUpdate
where custTableUpdate
.AccountNum == "00000001";
custTableUpdate.Name = custTableRead
.Dimension[2];
custTableUpdate.update();
ttscommit;
This behaviour relates to AX 2009 only and (if not fixed in the meantime) later versions, since the ability to use joins in
update_recordset was invented in version AX 2009.