This article discusses the whys and the hows of Microsoft Dynamics GP integration, and shares several tips, tricks, tools, and resources for successful integrations.
Originally published in H1 2019 GPUG Magazine
What really separates an ERP system from the herd? The ability to integrate, of course! Without having the ability to integrate, we’d be back to processing everything manually like the days of the cavemen.
Microsoft Dynamics GP is especially EASY to integrate with, and this short article will walk you through the whys and the hows, with a few tips and tricks along the way.
The Whys
SQL Server
Microsoft Dynamics GP is built on SQL Server, which is one of the most widely used database management systems in existence. It’s easy to work with, easy to connect to, you don’t come across many issues/bugs, and there are a million resources out there on the database platform and language itself.
Table and Database Structure
Microsoft Dynamics GP at its core is logically sound, very well thought-out, and tables/fields are organized. A thought-out and organized database structure makes integrating to (and reporting from) SIGNIFICANTLY easier. Anyone who has ever spent any time looking or working with the Microsoft Dynamics GP tables will be able to quickly tell you what a prefix of “PM” stands for (Payables Management) and what “10000” in a table name signifies (hello, unposted transactions). The whole database model is standard, consistent, and just makes sense.
eConnect
The out-of-the-box “Integration Roads” that Microsoft Dynamics GP provides (eConnect) give peace of mind when integrating data. If you’ve heard stories of corrupt data and nightmare ERP system implementations, it’s likely because of faulty/incorrect data integrations. This happens when “someone” who thinks they know everything just starts inserting data into tables based on their own thought process…this never ends well. With Microsoft Dynamics GP and the assistance of the out-of-the-box eConnect nodes, your worries of bad/incorrect data are no more. eConnect nodes are what can be used to integrate directly into the CORRECT places in Microsoft Dynamics GP and will prevent you from ending up with bad data. There are dozens of these eConnect nodes available right out of the box that you can hook up to and integrate data.
Need to integrate a payables transaction? Well instead of your team having to know that you must hit the PM10000 and PM10100 tables, you just run your data through the eConnect nodes which will properly import the data AND will stop/notify you if there are potential issues with your data (say an inactive vendor). eConnect nodes are hands down an integration job’s best friend.
The Hows
Integration Tools
The technical back-end is great, but what good is it if you’re not interested in talking SQL, tables, or data in general, and you just want to integrate and be done? Microsoft Dynamics GP has several integration tools available to its Users which is really what takes integrating with Microsoft Dynamics GP to the next level. Some of these tools are Microsoft provided and come right out of the box with Microsoft Dynamics GP, but there are also several other integration tools available from companies that focus their business solely on data integrations into Microsoft Dynamics GP. Almost all the tools provide pre-defined integration templates for those that just want to quickly push data through from say Microsoft Excel to Microsoft Dynamics GP. However, the bread and butter of these tools and real value come from the integrations that you can fit exactly to your business.
Want to integrate credit card transactions directly from the bank into Microsoft Dynamics GP as payables transactions on a monthly basis? You can take the out-of-the-box payables integration, modify it to exactly fit the file (or even setup a direct connection) that’s come from your bank, and push “Go” to integrate those transactions.
Also, these tools have the ability to handle your more complex integration scenarios and become fully automated!
Want to integrate customer orders from your website into Microsoft Dynamics GP on a realtime basis? You can set up a connection to your website and integrate these transactions over IMMEDIATELY with some of the tools available in the Microsoft Dynamics GP world.
Pre-Defined Integrations Within Microsoft Dynamics GP
Integration tools aside, there are also several pre-defined integrations that are built right into Microsoft Dynamics GP.
Need to integrate cleared checks from your bank into your bank reconciliation module of Microsoft Dynamics GP? There are a few integrations/processes like this already available right within Microsoft Dynamics GP that can be set up with little to no effort.
The Resources
It’s been mentioned a few times to this point, but the Microsoft Dynamics GP community and resources available are plentiful when it comes to integrations. Here are a few general resources that that I’d recommend utilizing:
- GPUG forums, webinars, and blogs (obviously )
- Microsoft community
- Partner/ISV blogs
- Scalar-valued functions in SQL (Ever wanted to know what the doctype value of “1” stands for in the payables table? The DYN_FUNC functions will tell you.)
Getting Data Out for Integrations to Other Systems
This article has been primarily focused on getting data into Microsoft Dynamics GP, but the points discussed above also apply to getting data out and integrating with other systems. We could go on for another four pages talking solely about reporting, but the main points are that getting data out is just as easy as getting data in (well, easier, really). Taking that data and importing into another system can be done with some of the tools that integrate data INTO Microsoft Dynamics GP or the data can easily be handed off to another tool for another system.
Recap
- The proper application set up on the back-end simplifies getting data in/out.
- The integrations tools and out-of-the-box integrations are very well developed and can handle the most basic through most convoluted integrations to Microsoft Dynamics GP that you can dream up.
- The resources and community available in the Microsoft Dynamics GP world are vast and experienced, and likely any integration scenario that presents itself to your business has already been done with others willing to share their insight.
That’s it. Hope you enjoyed, learned a thing or two, and are now ready to go move those current manual processes into some good-looking integrations!