Microsoft Dynamics 365: Data Entity for a Table Without a Natural Key

Problem Definition 

Data entities in Microsoft Dynamics 365 for Finance and Supply Chain Management (formerly Finance and Operations) and can be regarded as an abstraction from the physical implementation of database tables. Implemented as de-normalized views (a data entity is actually stored as a view in SQL Server) with their own set of methods, they are now key elements in data import/export and integration scenarios. 

A data entity can be quickly created by adding a new item of type Data entity to the project or by right clicking on a table and selecting Create data entity Addins option. However, if the primary data source of the data entity does not have a unique index (default Rec Id index is not taken into account) you are expected to get the "The natural key not found" error message. 

For this example, a simple table, TestTable, has been created. The table contains three fields and does not have a unique index for now. 

Generating a new data entity for it ends up with the error shown below.

The error comes from the system requirement that every data entity must have a primary key defined. So, it is possible to uniquely identify each row of the data that is conveyed through a data entity. For instance, Data Management Framework always evaluates data rows coming in for uniqueness using the data entity primary key. If a data row already exists in the system, then it gets selected for update, otherwise the data is inserted. 

Most often, changing an already existing index on a table to being unique cannot be considered as a solution to the problem. This post is aimed at outlining several workarounds of how the issue can be overcome with "little blood". 

Solving "The Natural Key Not Found" Error 

There are three approaches you can take to solve “The Natural Key Not Found” Error. 

First Approach: Adding a new LineNum field. 

The first approach is based on adding a new LineNum field (incremental identifier of Real type) to a table and building up a new table unique index based on it. 

This makes a difference and allows generating the data entity by standard means of Visual Studio for Dynamics 365, for instance by Data entity wizard. 

Table TestTable should also be supplied with a piece of code for the LineNum field default initialization, similar to that in the screenshot below.

Additionally, if there is any data in the original table, SQL update script needs to be prepared and executed in order to correctly initialize the missing LineNum values and eliminate DB synchronization errors. 

Second Approach: Input columns uniquely identifies each row of the input data. 

The second approach is premised on the assumption that a predefined combination of input columns (or one column) can be used to uniquely identify each row of the input data consumed by a data entity. Data entity is much of a View object and its primary key may include different set of fields than that found in the unique index of its primary data source table. In other words, we can build up a data entity with a primary key that contains almost any valid field from its data source(s) and get it working properly.  

However, there are two important prerequisites: 

1) Combination of values in columns that correspond to the data entity primary key fields must be unique for each row of the incoming data. 

2) Incoming data must not violate data integrity enforced by the updated data sources that a data entity is built on. 

In our case, we’ll start with an assumption that the Name field (or combination of other fields) can be taken as a data entity primary key, even though it is part of the non-unique table index. Provided that incoming names are unique for each data row, we are not expected to have troubles during import. 

Standard "The natural key not found" validation check can be bypassed by using the following simple workarounds: 

• Take any existing data entity in the Application Explorer and create a new one by duplicating it (right clicking on the data entity and selecting Duplicate in project from the context menu). Rename and adjust/re-implement the newly created data entity by changing its data sources, fields, primary keys, properties, code, etc. Create the staging table and associate it with the data entity. 
• Start the Data Entity Wizard, specify appropriate properties but take any other simple table that has at least one unique index on it for the Primary datasource property to pass the validation check. 

Proceed with the wizard and then adjust appropriately the newly created data entity and staging table. 

Third Approach: Data entity primary key can be built on primary data source Recld field. 

The third approach is based on the fact that a data entity primary key can be built on the primary data source RecId field. Standard data entity General Journal Account Entry Entity is a vivid example of applying such a technique. 

Although that conceptually contradicts the main data entity paradigm, the workaround may be useful when there is a need to prepare a data entity for data import/export without troubling to much about changing the primary data source table or manipulating with a set of fields for the data entity primary key. 

Read more about how Enavate can support your ERP, specifically Microsoft Dynamics 365 Business Central, needs.