Dynamics GP and Salesforce Integrations
Dynamics GP and Salesforce Integrations
Dynamics GP and Salesforce Templates Description
The Dynamics GP and Salesforce templates provide an excellent base for any integration designed to connect the two systems together. The templates can send records in either direction; some maps send Salesforce records into GP and some send GP records to Salesforce.
List of Integration Points
- Price Books
- Price Book Entries
- Standard Price
- Opportunity to Invoice
Description of integration types
Most of the integration points have one-time bulk maps to initially sync up both systems and maps to keep the systems in sync. The maps designed to keep systems in sync can be scheduled to run once a day or as often as every 5 minutes to keep both systems as up to date as you need.
Map Type Definitions and Order
Map IDs provide a basic understanding of what the map does:
- Map ID’s beginning with ‘GP_’ send data into Dynamics GP
- Map ID’s beginning with ‘SF_’ send data into Salesforce
- Map ID’s ending with ‘_BK’ are bulk maps meant to be run once to initially sync both systems
- Map ID’s ending with ‘_CH’ are change-based maps meant to be run on an ongoing basis. These maps can be scheduled to run every day.
- Map ID’s ending with ‘_RT’ are real-time maps that, once registered, will fire automatically when a certain table is updated or changed.
The bulk maps are designed to be run before the change-based maps, and the change-based maps can all be scheduled. Because the change based maps and the real-time maps both keep systems in sync, only one of the two types need to be used. Real-time maps will keep things in sync the most frequently, but can be harder to debug. The ‘SF_OPP_INVOICE_CH’, which is linked to the ‘GP_INVOICE_OPP_BK’ map, doesn’t need to be ran by hand.
Map List with Descriptions
The table below lists all the maps in this set of templates.
To use the templates, the following software must be installed and ready to use:
- Dynamics GP 2016
- A SalesForce company set up
- SmartConnect 22.214.171.124 or higher
If using real-time maps, a valid SQL user must be set up to make connections to the GP company database.
ERP Customizations and Installations
The templates do not require any changes to be made from within GP.
Opportunities in Salesforce have a custom text field to hold the name of the Dynamics GP Invoice number named ‘GP_Invoice_Number’ and the data type is Text(21). Opportunity Products has a custom field named ‘GP_Item_Number’ and a Formula (Text) data type.
Add ‘GP_Invoice_Number’custom field to the SalesForce Opportunity.
The ‘GP Item Number’ formula field added to the ‘Opportunity Products’ table should have a field name of ‘GP_Item_Number’ and a formula of ‘PricebookEntry.Product2.ProductCode’ (not in quotes). The edit field screen should look like:
With both services running, set up the connector for both services so SmartConnect knows how to reach them.
The Salesforce window needs a username, password, and user security token from Salesforce. The username is the email address, the password is the user’s password, and the security token is the user’s security token.
Click the link to edit personal information.
Click the ‘Reset My Security Token’ link on the far-left side of the interface. Follow the prompts to find your security token and paste it into the Salesforce Connector Setup window. Below is an example of a filled-out connector.
The setup for GP requires the server path, Dynamics database name, SQL username and password, and a default company. If you want to use real-time maps, then fill out the ‘Real time and SmartList web service’ section with information to access the SmartConnect web service. Below is an example of a filled-out connector.
SmartConnect Map Import
Import the maps by clicking the Maintenance tab on the ribbon and clicking the ‘Import Wizard’ button. Browse to the ‘Salesforce GP Maps.sce’ file from the zipped folder in the window that appears. Follow through the wizard to import the maps.
Map Run Overview
Choosing the Correct Maps to Use
Not all maps will be used by an organization. Because there are maps sending data into either Dynamics GP or Salesforce, choose the maps that best fit your organization structure. For example, if you create items in GP, you won’t need GP_ITEM_BK (which sends products from Salesforce into GP) but you will use the SF_ITEM_BK map (which sends items from GP into Salesforce).
Bulk Map Run Order
Some of the maps need to be ran before others if the map depends on data that doesn’t exist in the system yet. For example, pricebook entries can’t be imported before the price book exists. Note that only the bulk maps need to be ran in any order; once the systems share the same data, there are no more dependency issues with the maps.
Below is a list of maps and their dependencies.
There are less maps going from SalesForce into GP because GP has more settings and is more likely to be the original source for data. The maps that send data into GP from SalesForce can be ran in any order.
Ongoing Maps and schedule intervals
Once you rule out which bulk maps to run, decide how you want SmartConnect to keep both systems in sync. The change-based maps (ending in ‘_CH’) are very similar to the bulk versions of the map, but only send records that have been changed. The real-time maps are another option for making updates, but they operate a little differently. The real-time maps are registered on a specific table in the database and will run whenever that table is modified. This means that maps that join several tables together for a data source can quickly open up problems on when a map is supposed to fire since you can only register the map on one table. Because of the additional complexities of real-time maps, using the change-based maps and scheduling them to run on a regular basis is recommended.
Connection Setup for Dynamics GP Data sources
Many of the maps query the Dynamics GP database to get data. The maps are initialized with data source connection, but this will need to be changed to point at your specific system (it will show an error as soon as you open a map pointing at a bad database).
- Click the ellipse button to the right of the ‘Data Source’ text box (highlighted in yellow).
- Select the ‘Machine Data Source’ tab in the popup.
- Select the name of your Dynamics GP 2016 database.
- Enter the login ID and password of the user that connects to the SQL database.
- Click the ‘Options >>’ button and then verify the correct company database is selected. The sample company ‘TWO’ is selected below.
- Click OK and click the Preview button to verify data is returned successfully.
Using the GP_ITEM_BK Map
The GP_ITEM_BK map sends products from Salesforce into Great Plains. This map uses a multi data source where one of the queries pulls the number of decimal places associated with the default currency for a specified company. Because this query pulls information from the company database and the dynamics database, you may need to update the SQL query to point to the actual name of your dynamics database.
The screenshot below shows some of the steps to configuring this map.
- Open the ‘GP_ITEM_BK’ map. Click OK on any error dialog boxes that pop up from not being able to connect.
- Double click the ‘GP_CURRENCY_ML’ Table name (highlighted below).
- Click the ‘Edit SQL’ button and change the name of the dynamics database name in the query to the name of your dynamics database. The screenshot below uses ‘dyn2016’, but will say ‘dynamics’ in the map you import by default.
- Click Save on the ‘Multi Data Source Item’ window and click preview to verify data is being returned.