GP and CRM Integration – All in One
The Dynamics GP and Dynamics CRM templates facilitate easy data integration between the most commonly used modules in Microsoft Dynamics CRM and Dynamics GP using SmartConnect. They provide an easy to install and ready to use Dynamics CRM to Dynamics GP integration option allowing Dynamics CRM users to integrate data between Dynamics GP and Dynamics CRM as a one time bulk import as well as set up real-time or scheduled maps to keep both systems in sync. Several end points have maps sending data both directions, such as items or accounts. This allows. Each map can be used as-is or can be used as a great starting point to customize further. The provided maps integrate to:
- Payment Terms
- Price Lists
- Ship Methods
- Sales Territories
- Sales people
- Units of Measure
The GP-CRM templates contain 44 different maps consisting of bulk maps, real-time maps, and change maps. There are maps going from CRM into GP and maps going from GP into CRM. Because of the many types of maps, the naming convention used is:
- Maps that begin with “GP_” send data from CRM INTO Dynamics GP
- Maps that begin with “CRM_” send data from Dynamics GP INTO CRM
- Maps that end with “_RT” are real-time, “_CH” are change based, “_BK” are bulk maps
CRM Templates Set Up
Setting Up Connectors
The maps will all use ODBC Data Source connectors to the database that are set up once and used through most of the maps. There will be two separate connections set up for the company and dynamics database, respectively. To set up the use ODBC Data Source connectors for the maps, brows to the Maintenance tab in SmartConnect and click on the ODBC Data Source button in the Generic Connector pane. When the ODBC Defaults window appears, create two connectors with an initial connection in the company database and in the dynamics database. The name of the connection you choose will be used in the Map Setup window as the Connection Type.
The two ‘GP_SLSPN_’ maps require connecting to the company database through a calculated field. This calculated field uses a MSSQL connector that is set up by clicking the MSSQL Defaults button from the Maintenance Tab within SmartConnect.
- Import the ‘Dynamics CRM-GP Templates.sce’ file. It contains 44 maps to integrate data between GP and CRM. Not every map needs to be imported, but note that certain necessary for the Sales Order and Account data integration between CRM and GP.
- The order in which the maps need to be run is specified in section Running CRM Template Maps.
Connection Type Setup
The templates sending data from GP into CRM will use one of two connection strings for the Dynamics database and Company database. To set up the connection strings, go to the Maintenance tab from the main SmartConnect window. Click on ‘ODBC Data Source’ from the ‘Generic Connector’ pane. From this window, create two connections: one with initial connection to the company you will be exporting from, and another from the Dynamics database. Because these connection strings will be named and set up by the user, every map requires the ‘connection type’ be set before running the template. The appendix table with a List of CRM Templates shows which maps require which initial connection.
Because there are many ways to import data between GP and CRM, some of the maps may need to be changed for your specific requirements. Some of the changes include:
Currency from GP to CRM
For all the templates mapping currency ID in Great Plains to ISO Currency in CRM, the ISO Currency is set in a case statement within the query. If you are using a currency other than what is provided in the query, you can add additional cases to the query.
For templates mapping ISO currency in CRM to currency ID in GP, a translation table is included with these maps. If you are using a currency other than what is provided in the translation table, you can add additional cases to the translation table.
Multi-lookup option default values
The maps that import from GP to CRM and populate drop-down items in CRM will use a multi-lookup to populate these fields e.g. ‘Shipping method’ and ‘payment terms’. The query on these maps will include a hard-coded default value to be used if a record does not have a value at this field. For these maps, the value is set from within the query in a CASE statement. To change the default settings, simply update the hard-coded value in the query to what you would like the default to be if there is a blank value.
The Currencies map brings over the names of all the currencies, but hard-codes the exchange rate to 1 because multiple exchange rates can be set up for a given currency. Also note that the ‘Connection Type’ from the data source must have an initial connection to the Dynamics database.
Orders from CRM to GP
CRM doesn’t include as many input fields as GP, so certain fields have been hardcoded to constants. The orders map has Document ID set to ‘STDORD’, batch number set to ‘CRMBAT0001’, and Location code set to ‘WAREHOUSE’. These can be set to new constants or can be mapped to another field from CRM. If an entity is customized to allow entry of these fields from within CRM, these new customized fields can be mapped instead.
The Smart Connect maps for the CRM sales order and account integration must be run in the specific order. Firstly, run all the bulk maps, and then register the real time maps.
Bulk maps must be run in the order specified below. For some maps to run, data must exist, eg. Currency must exist for the exchange rate map to run. If the pre-requisite data does not exist in the destination system, your map may not run successfully.
Before running each map, configure the connection string to point at the correct data source: Map Setup -> Data Source. Click the lookup button next to connection string. Then, select the company in which the maps should be run.
This is the list of bulk maps by map ID that you will need to run as follows:
- CRM_CURR_BK – import all GP Currency to CRM
- CRM_SLS_TER_BK– imports sales territories from GP into CRM
- CRM_SLSPN_BK – links the sales person in GP and the user in CRM using the email address and Sales ID. The Sales Person email address must exist in GP. Email and Sales persons ID become a unique identifier of each CRM user. After the import, the CRM users should have the Sales ID, which were imported from GP.
- CRM_SHIPMETHOD – imports the shipping methods used in GP to CRM lookup fields. By default, all shipping methods will be sent to CRM and published directly from the map. If you do not use the shipping method drop-down lists in CRM, this map is not required. If you choose not run this map, the multi-lookup named ‘MLK_SHIPMETHOD__ATTRIBUTEVALUE’ in map ‘CRM_ACC_BK’ map must be unmapped.
- CRM_PAYMENTTERMS – imports the payment terms used in GP to CRM lookup fields. By default, all payment terms will be sent to CRM and published directly from the map. If you do not use the payment terms drop-down lists in CRM, this map is not required. If you choose not to run this map, the multi-lookup named
- CRM_ACC_BK – imports customers into CRM
- CRM_ADDRESS_BK – imports customer primary address and any additional addresses the customer may have
- CRM_PRCLEVEL_BK – imports price levels from GP into CRM
- CRM_UOFM_BK – imports various Unit of Measures to be used on Sales Order and Invoices
- CRM_ITEMS_BK – imports inventory items from GP into CRM. ‘Discontinued’ and ‘Kit’ items will not be imported because CRM does not list these types in the product type drop-down list.
NOTE: CRM does not allow a negative quantity for new items. If an item is imported with a negative quantity, a validation error will occur on the value of ‘quantityonhand’ from the SmartConnect Progress window.
- CRM_PRICELIST_BK – import Currency Price Lists
- CRM_INVOICE_BK – import invoices from GP into CRM
Real Time and Change Maps
There exists a real time map for all bulk maps. For GP to CRM maps, there also exists a change data source map as well. To use a real-time map, register the map on both Create and Update events. The maps with an exception are the CRM_ITEMS_RT and GP_SLSORDER_RT maps, which should only be registered on the update events.
Changing/Customizing Real Time and Change Maps
The query between bulk maps and real-time or change maps are very similar, though real-time and change maps must be edited through the query builder window instead of SQL directly. The screenshot above shows how a CASE statement can be entered through the query builder.
Because of the similarity between bulk maps and real-time or change maps, the templates are a great way to see how the query builder processes queries.
These maps have been created and can be used on as needed basis for bulk sales and order data import from CRM to GP:
- GP_ACC_BK – to import all your CRM accounts into GP
- GP_ADDRESS_BK – to import all your CRM account addresses into GP
- GP_SLSPN_BK – to import all your CRM Sales People into GP. This map uses a calculated field that accesses the DYNAMICS database through a MSSQL default connection set up outside of the map. To set this up, see the section above on setting up connection strings. After creating the MSSQL connector, verify this name is used within the calculated field. By default, the calculated field will attempt to create a new ID using the last name and first character of first name, but can be changed through the field.
- GP_ITEM_BK – to import all your CRM products into GP
- GP_ORDER_RT – to send newly created orders in CRM to GP. This file will import both the GP_ORDER_RT map as well as the CRM_ORDER_CH map. The CRM_ORDER_CH map is a change based map that sets the status of the orders in CRM to Submitted/In Progress. This map is set as a Map Post Task of the GP_ORDER_RT map to automatically set the order status every time an order is brought into GP successfully.
- If the multi-lookup does not find the appropriate shipping method, the entire record will error out. Depending on how your system is configured, there are two options:
- Run the CRM_SHIPMETHOD map and import all of the shipping methods into CRM
- Un-map the MLK_SHIPMETHOD from the SmartConnect Mapping window
- If all records fail sending from CRM to GP because Payment Terms ID does not exist, verify that option sets are being returned from the Map Setup window by checking the ‘Return Option Set Labels’ box. This will return the displayed name in a query instead of the numeric value.
- Sales Person is not setup as an active user in CRM (CRM Settings – Administration – Users Maintenance – the sales person must be set up in CRM as a user otherwise the following eConnect error will occur)
- CRM_SLSPN_BK runs but does not import any data – check that the sales person has an email address in GP.
- Map GP_SLSPN_BK fails with error in calculation column ‘SALESPERSONID_CALC’. The calculated field accesses the DYNAMICS database using a MSSQL Default connection that must be set up. Read the instructions on setting up connectors and the map description for GP_SLSPN_BK.
- Map CRM_ACC_BK fails to import accounts that belong to the CRM User (which is equivalent of the GP Sales Person). Check the user has sufficient rights to own Accounts. If not, assign a role as appropriate and re-run the map.
- If you have not entered any order lines (add existing product), your order will not be pushed into GP. (not much point having an order with no lines)
- CRM Account Relationship Type must be set to ‘Customer’ (relationship type =’3’), else map will not work (due to a restriction on the Real Time data source). Of course, if you want a different restriction it can be configured within the map.
- CRM Customer number must be entered otherwise the map will not integrate data for that record.
- GP_SLSPN_BK – must enter Sales Territory against the Sales Person/User. If not, you may get the error in the event viewer “GP_SLSPN_BK validation failed. Source column does not exist in the source data.”