Finance | Project Operations, Human Resources, ...

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

(1) Share

Report

Posted on by alinawaz

209

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.

I have the same question (0)

All responses (19)

Answers (0)

Suggested answer

Jonas "Jones" Melgaard 4,926 Most Valuable Professional on at

Like (0)

Report

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).

Was this reply helpful? Yes No
Suggested answer

Jonas "Jones" Melgaard 4,926 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
alinawaz 209 on at

Like (1)

Report

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

Was this reply helpful? Yes No
Jonas "Jones" Melgaard 4,926 Most Valuable Professional on at

Like (0)

Report

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).

Was this reply helpful? Yes No
alinawaz 209 on at

Like (0)

Report

Changed the join mode of the datasource (HcmPosition). Now its not building.

Was this reply helpful? Yes No
Suggested answer

Jonas "Jones" Melgaard 4,926 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
alinawaz 209 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
alinawaz 209 on at

Like (0)

Report

The problem stands still.

It is failing while exporting.

Was this reply helpful? Yes No
Jonas "Jones" Melgaard 4,926 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Waed Ayyad 9,039 Super User 2025 Season 2 on at

Like (1)

Report

Hi,

What is the relationship with HcmPositionWorkerAssignment table?

Thanks,

Waed Ayyad

Was this reply helpful? Yes No