When Microsoft designed the Data Import Export Framework in AX2012 it provided some entities out of the box. In many scenarios you will be missing an entity. You can create your own entities in the development environment from scratch or you can use the wizard which will create the basic objects required for your new entity. Sometimes you might run into errors and you should start troubleshooting. This post will provide a walkthrough how to create an entity. When finished this entity has an error due to conversion of an enumeration field. A solution for this problem is also provided at the end of this blog.
Suppose you are in the need for a new entity based on the Inventory posting setup. The next steps should be taken to create a new one based on the table InventPosting.
The entity is now ready to be setup in the Target entities form and use it in a Processing group.
As told in the introduction, this entity will raise errors. This is at the time of copying data to the target. What is the exact error? What causes it? How to solve this? This will be explained below.
For the test I did create a very small CSV file with some records that could be used in the demonstration company USMF.
The source to staging was executed without problems. Note that the correct string values for the Account type are inserted in the staging table.
When you want to execute the Copy data to Target step, the job fails. The next error will be visible.
The error is stating that a nvarchar (string) field is not possible to convert to and int (integer). This is related to the enumeration fields in this table, for sure. But I learned that the enumeration conversion is working with the label, enumeration text and value number, so why is this failing?
SQL statement: SELECT T1.ITEMRELATION,T1.CUSTVENDRELATION,T1.TAXGROUPID,T1.INVENTACCOUNTTYPE,T1.ITEMCODE,T1.CUSTVENDCODE,T1.COSTCODE,T1.COSTRELATION,T1.CATEGORYRELATION,T1.LEDGERDIMENSION,T1.INVENTPROFILETYPEALL_RU,T1.INVENTPROFILETYPE_RU,T1.INVENTPROFILEID_RU,T1.SITECODE_CN,T1.SITERELATION_CN,T1.RECVERSION,T1.PARTITION,T1.RECID,T2.COSTRELATION,T2.CUSTVENDRELATION,T2.INVENTPROFILEID_RU,T2.ITEMRELATION,T2.SITERELATION_CN,T2.TAXGROUPID,T2.DEFINITIONGROUP,T2.ISSELECTED,T2.TRANSFERSTATUS,T2.EXECUTIONID,T2.ECORESCATEGORY_NAME,T2.ECORESCATEGORYHIERARCHY_NAME,T2.COSTCODE,T2.CUSTVENDCODE,T2.INVENTACCOUNTTYPE,T2.INVENTPROFILETYPE_RU,T2.INVENTPROFILETYPEALL_RU,T2.ITEMCODE,T2.LEDGERDIMENSION,T2.SITECODE_CN,T2.COSTGROUPID,T2.RECVERSION,T2.PARTITION,T2.RECID FROM INVENTPOSTING T1 CROSS JOIN DMFINVENTPOSTINGENTITY T2 WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?)) AND ((T2.PARTITION=?) AND ((((((((((((((T2.RECID=?) AND (T1.SITERELATION_CN=T2.SITERELATION_CN)) AND (T1.SITECODE_CN=T2.SITECODE_CN)) AND (T1.INVENTPROFILEID_RU=T2.INVENTPROFILEID_RU)) AND (T1.INVENTPROFILETYPE_RU=T2.INVENTPROFILETYPE_RU)) AND (T1.INVENTPROFILETYPEALL_RU=T2.INVENTPROFILETYPEALL_RU)) AND (T1.COSTRELATION=T2.COSTRELATION)) AND (T1.COSTCODE=T2.COSTCODE)) AND (T1.TAXGROUPID=T2.TAXGROUPID)) AND (T1.CUSTVENDRELATION=T2.CUSTVENDRELATION)) AND (T1.CUSTVENDCODE=T2.CUSTVENDCODE)) AND (T1.ITEMRELATION=T2.ITEMRELATION)) AND (T1.ITEMCODE=T2.ITEMCODE)) AND (T1.INVENTACCOUNTTYPE=T2.INVENTACCOUNTTYPE))) ORDER BY T1.INVENTACCOUNTTYPE,T1.CUSTVENDCODE,T1.CUSTVENDRELATION,T1.ITEMCODE,T1.ITEMRELATION,T1.TAXGROUPID,T1.INVENTPROFILETYPEALL_RU,T1.INVENTPROFILETYPE_RU,T1.INVENTPROFILEID_RU
After debugging and looking at the SQL statement it is noticed that it is not caused by the conversion from the staging to the target value for enumeration fields, but the attempt to find an existing record. AX tries to join the target and staging table record in a query to find a possible record to update instead of creating a new one. This join is build based on the InventPosting relation on the staging table. Below you will see the fields marked which are incorrect. Why?
This relation is automatically created by the wizard based on the primary index of the target table. It used the replacement key index in case of a record ID index and if this replacement key is unique.
But now the 64000 dollar question: How to solve it?
It is good to know that there are two attempts on finding an existing record. The first attempt is a query which is code based created where the staging and target table are linked using the table relation to the Target table. Just removing this relation will not solve the problem. It will then think there is no relation, so only records will be inserted at any time. The target table will then raise duplicate key errors.
Removing the incorrect fields from the relation is also not a good idea. It will then find the wrong existing records and will cause updating these existing records in stead of creating new records. This is the case when e.g. the values for Item and Customer relation are the same, but the only difference is in the Account type selection.
So now we have to know how and when the second attempt is executed and how this works. If the first attempt will not find any existing record, it will then find a record in the target table based on the replacement key of the target table. If there is no replacement key, it will try find an existing record based on primary index if this is not containing the Record ID field.
So we have to cheat AX to have no record found in the first attempt. For that, we need to delete all fields relations and create an impossible relation. E.g. a record ID from the staging table will be linked to the relation type field on the target table. Record IDs usually start with high numbers, so it will never find an existing record with low relation type values. In this way the first query method will have no existing record found and the second attempt is working correctly for this table.
If you make the changes and saves, compiles the table, you can rerun the target execution step which will now give the correct outcome without error.
Before you implement a similar cheat on your entities, make sure you test it carefully in a separate environment to make sure it will work correctly.
That’s all for now. Till next time!