Using Insert_RecordSet or Update_RecordSet to pull a month or year value from a field

Finance and Operations/AX Developer Forum
1

Trying to use the insert_recordset or update_recordset to pull the month or year values from a field to populate another field. Is there any way to do this with these two commands. Have over 75,000 records to populate and do not want to do this row by row.

Below is the example code I have tried to use with no luck.

update_recordSet xtraAssetMonthlyReport
setting
AcqusitionMonth = mthOfYr(assetDepBook.acquisitiondate),
join assetDepBook
where assetDepBook.depreciationbookid = xtraAssetMonthlyReport.depreciaitonbookid
&& assetDepBook.assetid = xtraAssetMonthlyReport.assetid
OR
insert_recordset XTRAAssetMonthlyAcqMonthTmp (AssetId, depreciationbookid,acquisitionmonth)
select AssetId, depreciationbookid from xtraAssetMonthlyReport
join mthOfYr(acquisitiondate)
from assetDepBook
where assetDepBook.assetid == xtraAssetMonthlyReport.assetid
&& assetDepBook.depreciationbookid == xtraAssetMonthlyReport.depreciationbookid;

update_recordset xtraAssetMonthlyReport
setting acquisitionmonth = xtraAssetMonthlyAcqMonthTmp.acquisitionmonth;

Appreciate any help

2

You can’t send to SQL Server a command that depends on X++ function mthOfYr(). The database server wouldn’t be able to run it.

If you’re using AX 2012, consider creating a view with a computed column instead of creating a filling a new field.

3

If a view is used to create a computed column, won’t I then receive a message stating “Map and view fields cannot be assigned to fields in an update_recordset statement”.

4

What I suggested was using a computed column instead of the field. Because the column would be calculated for you, you wouldn’t have to maintain the field with update_recordset.