Finance | Project Operations, Human Resources, ...

Natural key for entities

(1) Share

Report

Posted on by ..

2,009

I have a customer table that have an index with allow with duolicates true..I don't have another index

So I'm guess a natural key is an index with allow duplicates false, correct?

So when I decided to create an entity for this table, I got the natural key error. Can I create the entity manually to ignore this error. Or is there sth better that I need to do with my custom table?

I have the same question (0)

All responses (10)

Answers (0)

Sort by

Suggested answer

Gunjan Bhattachayya 35,429 on at

Like (0)

Report
Copy link

Link copied!

Hi IntegrationBeginner,

You are correct. A natural key is one with Allow duplicates set to "No". For a table with information regarding customers, you should have an index based on a field so that there are no duplicates.

Is your data entity going to be read only, or are you going to use it to insert/update data as well? In case you need to insert/update data using this data entity, you need to set the fields in the entity key based on which new records will be inserted or existing records will be updated.

Was this reply helpful? Yes No
Alex VN 1,994 on at

Like (0)

Report
Copy link

Link copied!

Hi,

I think this document can give you a detail explanation about the limitation and also way to by-pass it if needed:

www.enavate.com/.../microsoft-dynamics-365-data-entity-for-a-table-without-a-natural-key

Regards,

Was this reply helpful? Yes No
.. 2,009 on at

Like (0)

Report
Copy link

Link copied!

Hi Gunjan,

Currently my table contains 5 fields.

The table has an index that contains 3 fields with duplicate true

And in the validate write I check that the 5 fields should not be repeated.

I will use the entity for import and export.

So do you mean the entity key should contain all 5 fields?

I did notice that this entity I made is currently updating records but I didn't know why.

Was this reply helpful? Yes No
Suggested answer

Gunjan Bhattachayya 35,429 on at

Like (0)

Report
Copy link

Link copied!

Hi IntegrationBeginner,

Which field(s) have you mentioned in the Entity Key for your data entity? Based on these fields only data will be inserted/updated. If your index has 3 fields with duplicate - true, these should not be a part of your data entity Entity key.

For an example, in CustCustomerV3Entity has CustomerAccount field as the entity key.

When a record being imported using this entity, if it has an existing customer account number, the customer record is updated. If the customer number does not exist, a new record is created. Similarly, for your entity, you need to decide the field(s) which will decide whether records should be inserted/ updated.

Was this reply helpful? Yes No
.. 2,009 on at

Like (0)

Report
Copy link

Link copied!

Hey Gunjan,

Currently the 3 fields with allow duplicate true are the entity key.

So since the combination of the 5 fields should be unique as this what I added in the validate write method of the table..then I should add the 5 fields to the entity key correct?

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

Like (1)

Report
Copy link

Link copied!

Hi IntegrationBeginner,

That is correct. If the combination of the 5 fields is unique, they should in the Entity key.

Was this reply helpful? Yes No
.. 2,009 on at

Like (0)

Report
Copy link

Link copied!

Hey Gunjan,

If I import to this entity these two records

Field1 field2 field3 field3 field5

A E C F P

A T C F P

They get inserted to the table correctly.

However if I import the first record first..then import the 2nd record later

It updates the first record by changing field2 from E to T

Instead of importing a new record...why is that?

Is it because the table has no unique index?

Was this reply helpful? Yes No
Martin Dráb 239,650 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

It's because you have a wrong relation between the staging table and the entity, and maybe a wrong entity key too.

Indexes on the table aren't relevant in this scenario, although having no unique key is indeed wrong.

Was this reply helpful? Yes No
.. 2,009 on at

Like (0)

Report
Copy link

Link copied!

Hi Martin,

What should I do then? currently the entity key is all five fields as was suggested to make sure no update occurs and the staging table has a relation with all these five fields. And the index of the staging table also contains these 5 fields

Was this reply helpful? Yes No
Martin Dráb 239,650 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

If the relation betrween the staging table and the entity have all these fields, this version of code is deployed to the environment where you're testing it and you really have a different value of one of the field in the staging table, the database query that looks for records to update wouldn't find the record with different field values. Something is wrong in your environment, and most likely it's about one of the assumptions I've just mentioned.

Was this reply helpful? Yes No