If you are using table inheritance in AX, you may have created base and derived tables. The InstanceRelationType field in the base table is an indicator that the record in base table belongs to which derived table. This field contains the TableId of the derived table. There can be scenarios in which you configure some sample/ demo data or meta data for your application in these tables and you may want this data to be available with each application installation. You might have generated some sql scripts/ or using Sql DTS(Data Tranformation Service) to copy that data for each installation.
The problem that you will face in that case is that for every fresh installation of your application on AX i.e. deployment of your code on AX, Your tables and all other objects are created with a new unique ID. A new tableId field is generated for each installation.
This will cause your setup data not to work with the new installation since the table ids are now different on the new environment so in the base table, AX can not identify which record belongs to which table.
You might think of manually checking the table id for all the derived tables and try to update them in an AX job by X++ code but AX will not allow you to update the InstanceRelationType field.
The solution to update this field is to change it from Sql Server. A Sql script can be generated to update this field accordingly in the base table but even for that script, you will require the TableId/ Id fields for each derived table which you can get from AX. Getting the table id from AX is an additional step if you are working on AX R2 or R3. But if you are working on R1, then you can easily get the TableId from Sql as well.. which means you just have to execute a script with a click of a button and it will do all the work. Here is the sample Sql code to get the Table id field in R1.
declare @tableIdMyAxDerivedTable
as int = (select tableid from SQLDICTIONARY where NAME = 'MyAxDerivedTable and FIELDID = 0)
print @tableIdMyAxDerivedTable
After fetching the correct table id, lets update that in our data..
update MyBaseTable
set INSTANCERELATIONTYPE = @tableIdMyAxDerivedTable -- Current table id in AOT
where INSTANCERELATIONTYPE = 98789 -- Old table id in my data
To do the same task in AX R2 or R3... the solution is to create a job in AX, and place the above written sql update code in a string. You can easily fetch the table id from AX and replace that fetched table id in that sql query.
You can print that string by calling the info method...All you have to do now is to run that string/Sql query in Sql server.
The problem that you will face in that case is that for every fresh installation of your application on AX i.e. deployment of your code on AX, Your tables and all other objects are created with a new unique ID. A new tableId field is generated for each installation.
This will cause your setup data not to work with the new installation since the table ids are now different on the new environment so in the base table, AX can not identify which record belongs to which table.
You might think of manually checking the table id for all the derived tables and try to update them in an AX job by X++ code but AX will not allow you to update the InstanceRelationType field.
The solution to update this field is to change it from Sql Server. A Sql script can be generated to update this field accordingly in the base table but even for that script, you will require the TableId/ Id fields for each derived table which you can get from AX. Getting the table id from AX is an additional step if you are working on AX R2 or R3. But if you are working on R1, then you can easily get the TableId from Sql as well.. which means you just have to execute a script with a click of a button and it will do all the work. Here is the sample Sql code to get the Table id field in R1.
declare @tableIdMyAxDerivedTable
as int = (select tableid from SQLDICTIONARY where NAME = 'MyAxDerivedTable and FIELDID = 0)
print @tableIdMyAxDerivedTable
After fetching the correct table id, lets update that in our data..
update MyBaseTable
set INSTANCERELATIONTYPE = @tableIdMyAxDerivedTable -- Current table id in AOT
where INSTANCERELATIONTYPE = 98789 -- Old table id in my data
To do the same task in AX R2 or R3... the solution is to create a job in AX, and place the above written sql update code in a string. You can easily fetch the table id from AX and replace that fetched table id in that sql query.
You can print that string by calling the info method...All you have to do now is to run that string/Sql query in Sql server.
No comments:
Post a Comment