Talk With an Expert
    December 28, 2023

    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. TestTable with three fields and does not have a unique index.

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

    Generating a new data entity for TestTable.

     Screenshot of error when generating a new data entity the TestTable, "The natural key for the table TestTable was not found."

    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. 

    Screenshot of Adding a new LineNum field to the TestTable.

    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. 

    Screenshot demonstrating how adding LineNum to TestTable makes a difference and allows generating the data entity by standard means of Visual Studio for Dynamics 365.

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

    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. 
    In the Data Entity Wizard, select a table that has at least one unique index for the Primary datasource property.

    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. 

    Example of applying the data entity primary key built on the primary data source RecId field in the standard data entity GeneralJournalAccountEntryEntity.

    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.

    Yahor Litvinau

    Yahor is a proficient, 100% client-focused software development engineer and technical consultant with over 14 years of extensive experience in Microsoft Dynamics. He has been actively engaged in modeling and implementation of different ISV and VAR solutions on Dynamics AX (4.0 - 2012) and Dynamics 365 for Finance and Operations platform, delivering high-quality software that meets business needs. Yahor has huge experience in requirements analysis, software architecture design, business logic development and code performance optimization. Being a certified Dynamics AX / D365FO professional he has been working on a number of diverse European and North American projects throughout these years. Yahor has accumulated vast knowledge and experience that allows him to act as a subject matter expert on technical implementations, helping the team to squeeze the utmost out of Dynamics capabilities. His daily responsibilities involve designing, developing, supporting and integrating Dynamics AX / D365FO with other systems, leading technical implementations and mentoring junior staff. Always ready for self-improvement, able to effectively work either independently or in a multicultural team environment.

    Enavate Recent Posts

    February 2, 2024

    2024 Distribution Technology Trends

    Anybody who was paying attention knows what the biggest tech buzzword of 2023 was: AI. Recent innovations in machine learning and large language models led to a breakthrough... Read More
    February 1, 2024

    Microsoft Dynamics GP Year-End Close: Tips & Tricks for U.S. Payroll

    Conducting your Year-End Close in Microsoft Dynamics GP can be complex— if you don’t know all the ways to streamline it. At Enavate, we put together a webinar series and... Read More
    February 1, 2024

    Microsoft Dynamics GP Year-End Close: AP & 1099 Processing

    Knowing how to close out your Microsoft Dynamics GP instance can be difficult if you don’t know best practices and how to streamline the process. At Enavate, we put together... Read More

    Subscribe to Receive Email Updates