One of the great features of Dynamics 365 is the ability to import records into the system. Import in Dynamics 365 Customer Engagement can be used for creating new records, as well as updating existing records.
This article will focus on three different import scenarios:
- Importing new records
- Updating records with import
- Importing records with non-typical entity look-ups
Importing Data in Dynamics 365
While imports can be used for creating new records, as well as updating existing records, this section will focus on the ability to import new records.
Import File Options
When importing new records into Dynamics 365, there are two options for the source file:
1. Produce your own file: Generally, this would be a CSV (or other delimited) file with your own headers. In an ideal world, you will want to make sure the column headings match your field names in Dynamics 365. When walking through the import process, Dynamics 365 will try to automatically map fields based on this information. Some up-front work on this will save you time when manually mapping these fields.
2. Use an import template: In Dynamics 365, you can download import template from: Settings > Data Management > Templates for Data Import. When using one of these files, do not remove columns A, B, or C, the system uses this data to know what records you are importing into the system. For ease of use, you may want to delete any columns representative of the fields for this entity in Dynamics 365 to make the file easier to use. Just leave columns A, B, and C alone!
Once your import file is complete, navigate to: Settings > Data Management > Imports > Import Data. This will launch a wizard-esque interface, allowing you to upload your data file, define field mappings, and then import.
The import feature will provide you with the total number of records, the number of successes, the number of partially imported records, and the number of failures. To further diagnose failures, the system provides detailed insight into why any individual row failed. If/when an issue is encountered, Dynamics 365 will fail on that row, and proceed to the next, allowing the import process to continue, and not immediately fail the remainder of the records in the import file.
Below are two of the most common issues experienced during the data import process.
1. Look-Ups: If using standard mappings, the import process in Dynamics 365 will attempt to resolve look-up fields referenced in the file based on the entity’s name field. This will work anywhere except for where the data is duplicated, such as Accounts with the same name, or the data does not exist in Dynamics 365.
If you do have records with the same name, but these are not true duplicates, the import process has a method where you can resolve these look-ups against some other distinct record ID. I would say it is also not uncommon to create fields specifically for this.
2. Option Sets: Any value within your source file must match the option set values in Dynamics 365. If using an import template produced from Dynamics 365, this business logic is built in. If using a CSV file, the values in the file must align. If the values are different, you will need to map the values to a corresponding value in Dynamics 365 when defining your field mappings.
When importing data, you will want to consider any automations which may exist for your data. These considerations may be Business Rules, Processes/Workflows, or other functionality driven by custom code. You also want to take a back-up of your Dynamics 365 database prior to importing, just in case any unanticipated data issues arise.
1. Business Rules: If you are driving field values based on other field values but have not imported all data in alignment with these Business Rules, your users will likely receive a message opening any record as the Business Rule triggers for the proper field update. Users would then be prompted to save the record.
2. Processes/Workflow: You may have Processes/Workflows which trigger on the creation of new records. You may want to take this into consideration, inactivating Processes/Workflows which you do not want to trigger.
Updating Records with Dynamics 365 Import
A common requirement is the ability to update records via an import within Dynamics 365. Fortunately, Microsoft has provided this functionality for quite some time. However, using this feature requires the data to be in a very specific format.
How to Update Records with Dynamics 365 Import
Step 1: Data Extraction
For the first step, it is vital the data is exported from the Dynamics 365 application using the Excel export features available. If any of your Views (System or Personal) include the column(s) of data needed for the update, you can navigate to this View and select the Export to Excel option. If your Views do not contain the column(s) of data needed for the update, the best option is using Advanced Find for this, as you can easily add the column(s) of data needed. Your other option is to create a System level View, but this may require some additional coordination for development and deployment.
Exporting Data from a View:
Exporting Data from Advanced Find:
Step 2: Data Update in Excel
Once your data is in Excel, you can update the data as needed. Keep in mind, you need to make sure data alignment exists for any option-sets, and/or look-up values in the system. Once you have the data updates needed, save this file.
It should be noted, in this Excel file, columns A, B, and C should not be updated. These columns contain critical information needed for re-importing the data. Make sure this data does not become misaligned with the rest of the dataset in the Excel file. These columns are hidden by default.
Columns A, B, and C – Do not modify this data:
Step 3: Import of Data
Prior to importing data, I would recommend securing a back-up of your Dynamics 365 instance, just in case the import has unexpected results. Within Dynamics 365, there are several ways to access the import process. I generally navigate to: Settings > Data Management > Imports. Select Import Data.
Proceed through the wizard. When you get to the window ‘Review Settings and Import Data’ (shown below), it is critical the window displays the message: This action will update existing records, and if required, create new records. If this message is not displayed, stop. If this message is not displayed, chances are high new records will be created, and the update will not occur. Continue through the rest of the import wizard.
Importing Records with Non-Typical Entity Look-Ups
In Microsoft Dynamics 365, there may be times when you need to import child records in a one-to-many (1:N) relationship which include a relationship to a parent record, but instead of having access to the Primary Field used by Dynamics 365 for the parent record, you have access to some other identifier such as an Account Number or ID. The example I’ll provide works when importing child records with a parent association. The Primary Field in Dynamics 365 is not available, but a different distinct parent identifier is available.
First, we need to understand what Dynamics 365 uses for the Primary Field for the import, as the import will try to resolve against this for the parent entity. For this, we will be looking at the relationship between an Account, and its related Contacts. From a Contact record, this relationship is established by the Account Name field.
In the screenshot below, you can see how this 1:N relationship between an Account and its Contacts are made:
The data displayed in the Account Name field on the Contact is coming from the corresponding Primary Field for the Account, which is the Account Name field.
To determine what data is expected from the system for this, open a solution (shown below) containing this information; it may be easiest to use the default solution.
- Select the entity representing the parent record, in this case, Account.
- Next, select the Primary Field tab. This tab displays the field the import process will try to resolve against.
Using this information, if we have an Account with Account Name: ACME, when we try to import Contact records, and make an associated to an Account, the import process expects ACME to be in the Contact file for Company Name field.
When this Account Name is not available, and instead you have some other Account Number or ID, such as ACME001. With this distinct value, in both our Contact file, and already existing on the Account record, we can still successfully import the Contacts with this Account relationship, we just need to go through some extra steps.
For this, we have ACME set-up as an account, with their Account Number/ID: ACME001 stored in the Account Number field. Our Contact file contains the following data:
|Company Name||First Name||Last Name|
Steps to Import Data with Non-Typical Entity Look-Ups
Step 1: To import this data, navigate to: Settings > Data Management > Imports, and click the Import Data button.
Step 2: On the Upload Data File window, select your Contact file and click Next. On the Review File Upload Summary window, make any adjustments and click the Next button.
Step 3: On the Select Data Map window, map an appropriate selection, I usually use System Data Maps – Default (Automatic Mapping). Make an appropriate select and click the Next button.
Step 4: On the Map Record Type, select the appropriate record. In our scenario, this would be Contact. Make an appropriate select and click the Next button.
Step 5: This is where we depart from the normal process. On the Map Fields window, we need to take a step to map our Account ID (in the Company Name field in our file) to the corresponding Account field: Account Number. For this, click the icon adjacent to the drop down for the standard mapping:
On Account, use the look-up to open the Select Fields window. Unselect all fields, and then select your Account Number/ID field, in our case: Account Number. With this selected, click OK to return to the previous window. Click OK to close. Back on the Map Fields window, click the Next button.
Step 6: Proceed through the rest of the import wizard windows.
Below, you can see our successfully imported Contact, with the Account relationship:
While the walkthrough was for a Contact, and the association to Account, this same methodology can be extended to other scenarios in the system.
Note: While the Dynamics 365 import tool is incredibly easy to use, always secure a back-up of your data prior to importing.
Dynamics 365 CE (CRM) How-To eGuide
41 pages of step-by-step instructions for 6 different key tasks in Dynamics 365 Customer Engagement (CRM). Includes interactions with PowerApps and Flow!Get the eGuide