During the data migration from Ax 2012 to D365, the table might be renamed and there might be system setups e.g. electronic signatures for the table fields. (For example: In Ax 2012 the table is called “AATable”, in D365 the same table is called “BBTable”)
In this scenario, it would be good to store the table name and id, field names, and ids of the Ax 2012 table (“AATable”) in order to apply setups to the "BBTable" in D365 during the data migration. Using a temporary SQL table is a good choice to do so.
You can find a code that can be used as an example in order to give you an idea, below. You can copy the queries in SQL Server Management Studio to see how it works.
First of all, it is needed to create a temporary SQL table via the SQL command at the "PreSync" stage:
private str getSQLCreateTmpSQLTable()
{
str tmpSQLTable = “TmpSQLTable”;
str sqlStatement;
if (! this.isTablePresent(tmpSQLTable))
{
sqlStatement
= strFmt(@"
CREATE TABLE [dbo].%1(
[KEYFIELD] [nvarchar](10)
NOT NULL,
[FIELD1] [nvarchar](60) NOT NULL,
[FIELD2] [int] NOT NULL,
[DATAAREAID] [nvarchar](4)
NOT NULL,
[PARTITION] [bigint] NOT
NULL,
CONSTRAINT [I_TMPSQLTABLEDATAAREA]
PRIMARY KEY CLUSTERED
(
[DATAAREAID] ASC,
[PARTITION] ASC,
[KEYFIELD] ASC
)
WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS =
ON)
ON [PRIMARY])
ON [PRIMARY]",
tmpSQLTable);
}
return sqlStatement;
}
///
<summary>
///
Defines, whether the table exists in the database.
///
</summary>
///
<param name = "_tableName">
///
The name of the table.
///
</param>
///
<returns>
///
True if the table exists, otherwise false.
///
</returns>
public boolean isTablePresent(TableName _tableName)
{
str sqlQuery = strFmt(@"IF OBJECT_ID ('[dbo].[%1]', 'U') IS
NULL
SELECT 0
ELSE
SELECT 1",
_tableName);
str result = ReleaseUpdateDB::statementExeQuery(sqlQuery);
return str2Int(result);
}
NOTE: Enum fields are presented as number fields in Ax2012/D365. If you need to save enum field values you need to create an integer field.
Then populate the table with the data at the "PreSync" stage:
private str getSQLPopulateTmpSQLTable()
{
TableName tmpSQLTable =
“TmpSQLTable”;
str sqlStatement = strFmt(@"
INSERT INTO [dbo].%1
(KEYFIELD,
FIELD1,
FIELD2,
DATAAREAID,
PARTITION)
SELECT [dbo].[INVENTITEMSAMPLING].[INVENTITEMSAMPLINGID],
[dbo].[INVENTITEMSAMPLING].[DESCRIPTION],
[dbo].[INVENTITEMSAMPLING].[TESTQTYSPECIFICATION],
[dbo].[INVENTITEMSAMPLING].[DATAAREAID],
[dbo].[INVENTITEMSAMPLING].[PARTITION]
FROM [dbo].[INVENTITEMSAMPLING]
where [dbo].[INVENTITEMSAMPLING].[COMPLETEBLOCKING] = 1",
tmpSQLTable);
return sqlStatement;
}
Now we can use the methods in the data upgrade method:
///
<summary>
///
Saving data to the temporary table.
///
</summary>
[UpgradeScriptDescription("@SYS113629"),
UpgradeScriptStage(ReleaseUpdateScriptStage::PreSync),
UpgradeScriptType(ReleaseUpdateScriptType::SharedScript),
UpgradeScriptTable(tableStr(InventItemSampling), false, true, false, false)]
public void preSyncAQMInventItemSampling()
{
str sqlStatement;
// Create the temporary upgrade table.
sqlStatement = this.getSQLCreateTmpSQLTable();
if (sqlStatement)
{
ReleaseUpdateDB::statementExeUpdate(sqlStatement);
}
// Populate the temporary table.
sqlStatement = this. getSQLPopulateTmpSQLTable();
if (sqlStatement)
{
ReleaseUpdateDB::statementExeUpdate(this.getSQLPopulateTmpSQLTable());
}
}
The final step is to use the temporary table in order to manipulate data at the “PostSync” stage:
///
<summary>
///
Update the <c>InventItemSampling</c> table with the saved values.
///
</summary>
[UpgradeScriptDescription("@SYS113629"),
UpgradeScriptStage(ReleaseUpdateScriptStage::PostSync),
UpgradeScriptType(ReleaseUpdateScriptType::SharedScript),
UpgradeScriptTable(tableStr(InventItemSampling), false, true, true, false)]
public void postSyncInventItemSampling()
{
str sqlStatement;
TableName tmpSQLTable =
“TmpSQLTable”;
if (this.isTablePresent(tmpSQLTable))
{
sqlStatement
= strFmt(@'UPDATE [dbo].[INVENTITEMSAMPLING]
SET [dbo].[INVENTITEMSAMPLING].[DESCRIPTION]
= ''
from
[dbo].[INVENTITEMSAMPLING] as iis
join [dbo].[%1]
as upgTmp
ON iis.INVENTITEMSAMPLINGID
= upgTmp.KEYFIELD
and
iis.DATAAREAID = upgTmp.DATAAREAID
and
iis.PARTITION = upgTmp.PARTITION',
tmpSQLTable);
ReleaseUpdateDB::statementExeUpdate(sqlStatement);
sqlStatement = strFmt(@"DROP TABLE %1", tmpSQLTable);
ReleaseUpdateDB::statementExeUpdate(sqlStatement);
}
}
I’ll be glad if you find this helpful.
No comments:
Post a Comment