Data Entity Export failing: Cannot create a record in staging table, The record Already exists.

(1) Share

Report

Posted on by alinawaz

150

Hi Community Experts,

I have one custom data entity which has multiple data sources. I have recently added one more data source to apply a Range and afterwards the entity is failing in DMF export project.

Now when I try to use this Entity in a DMF export project (Excel as output format) it is giving following error for around 60+ positions

I have tried the following to solve this issue:

Regenerated the staging table
Checked the index on staging table, its also intact.
Restarted the AOS service.
Full build/Sync the model.
Ran the following query on data entity to check for duplicates
SELECT TOP 1 COUNT(RECID) AS KEYCOUNT
FROM HRORGCHARTEXPORTENTITY
GROUP BY POSITIONID

None has worked so far.

Looking forward to helpful responses on this issue.

All responses (9)

Answers (0)

Jonas "Jones" Melgaard 701 on at

Like (0)

Report

Data Entity Export failing: Cannot create a record in staging table, The record Already exists.

Hmm... That's interesting.

Unless other chip in, I'd have a look at the data in the view that F&O generated for the entity, and see if there is any index conflicts for the positions it's complaining about.

These are some of the views F&O generates in the standard application for entities.
alinawaz 150 on at

Like (0)

Report

Data Entity Export failing: Cannot create a record in staging table, The record Already exists.

The problem stands still.

It is failing while exporting.
alinawaz 150 on at

Like (1)

Report

Data Entity Export failing: Cannot create a record in staging table, The record Already exists.

I actually don't need those fields in the field list. I had to add them in the Field list of data entity because without having those fields in the list the entity was not building and errors were present. So after I re-generated the staging table these fields came in the staging table too.

Now adding the fields in index of staging table only and changing the join mode back to "InnerJoin".

Lets see if this builds and Syncs , and afterward I will try to export entity again.
Suggested answer

Jonas "Jones" Melgaard 701 on at

Like (0)

Report

Data Entity Export failing: Cannot create a record in staging table, The record Already exists.

Okay, you are using fields from the joined table, based on the error message.

Then you just need to add the "ValidFrom" and "ValidTo" fields to the index of the staging table and keep the join mode as InnerJoin. Otherwise you will get index conflicts.
alinawaz 150 on at

Like (0)

Report

Data Entity Export failing: Cannot create a record in staging table, The record Already exists.

Changed the join mode of the datasource (HcmPosition). Now its not building.
Jonas "Jones" Melgaard 701 on at

Like (0)

Report

Data Entity Export failing: Cannot create a record in staging table, The record Already exists.

Yep, I added another suggestion (-:

Think the community board had an issue, could not see the text of your question.

But definitely something to do with the join mode of the datasource you added. It needs to be a binary join (ExistsJoin).
alinawaz 150 on at

Like (1)

Report

Data Entity Export failing: Cannot create a record in staging table, The record Already exists.

Hi Jonas,

Can not skip staging as their is code written on PostLoad method.

And here's the index situation for entity and staging table
Suggested answer

Jonas "Jones" Melgaard 701 on at

Like (0)

Report

Data Entity Export failing: Cannot create a record in staging table, The record Already exists.

You have added more information to the question, so it makes more sense now. (-:

You need to change the JoinMode of HcmPositionDuration to ExistsJoin from InnerJoin. If you have more durations, it will duplicate records.
Suggested answer

Jonas "Jones" Melgaard 701 on at

Like (0)

Report

Data Entity Export failing: Cannot create a record in staging table, The record Already exists.

Hi alinawaz,

You can skip staging, but it's not a good solution to your problem.

Ideally you should identify why there is an index issue (I.e. is the primary index of your staging table identical to that of the data entity etc).