In our most recent webinar, we took a look at Automation in Microsoft Dynamics GP. In this webinar, we covered how automation can be used in Microsoft Dynamics GP to improve efficiencies and accuracy of data. If you want to catch up on this, or any other, webinar, you can do so here.
In this blog post, I am going to recap the webinar and cover the highlights of how automation can be used in Microsoft Dynamics GP to improve efficiencies and improve data accuracy:
Where possible in this webinar I highlighted standard, or Microsoft supplied, features or additional products where they are available. However, in many cases the standard functionality does not allow for full automation. This is an intentional design choice made when Microsoft Dynamics GP was first created back in the md-90s. The company who created Great Plains, the original name of Dynamics GP, was intended from the very beginning to be extensible with the intention that there be a thriving third-party marketplace for add-ons.
This is the current situation; the core Dynamics GP system has strong core financials and distribution modules, but wider functionality is provided by third party (Independent Software Vendors (ISVs) who have a variety of add-ons and complimentary products which provide the functionality required or automating processes. In each of the areas, there are usually a number of products available from several vendors, but I have selected one in each area. usually an add-on which I have used with several clients across a number of years and which has received positive reviews.
Before implementing one of the solutions, I’d recommend reviewing the functionality it includes, the functionality of competing products and making your own decision about which will best fit your requirements.
Discussions on SmartConnect usually involve the question as to what data can be integrated by it into Dynamics GP; the short answer is if eConnect can do it then so can SmartConnect, but few people (including me) remember all of the nodes available within eConnect. As would be expected, this is also a common question direct to eOne, and one which Lorren Zemke addressed on the eOne blog: Tech Tuesday : What Data Can SmartConnect Integrate in Dynamics GP (eConnect).
A while back a client using SmartConnect reported an error running one integration; this is one of a set of newly created identical integrations running against a set of companies, but only one of them was failing.
The error produced was:
Task xxxx failed. Map run will end reporting failure. The DELETE permission was denied on the object 'xxxx', database 'xxxx', schema 'dbo'
As there was a reference to permssions, I assumed there would be a SQL issue; when looking through database permissions, I found that in one database the user account used by SmartConnect had the db_denydatawriter role membership assigned:
Once this permission was removed, the integration ran successfully.
I recently created an integration using SmartConnect for a client as I have many times before; however, when I came to test the integration, my source file was totally ignored and the integration returned an error that no file was found.
The integration was using a folder data source with an Excel template; nothing special and something which I have configured many times. I copied the template file into the source folder and tried the integration again and got the same result.
I asked another consultant on the team to take a look with me and neither of us could see a problem; everything looked correct. Then it occurred to me that the extension of the file was .XLSX which I would not expect to be a problem; but, we couldn’t see anything else, so changed the extension to .xlsx and tried the integration again.
It worked correctly. I didn’t expect the case of the file extension to cause a problem, but is something to definitely bear in mind in the future and to make sure clients are aware.
I recently assisted with a support call logged by a client following the introduction of an RM integration created using SmartConnect.
The client enters exchange rates at the start of the month, but following the integration being deployed, extra exchange rates were being inserted into the exchange rate table. This caused problems as the rate from the external system varied more often than the rates entered, and required, in Microsoft Dynamics GP.
The investigation started from the integration to see if there were any settings in it which could be causing this issue, but none seemed relevant. The only currency fields being set was the Currency ID and the Exchange Rate.
After this I started taking a look into the eConnect stored procedures on top of which SmartConnect runs. After digging down a couple of levels I discovered that taMCExchangeRate has a section which inserts the exchange rate passed in if it is not in the Exchange Rate table; this is regardless of the setting in Multicurrency Setup (Financial area page » Setup » Multicurrency) which were set to allow rates to be used without being added to the exchange rate table.
The client was running Microsoft Dynamics GP 2013 SP2 and my investigation was confirmed on a 2019 October Release environment so this is a long standing issue which is not yet resolved; I have confirmed with Microsoft that they are aware of this issue and it is logged with the development team for resolution in a future version.
In the meantime, I have deployed customised versions of two stored procedures to the client so that they do not have the exchange rates being inserted into the exchange rate table.
I’ve recently been working on a SmartConnect project with a client. Integrations were created, tested and signed off as working by the members of the project team. However, when additional users starting using SmartConnect, an error was encountered by some users, not all, running the integrations:
Connection could not be validated
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
The integrations were using an Excel file data source and, when checked, the users having problems had the 64-bit version of Microsoft Office installed and the working ones had the 32-bit version. You don’t need to repalce the whole office installation, just install the 32-bit version of the Microsoft Access Database Engine 2016 Redistributable from the command line as a silent install.
With the node created, we need to make it available for use in SmartConnect before we can actually use it.
This is done by refreshing the SmartConnect GP Resource Cache. Do this by launching Microsoft Dynamics GP and selecting GP Resource Cache from the (Microsoft Dynamics GP » Tools » SmartConnect » GP Resource Cache) menu:
We’ve been doing some work with a client recently creating a number of integrations in SmartConnect. One of these was creating General Journals into a consolidation company from other Microsoft Dynamics GP databases.
Everything looked fine from a SmartConnect perspective; success reported for the integration and no warnings or errors. However, when we looked at the resulting journal, there was no Currency ID:
The problem here isn’t actually a SmartConnect one, but an eConnect one which has been known for a long time. There are a number of reports of this, but the one we found when researching was on the Dynamics Blogger.
The solution is to create a chequebook (checkbook) for the Functional Currency.
The company we were importing into, didn’t have one as only the GL was going to be used; once the chequebook was created, the integration worked fine.