How To Use A D365 Data Entity

In the last couple articles, you learned what a data entity is, and how to create one. Now, learn how to test and use a D365 data entity to read or write data in Microsoft Dynamics 365 for Finance and Operations.

Review

Before showing you the steps to use a D365 Data Entity, as a reminder, there are multiple ways that a Data entity can be used. These are the three integration scenarios:

1. API Interface (OData)
2. File Import
3. Business Intelligence

Additionally, data can be loaded initially as part of an Application Lifecycle Management project.

See my data entity overview article for more information.

How, let us walk through a few examples.

Validate Data Entity

Before, we use a D365 data entity, we need to validate the system sees it. Especially if we just created a new one.

First, go to the System Administration>Workspaces>Data management. Or, search for ‘Data management’ in the search bar.

Second, click on the ‘Data entities’ tile.

Third, validate the data entity whose data you wish to export is in the list. In the last article, How to Create A D365 Data Entity, we created the entity named ‘FMLabCustomerEntity’. Feel free to follow those instructions first if you want to follow along exactly. Otherwise, you can pick a different data entity that already exists in your environment.

Fourth, now that you have chosen the data entity, close this form, by clicking the x in the top right of the form.

Export Data Using A Data Entity

Now, use a D365 data entity to export data to an excel file.

Create Export Project

First, go to the System Administration>Workspaces>Data management. Or, search for ‘Data management’ in the search bar.

Second, click the ‘Export‘ title on the Data management workspace.

Third, enter a value in the ‘group name‘ field, such as ‘FMLabCustomer Export‘. Leave the ‘Data project operation type‘ field to the default value of ‘Export‘. Then, click the ‘Add entity‘ button, in the ‘Selected entities‘ tab.

Fourth, in the dialog that opens up, select the data you wish to export in the field ‘Entity name’.

Fifth, select the value ‘Excel‘ in the ‘Target data format‘ drop down. Notice, there are many file types you can select. In this example, we are using Excel.

Sixth, click the ‘Add’ button on the dialog. A new record will be added to the grid. However, the dialog will stay open. Click ‘Close’ to close the dialog form.

Finally, click the ‘Export now’ or ‘Export in batch’ button in the top bar of the form.

Export Now

To clarify, allow me to briefly explain the differences between Export Now and Export in Batch.

Push the ‘Export now’ button if you expect the export process to not require much time. You can choose this option if you know there is not much data in the tables. This will cause your browser to wait for the process to finish, and then update your screen when finished.

After the process completes, you will see the following screen pop up. Click the ‘Download file’ button to download the file.

Export in Batch

Instead, push the ‘Export in batch’ button to run the process on the batch server. I recommend choosing this option in most cases. This is because the export process could take quite a while to run.

The browser will open up an Execution summary screen right away. However, you are still free to navigate to other forms while you are waiting for the export process to finish.

The batch job will run in the background. Initially, the ‘Execution status‘ will show as ‘Not run‘.

Push the ‘Refresh‘ button to cause the form to update the ‘Execution status‘ with the current state of the batch job. Once the batch job starts, the Execution status will change to ‘Executing‘.

Again, you can push the ‘Refresh‘ button. If the batch job has completed, the Execution status will change to ‘Succeeded‘. Click the ‘Download file’ button to save the excel file.

Open, the file from the ‘Downloads’ folder.

In our case, the FMCustomer table did not have any records to export. Therefore, there are no rows in the excel file.

Let us fix this by uploading some data.

Import Data

Similar to the last section, we can also use a D365 data entity to import data from a file.

Enter Data In File

First, open the excel file that you downloaded in the last section.

Second, enter in customer information you wish to import.

Note, there exists some validation on address information. Therefore, it is important you enter in a valid, city, state, zipcode, and country. To ensure I had valid data, I went to the Accounts receivable>Customers>all customers form, selected the first record, and looked an their address.

Additionally, I looked at their customer group.

Third, the data I entered looks like this:

Create Import Project

Now, that we have a file, we can use D365 to import it.

First, go to the System Administration>Workspaces>Data management. Or, search for ‘Data management’ in the search bar.

Second, click the ‘Import’ tile.

Third, set the ‘Group name’ value. In this example, I set it to ‘FMLabCustomer Import’. Leave the Data project operation type to ‘Import’. Then, click on the ‘Add file’ button, under the ‘selected entities’ section.

Fourth, in the dialog that opens, set the ‘Entity name‘ to the data entity you are using to import’. In this example, I set it to ‘FMLabCustomerEntity‘. Next, set the ‘Source data format‘ to ‘Excel‘. Also, uncheck the ‘Skip staging‘ checkbox. Then, click the ‘Upload and data‘ button, and select the file you just populated with data.

After the file is uploaded, you will see a record added to the grid. You can click the ‘Close’ button on the dialog.

Validate Mapping

Whenever you import a file, it is important that the correct file columns get inserted into the right fields in the data entity.

Since, we first exported a file first, the mapping to import this field should be correct. We can confirm this by clicking on the ‘View map‘ button. Click on the ‘View map‘ button in the import project.

The system will open the ‘Map source to staging‘ form. Review that each source field is mapped to the correct field on the data entity staging table.

Adjustments to this mapping can be made by clicking on the ‘Mapping details’ tab.

Once you have confirmed the mapping, close this form.

Import The File

Finally, we can import the file. The specified D365 data entity will correctly map the columns in the file to their destination tables and fields.

Click on ‘Import now‘, ‘Import in batch‘, or ‘Re-import‘ in the top button bar.

Similar to the export functionality, the ‘Import now‘ button will run the import process while your browser waits.

Whereas, the ‘Import in batch‘ will create a batch job and run the process asynchronously. You can then push a ‘refresh‘ button to update the browser with the current status of the batch job.

Error

Unfortunately, sometimes the system is unable to import the data you specified. Often times, this is because you entered data that has a different data type than the destination table requires. Such as entering in a character such as ‘a’ in a field that can only accept numbers.

Other times, it is because the system is validating that values you specified exist in related tables. This is the case for this example.

When an error occurs, click on the ‘View execution log‘ button to see the specific errors.

Additionally, click on the ‘View staging data‘ button to view the data you are importing.

In the ‘Staging data’ form, I can click on the ‘Validate all‘ button to get a complete list of issues.

I receive these messages:

The value '94115' in field 'ZIP code' is not found in the related table 'FMAddressTable'.

The value 'CA' in field 'State' is not found in the related table 'FMAddressTable'.

The value 'USA' in field 'Country/region' is not found in the related table 'FMAddressTable'.

The value 'Oakland' in field 'City' is not found in the related table 'FMAddressTable'.

The value '555 Main Street' in field 'Address 1' is not found in the related table 'FMAddressTable'.

Results. The value '30' in field 'Customer group' is not found in the related table 'FMCustGroup'.
Results. validateField failed on field 'FMLabCustomerEntity.CustomerGroup'

I am receiving these error messages because I have not imported any sample data into these tables.

Import Fleet Management Demo Data

To use the Fleet management tables, I first need to import the demo data.

First, go to ‘Fleet management>Setup>Fleet setup‘. Or search for ‘Fleet setup‘ in the search bar.

Second, click on the ‘Create’ button on the ‘Data setup’ tab.

You can also follow Microsoft’s documentation here.

Update Excel File and ReImport

After bringing in the sample data, first, go back to my excel file, and update the file to have valid values that exist in the FMAddressTable, and FMCustGroup table.

Second, go back to the data management workspace, and reimport the Excel file.

Notice, the file imports without errors:

Third, go to Fleet management>Customers>Customer form. Notice, the data we specified in the file now exists in this table. Yay, it worked!

Microsoft Import File Documentation

For more information, Microsoft provides an example of how to import data using a csv file.

API Interface (OData)

Furthermore, another common type of integration is an API Interface. This uses an web call to talk to an OData endpoint.

In order to learn how to do this follow these two articles I wrote.

This first article explains how to setup a Azure application, client ID and secret to authorize external systems to communicate with Microsoft Dynamics 365 for Finance and Operations.

Use Postman To Call D365 Data Entities

This second article, explains how to use Postman, a free web request tool to call data entities.

Setup Postman To Call D365 Data Entities

Additionally, see this example from Microsoft’s documentation on how to consume entities by using OData.

Conclusion

It is important for everyone to learn how to use a D365 data entity. Data entities allow users to quickly import and export data from within Microsoft Dynamics 365 for Finance and Operations. Additionally, the flat structure of the data entities allows user to just focus on the field names and mappings. This is way easier than having to understand all of the related tables that are needed to fully make the data work within the system. The data needed to relate these tables is handled for us by the data entity, both when importing and exporting. Users who know how to use the data management workspace can create data a whole lot faster than they could by entering data by hand.