Script to Remove Microsoft Dynamics Workflow History Where No Action Is Required

Microsoft Dynamics GPI have quite a few clients using the Microsoft Dynamics GP Workflow module as standard out of the box with no customisations required, but a fair number of clients do have one customisation or another.

A few of them have a customisation allowing a SQL view to be used in the workflow conditions and a few have a script like the one in this post to remove some of the workflow history.

If you have a workflow process which branches off so not all steps are followed, then a record is record in the history as “no action is required”; if you have a large workflow process, this can result in many history records being created which can significantly slow down some of the handling of workflow in the system (such as viewing history).

I took a look at the data and after some testing determine that entries in two tables could be removed without causing any issues and would allow the workflow history to be viewable.

The tables are Workflow Step Instance Table (WFI10003) and Workflow History (WF30100); in theory this could be set up as a trigger, but after discussion with the client we created it as a stored procedure which runs on a schedule in SQL Server Agent:

Created by Ian Grieve of azurecurve|Ramblings of an IT Professional ( This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
CREATE PROCEDURE usp_AZRCRV_DeleteWorkflowHistoryNoActionIsRequired AS -- Delete from Workflow Step Instance the lines which do not require approval DELETE FROM WFI10003 -- Workflow Step Instance Table (WFI10003) WHERE Workflow_Step_Status = 1 -- Delete from Workflow History the lines which do not require approval DELETE FROM WF30100 -- Workflow History (WF30100) WHERE Workflow_Comments LIKE 'No approval is required%' GO

Recent ISC Software Webinar: Automation in Microsoft Dynamics GP

ISC Software SolutionsIn 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:

  1. Introduction
  2. Integration
  3. Scanning
  4. Approval
  5. Posting
  6. Reporting
  7. Conclusion

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.

Continue reading “Recent ISC Software Webinar: Automation in Microsoft Dynamics GP”

Error Upgrading to Microsoft Dynamics GP Fall 2020 Release

Microsoft Dynamics GPAs I mentioned in my last post, I’m in the process of doing upgrades for a couple of clients to the Dynamics GP Fall 2020 Release. Before I started on a clients system, I thought I would upgrade my own demo/test system to the latest version. The upgrade started fine, but then in one of the companies, the upgrade crashed with two errors:

First error message

Microsoft Dynamics GP Utilities

The stored procedure SynchronizeTableData() of form duSQLAccountSynch : 27Pass Through SQL returned the following results: DBMS: 2601, Microsoft Dynamics GP: 0.

Second error message

Microsoft Dynamics GP Utilities

Microsoft Dynamics GP Utilities install/upgrade failed.

Needing some more information on the error, I enabled a Dex SQL log and ran the upgrade again.

The problem it identified was in the Workflow Template Fields (WF40202) table into which I had added some extra rows for the fields to be included on the Workflow notification emails.

Once I removed these fields, I was able to run the upgrade without further problems; after the upgrade was complete, I ran the script to recreate the rows.

Workfow Submit button on PM Transaction Entry not working in Microsoft Dynamics GP

Microsoft Dynamics GPI’ve been doing some work with a client to implement the Workflow module of Microsoft Dynamics GP for approvals of Payables Transactions and encountered an oddity; when the user creates a transactions and hits Submit, nothing happens, but was not able to reproduce this on any of my demo or test systems.

There was a customised window in use on the client system, so I removed this and tried with the standard window and got exactly the same result. Some more testing showed that when the action pane was disabled and the old style toolbar being used, the Submit button was missing.

I had tried a 2018 RTM versaion and not been able to reproduce, but this was becuase I had one setting diferent. In Payables Management Setup (Purchasing area page » Setup » Payables Management), my systems were all set to allow the voucher number to be overridden, but the client had disabled this option.

When we changed this setting in their test company, the workflow submit worked correctly.

I did some further checking and was able to confirm that this is a bug in the Dynamics GP 2018 version which was resolved in the R2 version; the workaround is to allow the voucher number to be overridden and the solution is to upgrade.

Fortunately, we are already talking to this client about upgrading Microsoft Dynamics GP to the latest version; in the short-term they’ll allow override in test while the build and test of the workflows are complete and the deploy to live should tie in with the upgrade project.

Microsoft Dynamics GP Fall 2020 Release New Features: Enable Self Service user type to access User Preferences

Microsoft Dynamics GPThis post is part of the Microsoft Dynamics GP Fall 2020 Release New Features series where I am going to echo the posts from the Dynamics GP Support and Services Blog, while adding some commentary, on the new features introduced in the Microsoft Dynamics GP Fall 2020 Release which is now available.

This post is on the Enable Self Service user type to access User Preferences System enhancement.

This feature means Self Service users will now have access to the Workflow Delegation window within User Preferences, allowing them to set their own delegations up when they know they will be unavailable:

Workflow User Delegation

This is a nice addition which will make it a lot easier for clients to properly manage delegations.

Click to show/hide the Microsoft Dynamics GP Fall 2020 Release New Features Series Index

Error opening Word templates from Microsoft Dynamics GP Workflow Action emails

Microsoft Dynamics GPA client reported a problem with workflow approvers opening the batch edit list on a Microsoft Dynamics GP General Ledger Batch Approval workflow action email this morning; only a few users are having problems while everyone else is working without problem. The users with the problem are getting this error when trying to open the Transaction Edit List report on the email:

Word experienced an error trying to open the file...

Microsoft Word

Word experienced an error trying to open the file.
Try these suggestions.
* Check the file permissions for the document or drive.
* Make sure there is sufficient free memory and disk space.
* Open the file with the Text recovery converter.
(C:...transaction Edit List.docx)

I did some research and found there was a recent post on the Dynamics GP Support and Services Blog where they covered this same issue. The blog post was only four days ago and they acknowledge the issue is ongoing and offer the workaround of downgrading Microsoft Office to work around the issue (I did this recently when sending emails using MAPI caused Dynamics GP to crash).

The problem does not just affect the Word template reports from Workflow Batch Approval emails, but also the Word templates used for sending purchase or sales orders, remittances and so on. For the non workflow Word templates, you could reconfigure the templates to use PDF, or one of the other formats, instead of Word, but, unfortunately, this option is not available for the batch edit lists.

Following the initial report, we’ve had a number of other clients report the issue, so it may be worth preempting problems and block Office updates for Dynamics GP users until a fix is in place from Microsoft.

Show Workflow Approval Status of posted Microsoft Dynamics GP General Ledger batches

Microsoft Dynamics GPI recently deal with a support call for a client where they were having problems creating a report which showed the approval status of GL batches. Building the WfBusObjKey was more complicated than I expected as it requires the date and time from the batch; for an unposted journal this can easily be retrieved from the Posting Definitions Master Dup (SY00500) table, but for posted batches I had to do some exploring to find where the data was stored.

The table was the Posting Definitions Master History (SY30500); I’ve obviously never written a query which used this table as the name dones;t look familiar, but once I had the tale, it was a simple task to concatenate the fields together to make the WfBusObjKey and pass it to my function which returns the Workflow Approval Status.

I’ve used the SQL query to create a view so that it could easily be hooked into SmartList Builder, Refreshable Excel or other reports::

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_GeneralLegderBatchApprovalStatus', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_GeneralLegderBatchApprovalStatus
-- create view
CREATE VIEW uv_AZRCRV_GeneralLegderBatchApprovalStatus AS
	['Year-to-Date Transaction Open'].OPENYEAR AS 'Open Year'
	,['Year-to-Date Transaction Open'].JRNENTRY AS 'Journal Entry'
	,['Year-to-Date Transaction Open'].SEQNUMBR AS 'Sequence Number'
												'General Ledger Batch Approval'
												,CAST(RTRIM(['Posting Definitions Master History'].BACHNUMB) AS VARCHAR(15))
													+ '~' + RTRIM(['Posting Definitions Master History'].BCHSOURC) 
													+ '~' + FORMAT(['Posting Definitions Master History'].CREATDDT, 'yyyy/M/d') 
													+ '~' + FORMAT(['Posting Definitions Master History'].TIME1 ,'HH:mm:ss')
											) AS 'Wordflow Approval Status'
       GL20000 AS ['Year-to-Date Transaction Open'] WITH (NOLOCK)
	SY30500 AS ['Posting Definitions Master History'] WITH (NOLOCK)
			['Posting Definitions Master History'].TRXSORCE = ['Year-to-Date Transaction Open'].TRXSORCE
GRANT SELECT ON uv_AZRCRV_GeneralLegderBatchApprovalStatus TO DYNGRP

Microsoft Dynamics GP Workflow Payables Batch Approval email not including Word template

Microsoft Dynamics GPA client has recently been working on a project to implement approval workflows in a few modules of Microsoft Dynamics GP, but had an issue with the Payables Transaction Approvals notification emails not always including the Word template which all of the steps are marked to include.

There appeared to be no pattern on which approval emails either did or did not include the Word template; a user would receive the Word template on to emails, then not on the third, but again receive it on the fourth.

Doing some searching online found a post by Rob Klaproth of Armanino where he had the same issue and confirmed that there is a known issue logged with Microsoft whereby when the user submits a document for approval, and clicks submit on the comment box before the Word template has finished generating the email will not include the Word template.

The workaround for this issue is to make sure that users wait until the Word template has finished processing, as shown by the Template processing message at the bottom of the main Microsoft Dynamics GP window. If they wait until this processing has completed before hitting submit, the Word template will be included on the notification email.

This issue affects all of the edit list Word template attachments through all of the Workflow approval types.

Incorrect Microsoft Dynamics GP Purchase Orders Workflow Status

Microsoft Dynamics GPI recently had an error reported to me in Microsoft Dynamics GP where some final approved purchase orders were not working correctly when opened in the Purchase Order Entry window (Purchasing area page » Transactions » Purchase Order Entry).

The PO is shown as not submitted in the workflow status bar, but the button on the tool is a Recall button rather than a Submit one showing that the document has been submitted:

PO Entry window showing workflow status bar and action pane

Continue reading “Incorrect Microsoft Dynamics GP Purchase Orders Workflow Status”

Workflows available in Microsoft Dynamics GP

Microsoft Dynamics GPSince Microsoft Dynamics GP 2013 R2 saw the introduction of a new Workflow module for approval of documents, almost every version of Dynamics GP since then has introduced new functionality to workflow; sometimes in the form of new approval types or integration with other modules such as Document Attachment.

This diagram shows the available workflow types, colour-coded by version introduced:

Available workflow approval types

If you are looking to implement workflow, I have a book which can guide you though configuring the Microsoft Dynamics GP Workfow module from the basics of designing your workflow process, through building it and the ways that users can interact with the workflow for approvals.

The Workflow module is quite popular and becoming much more so with so many clients working in a distributed manner. There is one problem on the horizon and that is the retirement of TLS 1.0 and 1.1 which the Web Services for Microsoft Dynamics GP rely on. We are waiting for confirmation as to when a fix for this will be available, but I am expecting the fix to require an upgrade of Microsoft Dynamics GP which isn’t always quickly possible.

Fortunately, the company for which I work, ISC Software, are both a VAR and an ISV with a number of products and services available.

One of those products is PurchaseHQ which provides an Azure hosted web portal through which users can log on to do their purchasing series approvals. It fully integrates with the Workflow module in Microsoft Dynamics GP (blog and video) for document approvals (blog and video). The portal is quite a popular approach for organisations with a lot of approvals which need to be done as approvers have a web page they can logon to to see all of their approvals for all companies in one list, rather than receiving one email per approval.