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:
Introduction ^
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.
Integration ^
One of the key areas for automation is integration of data into Microsoft Dynamics GP. Almost every client I work with has at least one other system which integrates data into Microsoft Dynamics GP, even if that is just a payroll journal. Far too many of those clients will manually enter transactions hen they only have a small number of integration requirements; at the very least, since GP 2013 R2, they have access to the copy and paste journals from Microsoft Excel which can help speed things up a little.
However, there is a range of other modules which can be used to integrate data into Dynamics GP.
Macros ^
The first item I covered in the webinar, was the use of macros to either insert or update data into Dynamics GP. This falls into the semi-automation category more than full automation as it does still require user actions, although it does minimise that effort. I’ve previously blogged about using macros to insert or update data using a variety of methods. The method I used in the webinar, was an extension of embedding a macro in an Excel spreadsheet formula; in the webinar I did this, but had some VBA in the spreadsheet which saved the macro column as a .mac file ready for the user to simply play it back.
I recently showed this approach to a client who needed to bulk update some costs in the Inventory module; they had been using the mail merge method which can be quite time consuming.
Macros are a standard Dynamics GP method for doing updates, but does have significant flaws:
- Playing a macro can be quite slow, although there is a method for a href=’https://www.azurecurve.co.uk/2017/11/microsoft-dynamics-gp-macros-playing-a-macro-quickly/’>playing a macro quickly although this doesn’t work in every case.
- When a macro is running, your PC cannot be used for anything else as the macro will crash when focus is moved elsewhere (see previous point).
- Inserting or updating data in scrolling windows gets very complex, very quickly.
- Macros in generally can be very fiddly to get correct burning time to get everything correct for running.
However, macros are free and included as standard with Dynamics GP.
Integration Manager ^
Integration Manager is a first-party integration tool from Microsoft, available as pat of the Customization Pack. hen you implement Dynamics GP, you receive a time-limited license for Integration Manager. Many clients opt to purchase the Customization pack add-on to continue using Integration Manager post-implementation as they are used to using it to integrate data.
Again, this is semi-automation as it requires a user to log into the Dynamics GP, select the integration to run and wait for the integration to finish; integrations cna be used to insert or update master records (such as new GL accounts, vendors, customers or items) and insert a wide range of transactions (such as journals payables invoices, purchase or sales orders or inventory transactions).
Integration Manager can be used by end-users who are also able to create and maintain their own integrations without the need for develop assistance.
Financial Elementz ^
ISC Software have a variety of add-ons available for Dynamics GP one of which is the Financial Elementz which includes many additional enquiries and reports as well as a few integrations.
Like Integration Manager, the Financial Elementz integrations require a user to place a file in a specific location, select the integration to run and wait for it to finish. The types of integration are fixed, but users can create definitions within those types to map the Excel source file to the fields in Dynamics GP.
Web Services & eConnect ^
Web Services and eConnect can be used to provide fully automated integrations to/from Dynamics GP. Web Services, which sit on top of eConnect, are typically used to integrate other web based applications with Dynamics GP. This would incude CRM or e-commerce platforms.
Web Services for Dynamics GP are SOAP based and will generally require a develop to create the integrations.
SmartConnect ^
SmartConnect is an integration tool from eOne Solutions, in ISV whose products I have a lot of experience with. I have previously written about Implementing SmartConnect as well as how to Create SmartConnect Journal – Standard Map.
SmartConnect is a true automation solution for integrating data into, or extracting data out of, Dynamics GP. I’ve helped a number of clients implement it over the last few years, so that they had seamless integration with a CRM, WMS or other system or even to automate processes between companies such as creating a payables invoice in a second company when a sales invoice was created in the first or for automating the production of accruals.
SmartConnect sits on top of eConnect for integrating data and so obeys the standard business logic applied in eConnect and has access to all of the methods for integrating data which means almost all data types can be integrated, and can be extended to do any of the missing ones or third party data types.
The interface of SmartConnect is fairly simple and can be used by end-users rather than requiring developers to create integrations.
Scanning ^
One of the key areas of automation a lot of clients are looking to is the creation of supplier invoices into Dynamics GP. Even a company receiving a relatively small amount of invoices each week can spend quite a lot of time creating invoices in Dynamics GP or looking for documents when auditors are on site. Scanning can be sued to tackle both of these areas.
DocAttach ^
The standard module in Dynamics GP for the canning and storage of documents is Document Attachment, a module first introduced in Dynamics GP 2013 R2 and extended regularly with new functionality. Most recently, Document Attachment was added to the Bank reconciliation module.
Doc Attach allows you to scan or attach electronic documents into a range of card, transaction and enquiry windows, and through all of the notes windows, for recall and viewing at a later date. The creation of the invoice is still a manual process.
OCR Scanning ^
The standard Document Attachment module allows you to scan and store documents, but will not create transactions. For this, you need a third party module of which there are many available.
In the webinar, I showed the new module being written by ISC Software which can be configured to scan supplier invoices, create the payables transaction in Dynamics GP and then store the scanned image using the Document Attachment module; by not creating a whole architecture and database for storing scanned invoices, we are able to take advantage of standard functionality and thereby keep down the cost.
For OCR scanning system which have their own architecture and database they usually also create their wn approval platforms. The ISC approach means that when a document is scanned and saved in Doc Attach for the newly created payables invoice, the document can be submitted to the standard Dynamics GP workflow module for approval which can be done via the Dynamics GP client (desktop or web), email or using the ISC GP Elementz Portal PurchaseHQ.
Approval ^
The Workflow module in Dynamics GP is a standard module included since 2014 and the release of Dynamics GP 2013 R2 and is used for approvals across Dynamics GP. Almost every version has seen workflow extended either through the ,a href=’https://www.azurecurve.co.uk/2020/06/workflows-available-in-microsoft-dynamics-gp/’>introduction of new workflow types or integration with other modules such as Document Attachment or Word Templates/a>:
While approvals can be done through the Dynamics GP client, approvals done this way require the user to have a Dynamics GP license. You can do approvals via email actions which use the Web Services for Dynamics GP; please note that there browser companies are retiring the security levels known as TLS 1.0 and TLS 1.2 meaning approval via email will not work for much longer unless you upgrade to the Dynamics GP Fall 2020 release which has been updated to use the supported TLS 1.2.
Email approvals can be used external to your network, but this should only be done if the default installation is updated to allow secure connections as the default installation is not secure.
An alternative to approvals via the Dynamics GP client or email actions, is to use the ISC GP Elementz portal PurchaseHQ which is integration with Workflow for Purchasing document approvals and was designed to be very secure. Beyond being very secure, one of the key advantages of using PurchaseHQ for approvals is that approvers can log on and see one list of all pending approvals for all companies in Dynamics GP; using standard functionality they will either have to log into each company in Dynamics GP or receive one email per approval, which can mean users get swamped with emails.
Posting ^
There is no standard way of automatically posting batches in Dynamics GP so any requirement for this will require a third-party solution. There are a number of alternatives available, but the one I have been recommending for years is PostMaster Enterprise from Envisage Software.
PostMaster Enterprise is my recommendation as it does not require a Dynamics GP client to be running all of the time and can be used to schedule many different types of batches for automatic posting and it can also be used to automatically post batches which have been approved via the standard Workflow module.
I have previously blogged about implementing PostMaster Enterprise.
Reporting ^
There are a number of reporting tools available for Dynamics GP which can be used to automate the production of reports through scheduling; some of these are standard and some third part add-ons.
Management Reporter ^
If you are able to schedule your financial reporting, then Management Reporter which comes as standard with Dynamics GP can be used. This module reports on actual and budget information from the GL and has a Report Schedule function built in; this function can be used to schedule either individual reports or report groups.
Many clients can’t schedule many reports as Management Reporter is mainly used for period or year-end reporting and it isn’t easy to predict the exact day when reports will need to be run, but I have worked with a few clients who have automated the production of budget reports so the budget holder can see their spend compared to budget on an up-to-date basis.
Business Alerts ^
Business Alerts is a standard function in Dynamics GP which uses SQL Database Mail for the sending of email alerts. There is a wizard process in Dynamics GP which allows a business alert to be created on a schedule to send a report to users. One client used this to send regular emails to approvers of purchase orders awaiting approval.
Another client had more complex requirements which could not be created in Business Alert so we created a custom alert, also using SQL Database Mail, to send emails to suppliers 7 days before an invoice became due and then a follow up on the day they came due.
Reporting Services Reports ^
Reporting Services Reports come as standard with Dynamics GP and can be customised or new reports added to extend the available reporting; this type of report can be scheduled for email distribution to users.
Jet Reports ^
A common reporting tool used with Dynamics GP is Jet Reports (formerly called Jet Professional) which allows non-technical users to create financial or operational reports. It includes the function to allow users to schedule the production of a report; this is often used with a schedule running overnight so that when users come in in the morning the report always includes up-to-date information.
Conclusion ^
There are many tasks which can be automated in Dynamics GP; the standard functionality will generally only go so far as semi-automation with third party add-ons required for full automation. This is not a flaw in Dynamics GP but a deliberate design decision made when the software was first created. By supporting third party ISVs to create add-ons for the system a rich ecosystem with many options has grown alongside Dynamics GP, meaning you are not limited to only what Microsoft themselves develop.
In the webinar I rounded off with a demonstration of a seamless accruals process, although an integration of invoices from a thirst party could have been done. The process I showed was:
- SmartConnect extracted a list of payables invoices to be accrued.
- SmartConnect inserted a reversing journal of those accruals into the GL.
- SmartConnect automatically submitted the resulting journal for approval in the standard Workflow module.
- I approved the batch via email (but could have used PurchaseHQ).
- PostMaster Enterprise posted the accrual batch once it had been final approved.
The above process was accruals but it could equally have been invoices integrated from a spreadsheet, integrated data through web services, or invoices created through OCR scanning.
As each element is separate, automation can be implemented in one area to meet one need and then automation introduced in another area at a later time to suit a different need.
If you want to catch up on this, or any other, webinar, you can do so here and if you want to speak to us about any of the points covered, you can use the Contact ISC form below.
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.
