How to Fix “Natural Key Not Found” When Creating a Dynamics 365 Data Entity

Creating a data entity for a table without a unique index in Dynamics 365 Finance & Operations

When creating a Dynamics 365 Finance and Operations data entity, developers often encounter the frustrating “Natural key not found” error. This typically happens when the source table does not contain a unique index required for a data entity primary key.

In this guide, we’ll explain why Dynamics 365 requires a natural key, and walk through three practical approaches for creating a data entity for a table without a natural key.

Quick Answer: Dynamics 365 Natural Key Error

Error:
“The natural key for the table was not found.”

Cause:
The source table does not contain a unique index required for a Dynamics 365 data entity primary key.

Fix Options:

1. Add a unique LineNum field to the table
2. Build a composite primary key from input columns
3. Use the RecId field as the data entity primary key

Who This Article Is For

This article is intended for:

• Dynamics 365 Finance & Operations developers
• ERP technical consultants
• Integration specialists
• Teams working with the Data Management Framework

What Is a Data Entity in Dynamics 365 Finance & Operations?

A Dynamics 365 data entity is a de-normalized SQL view used for data import, export, and integrations through the Data Management Framework.

Data entities make it possible to move data in and out of the system without directly interacting with underlying tables. Because of this, each data entity must have a primary key that uniquely identifies records.

Why You Get the “Natural Key Not Found” Error

When using the data entity wizard, you may see the error:

“Natural key for the table was not found.”

Dynamics 365 requires every data entity to have a primary key so incoming data can be matched to existing records during import or integration.

If the source table lacks a unique index, the wizard cannot determine how records should be identified, which results in this error.

This situation is common when working with custom tables or legacy designs that were never intended for integration scenarios.

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

How to Fix the Dynamics 365 Natural Key Error

Below are three proven approaches to creating a Dynamics 365 data entity without a natural key.

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. 

Choosing the Right Approach

Each option has tradeoffs:

The right choice depends on your Dynamics 365 Finance & Operations development goals and integration needs.

Need Help With Dynamics 365 Development?

If you're working through data entities, integrations, or migrations, our team regularly supports organizations with:

• Dynamics 365 Finance & Operations development
• Data migration and integration projects
• ERP modernization initiatives

Learn more about our Dynamics 365 services and support.

FAQ: Dynamics 365 Data Entity Natural Key

What is a natural key in Dynamics 365?
A natural key uniquely identifies each record in a data entity so the Data Management Framework can determine whether to insert or update records.

Can a Dynamics 365 data entity use RecId as a primary key?
Yes. RecId can be used as a practical workaround when a table lacks a unique index.

Why does a data entity require a primary key?
The Data Management Framework uses the primary key to determine whether incoming records already exist or should be inserted.

What happens if a table has no unique index?
You must create a workaround such as adding a unique field, building a composite key, or using RecId.

What causes the “Natural key not found” error?
This error occurs when the data entity wizard cannot find a unique index to use as the primary key.