This article is a continuation of the discussion about the new workspace called Data Management in Microsoft Dynamics 365 for Operations (MsDyn365). Data Management replaces the Data Import and Export Framework (DIXF) Module that was available in AX 2012 R2 and above. This illustrates the real world process of creating new tables for legacy or new processes and being able to populate that table using the expanded data management tool.

The emphasis of this article is to introduce you to the technical process of creating a data entity that you can use for both the import and export of data. This is the version where we create a data entity from a table versus creating the data entity via the wizard.

To demonstrate this life cycle, we will create a table, generate a data entity, export the headers, insert data, and then import it back into Microsoft Dynamics 365. The assumption of this article is that you created a package, model, and project to contain your programming within Visual Studio in your own virtual image or Azure-hosted development environment. Ensure that your model has an association to the Fleet Management package. Please see Development Basics in Customer Source, or subscribe to our training sessions on basic programming if you are unfamiliar with this process.

Data Management in Dynamics 365: Creating a Data Entity from a Table

Create the Table

For the first operation, we will need to create the table to use as the basis for the data entity. Any table will work; for simplicity, we are creating one for this exercise. In this case, we used the Demo Dynamics 365 for Operations November 2016 release (previous versions will also work).

To Create the Table:

  1. Open Visual Studio as an administrator and create a package, model, and project to contain your objects.
  2. Right click your project, and click New > Add item.
  3. Select Data Model > Table.
  4. Enter rsmRentalVehicleTable for the name, and click Add.
  5. In the Application Explorer, drag and drop the following Extended Data types from the Data Types node. Once inserted, change the names and the labels to the following.
    • Note: On the RANumber, ensure that property Allow Edit is set to No.
Extended Data Type Name Label
AccountNum RANumber Agreement Number
AccountName RentalAccountName Account Name
AmountMST Amount Amount
TransDate RentalStartDate Rental Start Date
  1. Create the following new fields by right-clicking Fields and selecting New.
Primitive Data Type Name Label
Date ReservationDate Reservation Date
Integer NumDaysRented Number of Days
Integer VehicleID Vehicle ID
  1. Save all the new fields.
  2. Expand the Indexes node and right-click for New Index.
  3. Rename by right clicking and Rename to RANumberIdx
  4. Drag RANumber from the Fields node to the newly created Index.
  5. Save all.
  6. Right-click the rsmRentalVehicleTable.
  7. Enter Rental Vehicle in the Label
  8. Set the Primary Index to the newly created RANumberIdx.
  9. Save all and then click Build.
  10. Ensure that there are no errors. If you have an error resolve and rebuild.

Create the Data Entity

In this part of the process, we are generating the data entity. It not only creates a data entity, it also creates the staging table and security privileges needed for the entity use.

To create the data entity, follow these steps:

  1. Select the correct project in the Solution Explorer.
  2. In the center code editor, right-click the newly created rsmRentalVehicleTable.
  3. Click Addins > Create data entity.

datamgmt1

Create Data Entity

  1. Click OK.
  2. Save all and click Build. Ensure that there are no errors. If you have an error resolve and rebuild.
  3. Synchronize the project in order to ensure database integrity if you don’t already synchronize on project in your Visual Studio settings.

Exporting and Updating the Data Set

Exporting the Data Header

For the second major operation, you will update the information on the Excel spreadsheet so that you can reimport. The emphasis here is to not change any of the columns because these are exactly what the format will be on the import. If you were to change the columns, they you would need to use a different data entity or remap that entity to work.

To export the data header, follow these steps:

  1. Open Dynamics 365 for Operations and then click Show navigation pane.
  2. Under Workspaces, open Data Management.
  3. Click the Export
  4. Enter rsmRentalVehicleHeaderExport.
  5. Select Excel for in the Target Data Format
  6. Select or enter rsmRentalVehicleTableEntity in the Entity name
  7. Ensure that the Skip staging field is set to Yes.
  8. Select Yes in the Generate data package
  9. Select the rsmRentalVehicleHeaderExport.
  10. Click Download in the action pane.
  11. Click Open.
  12. Copy the rsmRentalVehicleTableEntity Excel file to the desktop (or another easily accessible location).

Update the Data Set

For this step, we will update the information on the Excel spreadsheet so that we can reimport. Again, the emphasis here is to not change any of the columns because these are exactly what the format will be on the import.  If you were to change the columns, you would need to use a different data entity or remap that entity to work.

To update the data set, follow these steps:

  1. Open the file xlsx from your desktop, if not already displayed.
  2. Click Enable Editing, if displayed.
  3. Enter new information on the spreadsheet, paying special attention to key fields so that you have unique values for like RANumbers.
datamgmt2

Click To Expand Image

  1. Repeat step 2 (the creation of new customers) so you have at least 7 new entries.
  2. Save and close the spreadsheet.

Importing the Data Set in to Dynamics 365

Import Process

For the third major operation, we will import the data back in to Dynamics 365.  Since we created the data entity specifically for this operation, we have a perfect data set match. Not all mappings will occur this way and we will delve into complex data set imports in a future article.

To import the modified data set, follow these steps:

  1. Open Dynamics 365 for Operations and click Show navigation pane.
  2. Under Workspaces, Open Data Management
  3. Click the Import
  4. Enter rsmRentalVehicleImport in the Name
  5. Select Excel in the Source data format
  6. Select rsmRentalVehicleTableEntity in the Entity name
  7. Click Upload.
  8. Select rsmRentalVehicleTableEntity and then click Open.
  9. Select Sheet1$ (or name of sheet in your Excel item) in the Sheet lookup
  10. Click Yes to generate the mapping from scratch.
  11. Click View map.
  12. This should have mapped exactly. If not, map accordingly if there were any changes.
  13. Close the form.
  14. Click Import on the action pane.
  15. Click View staging data to see imported items.
  16. Click Copy data to target.
  17. Click OK.
  18. Click Run.

To check the import of the data set, follow these steps:

  1. Open Visual Studio as an administrator.
  2. Select the rsmRentalVehicleTable and click Open.
  3. Right-click the table and select Table Browser.
  4. Log in, if necessary.
  5. Validate that the data was imported.

Conclusion

This article reviewed the Data Management Data Entity creation and use. We created a table and generated a data entity, changed the data set to simulate the update process, and covered the import process.  We also covered the complete lifecycle of Data Management and a custom data entity to illustrate that you can populate new table structures quickly and effectively using these tools. In addition, the potential for your use of these process in your operation expand the ability to automate data updates that range from very simple to complex.

For more information, I would recommend “Data entities and packages framework” on the ax.help.dynamics.com, our Dynamics 365 live training, or our on-demand online training to learn more. Plus, keep an eye out for more articles that dive into the technical aspects of using the data management and SQL.

Please visit academy.rsmus.com for more information and training materials that will cover this and hundreds of other topics for AX2012 and Dynamics 365! Or contact our dynamics pros experts. 855-437-7201

by Shaun McMikle for RSM