Dynamics CRM/D365 Sales Bulk Templates

Supports:
SmartConnect 2017+ (Office 2016+)
Publisher:
eOne Solutions

Dynamics CRM/D365 Sales Bulk Templates



Overview

Integrate directly into Dynamics CRM/D365 Sales using our pre-configured bulk templates!

The templates make importing data fast and easy without sacrificing flexibility. Each SmartConnect map includes an Excel workbook prefilled with sample data to show you the structure, all you need to do is fill in your own data and import!

The Dynamics CRM/D365 Sales Bulk Templates include SmartConnect maps that integrate into:

  • Accounts
  • Contacts
  • Currencies
  • Leads
  • Price List Items
  • Price Lists
  • Products
  • Sales Invoices
  • Sales Orders
  • Shipping Methods
  • Territories
  • Unit Groups
  • Units of Measure


Details

The Dynamics CRM/D365 Sales Bulk Templates include 13 SmartConnect maps. Each map comes with an Excel workbook filled with a few rows of sample data, but the data can (and should!) be filled with your own data. The maps were exported from SmartConnect build 20.18.1.14. If you are using an earlier version of SmartConnect, you will need to update SmartConnect to use the templates.

The maps will work with Dynamics CRM and Dynamics 365 Sales. To keep the name short, "CRM" will be used throughout the document, but the steps are applicable to either system.

Below is the complete list of maps and their description.

Map IDMap Description
CRM_ACCOUNTS_BKBulk Import from Excel to Accounts
CRM_CONTACTS_BKBulk Import from Excel to Contacts
CRM_CURRENCIES_BKBulk Import from Excel to Currencies
CRM_LEADS_BKBulk Import from Excel to Leads
CRM_PRICE_LIST_ITEMS_BKBulk Import from Excel to Price List Items
CRM_PRICE_LISTS_BKBulk Import from Excel to Price Lists
CRM_PRODUCTS_BKBulk Import from Excel to Products
CRM_SALES_INVOICES_BKBulk Import from Excel to Sales Invoices
CRM_SALES_ORDERS_BKBulk Import from Excel to Sales Orders
CRM_SHIPPING_METHODS_BK    Bulk Import from Excel to Shipping Methods
CRM_TERRITORIES_BKBulk Import from Excel to Territories
CRM_UNIT_GROUPS_BKBulk Import from Excel to Unit Groups
CRM_UNITS_OF_MEASURE_BKBulk Import from Excel to Units of Measure
To begin using the CRM Templates:
  1. Create a CRM/Sales connector in SmartConnect
  2. Import maps into SmartConnect
  3. Run SmartConnect Maps
  4. Going Forward

1. Create a CRM Connector in SmartConnect

You only have to set this up once because every map will use the same connection.

To set up the Connector:
  1. Open the 'SmartConnect Dynamics CRM Connector' in the SmartConnect setup window

  2. Fill out the 'Default Server Settings' section:

    • Enter the 'CRM Server'
    • Choose the 'Authentication Type'
    • Click the 'Refresh' button to populate the 'Organization' drop-down box. This will only populate if the server and authentication type are entered correctly.
    • The 'Service Url' and 'Metadata Url' are populated automatically
  3. Fill out the Default Security Settings:

    • Every check box (except the last one) will add additional functionality, so checking every box will work 95% of the time. Only leave a box unchecked if you have a reason not to.
    • Check the 'Use the following credentials when accessing Microsoft CRM' box.
    • Enter the 'Username', 'Password', and 'Domain' fields.
  4. CRM has the option to trigger maps to run in real-time. You only need to fill out the 'Real-Time Data Source Settings' if you will be using these types of maps. Note that none if the included maps are real-time maps.

    • The 'Call Back Web Service' is the URL of the SmartConnect web service. The URL will be of the format:

      http://{Server with SmartConnect}:5557/smartconnect.svc
      
    • Enter the 'Username', 'Password', and 'Domain' fields.

Below is a completed CRM Connector:

2. Import maps into SmartConnect

After the connector is set up, we can import the maps.

To import maps into SmartConnect:
  1. Click the 'Import Wizard' button from the 'Maintenance' tab in the ribbon.

  2. Go through the remainder of the import wizard, selecting the 'CRM Bulk Maps.sce' file that was included in the download. To import everything, click next. If you don't want to import all the maps, check only the maps you wish to use. The maps are ready to use once imported.

3. Run SmartConnect Maps

You may not use every map in the CRM templates. Every map is designed to work by itself, but you may encounter errors if the sample data references data that doesn't exist. For example, you cannot import an invoice if the products and accounts referenced within the invoice aren't in the system. Importing invoices would require all the fields referenced (like accounts and products) to be imported first.

Below is the order the maps should be ran in. You won't need to import every map, but run the selected maps in order. Not every map has other dependencies, but running the maps in the order they appear in this list should eliminate any dependencies.

Run OrderMap IDMap Description
1.CRM_CURRENCIES_BKBulk Import from Excel to Currencies
2.CRM_UNIT_GROUPS_BKBulk Import from Excel to Unit Groups
3.CRM_UNITS_OF_MEASURE_BK Bulk Import from Excel to Units of Measure
4.CRM_PRICE_LISTS_BKBulk Import from Excel to Price Lists
5.CRM_PRODUCTS_BKBulk Import from Excel to Products
6.CRM_PRICE_LIST_ITEMS_BKBulk Import from Excel to Price List Items
7.CRM_ACCOUNTS_BKBulk Import from Excel to Accounts
8.CRM_CONTACTS_BKBulk Import from Excel to Contacts
9.CRM_LEADS_BKBulk Import from Excel to Leads
10.CRM_TERRITORIES_BKBulk Import from Excel to Territories
11.CRM_SHIPPING_METHODS_BKBulk Import from Excel to Shipping Methods
12.CRM_SALES_ORDERS_BKBulk Import from Excel to Sales Orders
13.CRM_SALES_INVOICES_BKBulk Import from Excel to Sales Invoices

Setting the Excel Workbook Path

When loading each map for the first time, you will probably receive errors saying 'Connection could not be validated', and 'You must enter a valid data source before continuing'.

This just means that the map doesn't know where the Excel workbook is located. Update the 'Excel Workbook and 'Sheet' fields and click the Preview button to verify it works.

4. Going Forward

Every map comes with an Excel workbook. The provided Excel workbook contains a single table with sample data. This data can change, but the column names must stay the same. The column names tell SmartConnect where to put the data in CRM.

Changing the mapping in the SmartConnect map is possible and encouraged! The templates are only meant to get you familiar with the interface and give a good starting point. Covering everything around modifying a process is beyond the scope of this document, but adding/removing fields will show some of the main features of the product.

The steps below will show how to add additional columns to the data source and how to add these new columns to the existing process.

Adding a Column to the Template

The data source is re-read several times when creating a map, changing a map, or running a map. To add additional columns, simply add an additional column to the Excel workbook and re-click the preview button. The Map Setup window won't need to be closed to add the column.

To add/remove a field to an existing process:
  1. Click the 'Preview' button on the SmartConnect Map Setup window to see the existing data on the Excel workbook.

  2. Add another column and save the workbook. The example below has 'MobilePhone' added.

  3. Click the 'Preview' button on the SmartConnect Map Setup window again and notice the new column added.

Adding the column to the workbook gets the data into SmartConnect, but not into CRM. We need to map the new column to a CRM column to make it work. I'll continue with the *'MobilePhone' * example from above.

To map a new field:
  1. Go to the Destination section of the Map Setup window and double-click the name of the node you wish to map to. The 'CRMCONTACTSBK' only has the 'Contact (contact)' node, so double click that.

  2. Notice the 'MobilePhone' field added to the table on the left along with all the other columns in the Excel workbook. Every column from our data source (and any calculated fields we create) are in this list table. The fields won't do anything in this table until they are mapped to the correct field on the right.

    In the table on the right, scroll down to the 'Mobile Phone' field. Click and drag the 'MobilePhone' row from the table on the left and drop it onto the 'Mobile Phone' field on the table on the right. The icon will change during the drag-and-drop step.

The source column name is now added to the 'Column Name' column, so we know our field is now mapped. Click OK and save the map.

Special Types of CRM Columns

The mobile phone example doesn't reference anything else in the system. It's just a plain text field. The other type of fields that you look up are a little bit more complicated, but SmartConnect makes them pretty simple with Additional Columns.

From the 'SmartConnect Mapping' window, click the 'Additional Columns' button.

There are quite a few Additional Column types to chose from. I'll briefly cover 3 of them used in the 'CRMSALESINVOICES_BK map. Below is the Excel workbook used as a data source for the invoices map.

Entity Lookup

An entity lookup will be used whenever you need to look up another entity. In the invoice map, Customers, Currency, and Price List are all entities and will require an entity lookup. Our data source has a column called 'accountname'. *The account name isn't an entity, it's just a text field!* Without the entity lookup, SmartConnect sees this just as text like the mobile phone example above. The 'ACCOUNT' entity lookup will find the correct account that has the same name as the 'accountname' field.

From the Additional Columns window, double-click the 'ACCOUNT' entity lookup.

This box says, "From the account type of entity, give me the entity that has the name field that matches my account_name data source column from Excel". We can add additional filters here, but the name should be unique enough to figure out which entity we are referring to.

List Option Lookup

List option lookups are used for drop-downs in CRM that are a simple (usually non-changing) list, referred to as a picklist. 'Shipping Method' and 'Payment Terms' are both drop-down items, so we need to use a list option lookup to fill in these fields.

Above is the 'SHIPPINGMETHOD' additional column. This box says "From the invoice type of entity on the shippingmethodcode picklist, give me the reference to the item that matches my shippingmethodcode data source column from Excel. If the shippingmethodcode Excel column doesn't match any item in the list, return blank".

Multi Lookup

Multi lookup columns are very similar to an entity lookup, but they return another field from our entity that isn't just the Entity ID. It can pull any other field from the entity. For example, the invoice map doesn't have any columns in Excel for unit of measure. The map is set up to grab the default unit of measure for that product.

The box says "From the product type of entity, on the product that has the name field that matches my product_name data source column in Excel, give me the Default Unit from that product".

Conclusion

These template are provided as is and are free to use for all SmartConnect customers. If you are having difficulties getting up and running, please contact support@eonesolutions.com

Free 30 Day Trial

Free 30 Day Trial