BYOD Export: Cannot Create a record in Inventory transactions originator (InventTransStaging)... The record already exists

(0) Share

Report

Posted on by zachary.robinson@gt...

Hello,

Let me preface by saying I am a novice at best when it comes to D365FO, and exporting to our Azure SQL DB. I will try and be as thorough as possible.

I am currently attempting to export the entity 'Inventory transactions originator', which I can verify has data within it when utilizing an Odata.Feed with Power BI.

I am receiving the following errors when attempting to BYOD export this entity to our Azure SQL DB:

Cannot create a record in Inventory transactions originator (InventTransStaging). Reference: Sales order. The record already exists
Cannot create a record in Inventory transactions originator (InventTransStaging). Reference: Transfer. The record already exists
Cannot create a record in Inventory transactions originator (InventTransStaging). Reference: Counting. The record already exists
Cannot create a record in Inventory transactions originator (InventTransStaging). Reference: Purchase order. The record already exists
Cannot create a record in Inventory transactions originator (InventTransStaging). Reference: Transaction. The record already exists
Cannot create a record in Inventory transactions originator (InventTransStaging). Reference: Transfer order shipment. The record already exists
Cannot create a record in Inventory transactions originator (InventTransStaging). Reference: Transfer order receive. The record already exists
Cannot create a record in Inventory transactions originator (InventTransStaging). Reference: Project. The record already exists
Cannot create a record in Inventory transactions originator (InventTransStaging). Reference: Inventory adjustment. The record already exists

These are the summary of the errors received, I received multiple of each of the above listed.

Again, I can see the entity contains data when utilizing an Odata.Feed within Power BI, so not sure where the issue is occurring.

Any idea what could be causing this issue?

I have the same question (0)

All responses (19)

Answers (2)

Gunjan Bhattachayya 35,423 on at

Like (0)

Report

Hi Zach Robinson,

It looks like you are using a customized entity to export data. Please check the entity key defined and if there are any duplicate records (as per the entity key).

Was this reply helpful? Yes No
zachary.robinson@gt... 99 on at

Like (0)

Report

Gunjan, thank you very much for your response.

And, yes, I am utilizing a custom entity. How would I check the entity key that is defined?

Was this reply helpful? Yes No
Verified answer

Gunjan Bhattachayya 35,423 on at

Like (0)

Report

Hi Zach,

If you go to your custom entity, you should be able to check the entity key. For an example you can check the screenshot here.

Was this reply helpful? Yes No
zachary.robinson@gt... 99 on at

Like (0)

Report

Thank you Gunjan,

Below is a screenshot of the custom entity:

I am not seeing anything listed under EntityKey. Is there supposed to be something listed here? If so, what should be listed here?

Was this reply helpful? Yes No
Verified answer

Gunjan Bhattachayya 35,423 on at

Like (1)

Report

Hi Zach,

The entity key uniquely identifies a record of the data entity. You can check this link for details. Looking at the screenshot, you might try setting a combination of these fields- InventTransId, InventDimId, RecId (from InventTrans) as the Entity key for this entity and check if that works. You will need to make sure that the fields exist in the data entity so that you can add them as an Entity key.

Was this reply helpful? Yes No
zachary.robinson@gt... 99 on at

Like (0)

Report

Thank you for that.

I have added InventTransId (from InventTransOrigin), InventdimId (from InventDim), and RecId/RecId1 (from InventTrans, the Datafield property says it is called RecId, and the Name property says it is RecId1).

I have rebuilt the project and synchronized to database. What is the best way to ensure that this entity will work for export prior to me promoting this up to Production, and having to start over again? Is there a way to check if this will work in the dev environment first before requesting promotion?

Was this reply helpful? Yes No
Gunjan Bhattachayya 35,423 on at

Like (1)

Report

Hi Zach,

What I would normally do is to check the view created for the entity in the SQL DB and look for any duplicates there based on the fields in the Entity key. You should have a view named dbo.InventTransEntity_V1. You need to regenerate the staging table as well.

Was this reply helpful? Yes No
zachary.robinson@gt... 99 on at

Like (0)

Report

Thank you Gunjan. Regarding the staging table, in my previous edit of this entity recently, I had done a refresh/update of the staging table, and am not seeing that option available when I right-click on my project.

In Sql Server Managment Studio, on my initial query in of the custom entity/view that I created, InventTransEntity_v1, there are duplicates listed for the InventTransId.

The same query with RecId1 did not show any duplicates.

What should my next steps be?

Was this reply helpful? Yes No
Suggested answer

Gunjan Bhattachayya 35,423 on at

Like (1)

Report

Hi Zach,

You should open the data entity in the designer, right click and regenerate staging table.

In the Sql query, you should add the other two fields in the entity key to the group by clause as well and check for duplicates.

Was this reply helpful? Yes No
zachary.robinson@gt... 99 on at

Like (0)

Report

Thank you for that!

I have regenerated the staging table on my end, I was looking in the wrong place.

I also updated the query to reflect the three entitykeys together, and found no duplicates when all 3 are together, is this correct?

Was this reply helpful? Yes No