How to use a recurring Integration Endpoint for importing data
You are here
Refresher On Database Upgrade Scripts for D365 F&O
Sample database upgrade scripts with use cases.
Database upgrade scripts from AX 2012 to D365 F&O have changed a little when you compare them to AX 2009 to AX 2012 upgrade scripts. We're going to cover a few use cases and script samples for how to perform some common and uncommon scenarios
Removing Custom InventDim Fields
Let's say I added 2 custom InventDim columns in AX 2012. As an organization, we've agreed that we are going to remove them during the upgrade process. However, I still need to keep the data around for a while longer and let inventory cycle out as it naturally would. I can't just delete the columns outright because then I'll have duplicate inventDim records. Let's use some of the OOTB Inventory Dimensions F&O:
[UpgradeScriptDescriptionAttribute("@SYS53630"),
UpgradeScriptStageAttribute(ReleaseUpdateScriptStage::PostSync),
UpgradeScriptTypeAttribute(ReleaseUpdateScriptType::StandardScript),
UpgradeScriptTableAttribute(tableStr(InventDim), false, true, true, false)]
public void moveCustomInventDimFields()
{
InventDim Inventdim;update_recordset inventDim
setting InventDimension1 = Inventdim.MyContainerId,
InventDimension2 = Inventdim.MySampleId;
}
As a post sync script, this will move data contained in my custom fields into standard fields. In the process of the upgrade, the inventDimension1 and 2 fields will be added, the data copied into them and later in the process, my custom fields will be removed. I have given the custom fields a Configuration Key of SysDeletedObjects73 so they will be cleaned up later in the upgrade in the database. They will still exist in the code base but can be removed at a later date.
Data Cleanup
Sometimes we'll have some junk data we need to get rid of. In this instance, I have some project workers that don't have a worker record associated with them. I'm not sure how the data got into this state but we can't keep the records as we're failing misc. data integrity checks.
[UpgradeScriptDescriptionAttribute("@SYS53630"),
UpgradeScriptStageAttribute(ReleaseUpdateScriptStage::PostSync),
UpgradeScriptTypeAttribute(ReleaseUpdateScriptType::StandardScript),
UpgradeScriptTableAttribute(tableStr(ProjWorkerSetup), false, true, true, false)]
public void updateProjWorkerSetup()
{
DataArea dataarea;
ProjWorkerSetup ProjWorkerSetup;
HcmWorker HCMWorker;while select dataarea where dataarea.isVirtual == NoYes::No
{
changecompany(dataarea.id)
{
ProjWorkerSetup = null;
while select forUpdate ProjWorkerSetup
{
if(!HcmWorker::exist(ProjWorkerSetup.Worker))
{
ttsBegin;
ProjWorkerSetup.delete();
ttsCommit;
}
}
}
}
}
Moving Data From 1 Table to Another
This is one method of moving data from 1 table to another. In this instance I have an ISV table that as some data in it (OldVendorStatus) but I want to keep some of that data in a table of my own design (NewVendorStatus). The plan is to just cherry pick the fields we need into our new table.
[UpgradeScriptDescriptionAttribute("Populate NewVendorStatus Table"),
UpgradeScriptStageAttribute(ReleaseUpdateScriptStage::PostSync),
UpgradeScriptTypeAttribute(ReleaseUpdateScriptType::StandardScript),
UpgradeScriptTableAttribute(tableStr(NewVendorStatus), true, true, true, false),
UpgradeScriptTableAttribute(tableStr(OldVendorStatus), false, true, false, false)]
public void populateTableNewVendorStatus()
{
NewVendorStatus NewVendorStatus;
OldVendorStatus OldVendorStatus;insert_recordset NewVendorStatus (NewVendorStatusId, Description)
select StatusId, Description from OldVendorStatus;}
In this instance, OldVendorStatus has a configuration key of SysDeletedObjects73 so after upgrade, it will be removed from the database.
What If I just want to rename a field?
That's actually really easy now. You can use the following:
const str oldFieldName = 'Date';
const str newFieldName = fieldStr(MyTable, TransDate);ReleaseUpdateDB_GLSHelper::replaceTableField(tableStr(MyTable), oldFieldName, newFieldName);
Or Just Rename A Table?
That's also easy. The new table metadata has to exist in the code base as well.
SysDictTable sysDictTable = new SysDictTable(_tableId);
ReleaseUpdateDB_GLSHelper::executeSqlStatementUpdate(
strFmt("EXEC sp_rename '%1','%2'",
sysDictTable.name(DbBackend::Sql),
_oldNameSQL));
But What If I want To Do My Modifications in SQL?
You can do that too also. Take a look at ReleaseUpdateDB70_FixedAssetSetupDepBookRemovalBonus.preSyncCopyAssetGroupDepBookSetupBonus and ReleaseUpdateDB70_FixedAssetSetupDepBookRemovalBonus.postSyncInsertAssetGroupSetupBonus. Those methods do all of their work directly in SQL. Returning to our first example with InventDim, this is another way to do the same thing:
[UpgradeScriptDescriptionAttribute("@SYS53630"),
UpgradeScriptStageAttribute(ReleaseUpdateScriptStage::PostSync),
UpgradeScriptTypeAttribute(ReleaseUpdateScriptType::StandardScript),
UpgradeScriptTableAttribute(tableStr(InventDim), false, true, true, false)]
public void moveCustomInventDimFields()
{
sqlStatement = @"UPDATE inventDim set InventDimension1 = Inventdim.MyContainerId,
InventDimension2 = Inventdim.MySampleId";this.executeSqlStatement(sqlStatement);
}
This does nearly the same thing. However, this skips all business logic related to InventDim. This may be an on purpose choice but just something to be aware of. Also, database logging is skipped, if enabled. Lastly, we have to pay attention to timing. Depending on if this is a PostSync or PreSync script, the columns may not yet be there in the database. InventDimension1 and 2 will only exist after a database sync occurs adding in all fields from all version of F&O. However, if MyContainerId and MySampleId aren't in my code base, by the time we get to PostSync scripts, they will have already been dropped from the database.
What About Worksheet And WorksheetLine Tables?
Also easy but we have to make sure we aren't executing any business logic on those tables. We will have to skip it as much as possible if we're just moving data around. Below is an example where i'm moving data from one field to another on PurchLine and I want to to happen regardless of the PurchLine ( and PurchTable ) DocumentStatus or state. I just want it to copy the data, nothing more.
[UpgradeScriptDescriptionAttribute("Update Purchase Order Lines"),
UpgradeScriptStageAttribute(ReleaseUpdateScriptStage::PostSync),
UpgradeScriptTypeAttribute(ReleaseUpdateScriptType::StandardScript),
UpgradeScriptTableAttribute(tableStr(PurchLine), false, true, true, false)]
public void MoveValuesToforPurchLine()
{
PurchLine PurchLine;PurchLine.skipAosValidation(true);
PurchLine.skipBusinessEvents(true);
PurchLine.skipDatabaseLog(true);
PurchLine.skipDataMethods(true);
PurchLine.skipEvents(true);update_recordset PurchLine
setting NewSomeId = PurchLine.OldSomeId;}
Can I Empty a Table?
Sure. Be Careful.
[
UpgradeScriptStageAttribute(ReleaseUpdateScriptStage::PreSyncUpdate),
UpgradeScriptTypeAttribute(ReleaseUpdateScriptType::SharedScript),
UpgradeScriptTableAttribute(tableStr(MyTable), false, false, false, true)
]
public void preSyncMyTableClearScript()
{
str sqlStatement = strFmt('delete from %1', tableStr(MyTable));Connection sessionConn = new Connection();
Statement statement = sessionConn.createStatement();new SqlStatementExecutePermission(sqlStatement).assert();
statement.executeUpdate(sqlStatement);
CodeAccessPermission::revertAssert();
}