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.
With GDPR rules, clients have become concerned about the information retained in the system without a means to delete it. To that end I created a SQL stored procedure which could be scheduled to run on a regular basis and delete transactions older than the specified number of years (highlighted value is the number of years).
This allows clients to run this on a scheduled basis and remove old documents; it can also serve as the basis for a customised version which deletes on a more controlled basis.
As with any script, please ensure you perform through testing before deploying to a live system.
IFOBJECT_ID(N'usp_AZRCRV_DeleteDocAttachAttachments',N'P') IS NOT NULLDROP PROCEDURE usp_AZRCRV_DeleteDocAttachAttachments
GOCREATE PROCEDURE dbo.usp_AZRCRV_DeleteDocAttachAttachments
@iAge INTEGER=7AS/* Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/-- CREATE TEMPORARY TABLECREATE TABLE #AttachmentsToDelete(
Attachment_ID CHAR(37))-- SELECT ATTACHMENTS OVER n YEARS OLD TO DELETEINSERT INTO #AttachmentsToDelete
CREATDDT < DATEADD(yyyy,-@iAge,GETDATE()))-- DELETE FROM Document Attachment Master (CO00101)DELETE FROM
Attachment_ID IN (SELECT Attachment_ID FROM #AttachmentsToDelete)-- DELETE FROM Document Attachment Reference (CO00102)DELETE FROM
Attachment_ID IN (SELECT Attachment_ID FROM #AttachmentsToDelete)-- DELETE FROM Document Attachment Properties (CO00103)DELETE FROM
Attachment_ID IN (SELECT Attachment_ID FROM #AttachmentsToDelete)-- DELETE FROM Document Attachment Status (CO00104)DELETE FROM
Attachment_ID IN (SELECT Attachment_ID FROM #AttachmentsToDelete)-- DELETE FROM Document Attachment Email (CO00105)DELETE FROM
Attachment_ID IN (SELECT Attachment_ID FROM #AttachmentsToDelete)-- DELETE FROM COATTACHMENTITEMSDELETE FROM
Attachment_ID IN (SELECT Attachment_ID FROM #AttachmentsToDelete)
-- DROP TEMPORARY TABLE
DROP TABLE #AttachmentsToDelete
The fifth financial enhancement is the addition of Document Attachment to the Bank Reconciliation transaction entry and transaction inquiry windows.
You are now able to attach documents in the following windows:
Bank Deposit Entry
Bank Transaction Entry
Bank Deposit Entry Zoom
Bank Transaction Entry Zoom
I’m a big fan of Document Attachment and am pleased to see it rolled out to more areas; this will allow you to attach documentary evidence of any transactions entered through the Bank Reconciliation module.
The Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP “October 2019” Release on which I am following and adding commentary. The index for this series of posts is here.
The seventh Feature of the Day is add Document Attachments to Bank Reconciliation. Document Attachment functionality has been added to Bank Reconciliation. Specifically, you can attach, view, edit documents in Bank Deposit Entry, Bank Transaction Entry, Bank Deposit Inquiry Zoom and Bank Transaction Inquiry Zoom.
You can also attach images, text files or assign attributes to the attachments to further describe them.
As I mentioned in a previous post, I have joined ISC Software Solutions, a UK headquartered Microsoft Dynamics GP VAR (Value Added Reseller) and ISV (Independent Software Vendor) with an office in Ireland.
The ISV offering from ISC Software includes the GP Elementz add-ons for Microsoft Dynamics GP as well as a set of web portals:
CustomerHQ – Provide your customers secure access to their Dynamics GP receivables account data such as invoices, statements and transaction reports.
SalesHQ – Provide sales teams with the ability to generate quotes, enter orders and invoices and view transactions, from any location via the internet.
PurchaseHQ – Extend your Dynamics GP system and allow purchasing users to manage purchasing from anywhere, via any device all in real-time. Includes integration with Microsoft Dynamics GP’s Document Attachment and Workflow module for approvals.
Key features of all of the web portals include:
Access data from anywhere and on any device.
Safe and secure portals using Microsoft Azure.
Fast and simple set up.
The user interface make the portals easy to use.
Your portal can be branded with your own logo and background image.
All portals are easy to setup and requires no technical skills or additional software or hardware. It is stored securely on Microsoft Azure and data remains in your Dynamics GP system (none is stored on Azure). It uses responsive design so it can be used on any device including cellphones and tablets.
After a clients system was upgraded a while ago from Microsoft Dynamics GP 2010 SP3 to Microsoft Dynamics GP 2018, we enabled the Document Attachment feature for the client to use. Unfortunately when showing the client how to use the feaute, we found that the Doc Attach button was missing (the red square is where the button should be):
The enhancement made to the inquiry windows, is the ability to add new attachments to a posted document. This is a most welcome addition, but one which always surprised my by its omission.
I am using a purchase order invoice as the example. Before you can add new attachments, you need to enable the security. If you don’t enable security, you’ll see the Document Attachment Inquiry window when clicking the Attach button:
Two new fields have been added to the Document Attachment Setup window (Administration area page >> Setup >> Company >> Document Attachment Setup):
I said in the Feature of the Day Post on this feature, that I couldn’t really see a use case for the additional security. In the, almost, three months since, I still haven’t come up with a scenario where I think the security is useful.
For those clients using DocAttach, they want users to be able to attach documents to any record; there is no requirements for security around the enquiry windows, and certainly not to the extent of requiring a password.
For setup purposes, there is another checkbox which will need to be marked when enabling the feature, but little more impact, so it’s not like the security is onerous.
2013 – this will be the last hotfix for GP 2013, so you should really start planning your upgrade to a supported version.
While you don’t need to install every hotfix for Microsoft Dynamics GP, I would always recommend reviewing the fix list to determine if you need to upgrade.
I am not listing all of the fixes here, just the ones which seem important or relevant to my clients:
These fixes are for 2018 only:
You may experience slow performance when you click Inquiry>>Purchasing>>Transaction By Document.
You are unable to make changes to copied workflow steps when created using the Copy Workflow Step feature.
These fixes are for both 2016 and 2018:
PM Payables Transaction Workflow A get/change operation on table ‘PM_Transcation_WORK’ failed.
Upgrade error CO00104 set BusObjKey = replace(BusObjKey,’0\PM\Payables Transaction History’,’0\PM\Payables Transaction’) where BusObjKey.
When two users simultaneously submit a document into a workflow with multiple approvers at the same time, one of the workflows will either go to the workflow manager or only be assigned to a portion of the approvers (this is an issue I logged with Microsoft which was causing issues for a couple of clients, so nice to see the fix available).
You may notice duplicate payments with simultaneous use of the payables select checks window, warning was added to the user.
You are unable to add new columns to existing Analytical Accounting SmartLists in Dynamics GP 2016 R2 or later & columns with special characters result in “excel found unreadable content” error when list is exported to excel.
Posting a GL reversing entry to a historical year with P&L account, where it reverses in the new year has incorrect SEQNUMBR in the GL20000 and incorrect ACTINDX in AAG30001 on reversing ‘GJ’ entry and 0% in AAG3000.