Posted by Nick Schlepp
Have you ever tried to import N:N Relationships in Microsoft Dynamics CRM?
If so, you probably found that there isn’t an easy way to do it. Unless you have a tool, you are stuck with either writing code or inserting into the database to handle import N:N data.
Fortunately, one of the free utilities in the XRM Toolbox comes to the rescue.
Have you ever tried to import N:N Relationships in Microsoft Dynamics CRM?
If so, you probably found that there isn’t an easy way to do it. Unless you have a tool, you are stuck with either writing code or inserting into the database to handle import N:N data.
Fortunately, one of the free utilities in the XRM Toolbox comes to the rescue.
In the scenario below, our sales and marketing team has pulled a list of our accounts and matched them up with their corresponding NAICS Codes. As Office users tend to do, they pulled our account list into Excel and matched them up with the NAICS Codes. They now have this fantastic list, want it in CRM, but don’t want to key it all.
In this post, I am going to walk through how to use the Import/Export NN Relationships utility to import this data.
How to import N:N relationships
1. Download and extract the XRM Toolbox. This is a suite of free tools that can make quite a few things easier in CRM. It can be found here:https://xrmtoolbox.codeplex.com/
2. Launch the XRM Toolbox
3. Click Connect to your organization
2. Launch the XRM Toolbox
3. Click Connect to your organization
4. If you have previously used the XRM Toolbox, you can pick your connection from the list, otherwise create a new one (your CRM administrator should be able to provide you with your connection information)
5. Once connected, scroll down and select the Import/Export NN Relationships tool
6. Once it opens, click the Load Metadata button (this may take a minute).
6. Once it opens, click the Load Metadata button (this may take a minute).
- If you get a Warning stating “No many to many relationships found for this entity!”, that's okay. It will default to the first entity it finds (sorted alphabetically). That entity may or may not have N:N relationships
7. Select the First Entity value. In this test case, we are going to relate existing Account records to a custom entity called NAICS Code. We will select Account as the First Entity
8. Under Relationship we will select the appropriate N:N relationship
9. Under Second Entity, it will display the other side of the N:N relationship
10. Next, we will change the Mapping Attribute options. Unless you know the list of GUIDs assigned to each record, you will probably want to change this to look up based on another known value, i.e. Account Number
11. Repeat this process for the second entity. In the example below, I selected the NAICS Code instead of its GUID
8. Under Relationship we will select the appropriate N:N relationship
9. Under Second Entity, it will display the other side of the N:N relationship
10. Next, we will change the Mapping Attribute options. Unless you know the list of GUIDs assigned to each record, you will probably want to change this to look up based on another known value, i.e. Account Number
11. Repeat this process for the second entity. In the example below, I selected the NAICS Code instead of its GUID
Next, you will need to build your file. This requires a semicolon delimited list. The first column will be the value that ties to the First Entity. The second column will be tied to the Second Entity.
- Our Excel file looks like this:
- Remove the column header as the Import/Export NN Relationships utility will attempt to import all of the rows as data
- Then convert it to a semicolon delimited file. In order to convert it to a semicolon delimited list, we can follow the steps outlined here. In their example, they use the pipe character, you will just need to enter a semicolon instead. Don’t forget to change this setting back once you have exported your file!
- If we look at the file we just saved in Notepad (or your favorite text editor), it should look like the image below
13. Now that we have our file, click the Browse button and select it
14. Then click Import NN Relationships to import your file, the results will be displayed in the Log window
14. Then click Import NN Relationships to import your file, the results will be displayed in the Log window
15. If we go look in CRM, we can now see that the Account is associated with the NAICS Code! If something goes wrong, it will tell you which line is having a problem and which part of the lookup failed
Local Microsoft Dynamics Partner Charlotte | NC | SC
InterDyn Artis is the premier Microsoft Dynamics partner for Charlotte and the Carolinas. If you're looking for CRM, GP (formerly Great Plains), AX or a customized solution, reach out to us - we've been doing this for 25 years, have a Microsoft Silver certification for Dynamics CRM, and specialize in CRM customization and process improvement.