In this article, I will cover the steps to create a Microsoft Dynamics NAV MSSQL Real Time Data Source in order to migrate your NAV customers over to your Microsoft Dynamics CRM Accounts.
Your first step will be to create the MSSQL Default connection to be used in your MSSQL Real Time Data Source.
1. Log in to SmartConnect as an administrator and select the MSSQL Defaults option from the Maintenance Ribbon at the top of your SmartConnect window.
- Click Add to open the Default Microsoft SQL Servers window.
- Enter a Name, Description, and your server credentials.
- Click Connect to verify the server information is correct and select the NAV Database.
Click Save to save your changes
(Please note, this window clears when you click save, but you can verify your changes were saved by selecting the ellipses next to the Name or simply close the window and you will see the new connection info in the list)2. With the MSSQL Default connection saved, you can now select the Real Time Data Sources option from the Map Ribbon at the top of your SmartConnect window to proceed with creating the Real Time Data Source.
- Select the MSSQL Table Real-Time Data Source option and click on Create.
- Select the newly created connection information, in my example “NAV 2016”.
- Select the Table, in this case I will be pointing to the table “Cronus USA, Inc_$Customer”, which stores the Microsoft Dynamics NAV customer information I wish to use to populate the Accounts entity in Microsoft Dynamics CRM.
Click on the Define button, found at the lower right hand corner, and select the Fields from the Microsoft Dynamics NAV Customer table you’d like to use in your integration.
In this example I’ve selected the following fields:
- No, Name, Address, Address2, City, Phone_No, Post Code, and County
- Click Ok to save the changes
Click Save to close the Real Time Data Source window
You should see the newly created Real Time Data source in your list.
3. Now that you have created the SQL Default and created a new Real Time Data Source, you can build your Map.
- Under the Map Ribbon menu, click “Create” to open the Map Setup window.
- Expand the Data Source section of the Map and enter a Map ID and Map Description
- Select the Data Source Type “Real-Time Event”
- Select the Data Source “MSSQL Real-Time Data Source”
Under the Settings Select your NAV Server
- Select the Key Field “No” (Customer Number) for your unique Identifier.
Click “Validate” and then “Preview” to display the records your data source values.
- Using the double arrows on the right hand side, collapse the Data Source section and expand the Destination, which appears below the Data Source section.
Select the Destination Type “Microsoft Dynamics CRM”.
- As long as you have a CRM connector configured, you will see your default instance appear in the CRM instance field.
- Select the Entities to Process. In this case, we are selecting the Account entity to populate in CRM.
- Next, we will need to Map the fields from NAV to CRM by double clicking on the Account Mapping.
Here are the fields that were mapped in this example:
|Address||Address 1: Street 1|
|Address2||Address 1: Street 2|
|City||Address 1: City|
|PostCode||Address 1: Zip/Postal Code|
|County||Address 1: State/Province|
The following fields were added to the Destination Mapping.
- Address 1: Shipping Method – This was set to a local constant value of “DHL (2)”
- Relationship Type – This was a List option set to “Customer (3)”.
The “Modified By” field was mapped to an Entity Lookup field called LK_Owner, which is referencing a calculated field called “CALC_OWNER”. The screen shots below show the Additional Columns that were created on the Account mapping window.
You will also need to check the box “Update Existing” at the top right hand corner of the SmartConnect Mapping window and mark the “Group On” box next to the Source Column “No”.
The final Mapping looks like this:
- Click Ok to save your changes and close the SmartConnect Mapping window.
- Click “Register” and mark the “Create” and “Update” boxes.
With your new Mapping created and registered, the next Customer you create or update in the Microsoft Dynamics NAV Cronus USA database will be automatically pushed over to your Microsoft Dynamics CRM system.