Month: February 2022

If you’re a regular reader, you’ll be aware that I am a big fan of the Microsoft Dynamics GP Workflow module which can be used for the approval of batches, documents, security and more. Workflow can easily be customised to allow for extra fields to be available in the workflow conditions and on the notification emails (which I cover in chapters 9 and 11 of my Microsoft Dynamics GP Workflow (3rd Edition) book.

One issue with making these changes is that they can be removed or overwritten when you upgrade Microsoft Dynamics GP. The following script can be used to help check the three tables in which this can have an impact; the way I work is that I export the tables before running the upgrade and then again afterwards to see what the changes are. If any of the customisations are gone, I determine the best way of adding them back.

The highlighted section is the Workflow Type Name which you want to verify.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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).
*/
DECLARE @WorkflowTypeName VARCHAR(51) = 'Purchase Requisition Approval'
DECLARE @FieldsListGuid VARCHAR(37) = (SELECT FieldsListGuid FROM dbo.WF100001 WHERE Workflow_Type_Name = @WorkflowTypeName)

-- Query Designer Fields List
SELECT * FROM CO00121 WHERE FieldsListGuid = @FieldsListGuid ORDER BY SEQNUMBR

-- QueryDesigner_Relationships
SELECT * FROM CO00122 WHERE FieldsListGuid = @FieldsListGuid ORDER BY SEQNUMBR

-- Workflow Template Fields - Workflow Assignment
SELECT * FROM WF40202 WHERE Workflow_Type_Name = @WorkflowTypeName AND Email_Message_Type = 2 ORDER BY SEQNUMBR

-- Workflow Template Fields - Workflow Action Completed
SELECT * FROM WF40202 WHERE Workflow_Type_Name = @WorkflowTypeName AND Email_Message_Type = 3 ORDER BY SEQNUMBR

I 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 (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 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

I do demonstrations of quite a bit of functionality in Microsoft Dynamics GP which often necessitates restored databases or otherwise reversing transactions and processing. One of the modules I’ve done some demonstrations of recently is the Advanced Bank Reconciliation module from Nolan Business Solutions. One of the features of this module is allowing you to atch statement entries with transactions in Dynamics GP.

If you use the auto-reconcile rules to do the matching and then want to undo the match, you need to select each matched transaction and unmatch them and then do the next and so on, which can take quite a while. The below script will remove the matches from both the NC Matched Statement Trx 11003k (NCABR011) and NC_Matched_GL_TRX (NCABR012) tables:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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).
*/
UPDATE
	NCABR011 --NC Matched Statement Trx 11003k (NCABR011)
SET
	NC_Matched = 0
	,NC_Match_Number = 0
WHERE
	NC_Matched = 1
GO

UPDATE
	NCABR012 --NC_Matched_GL_TRX (NCABR012)
SET
	NC_Matched = 0
	,NC_Match_Number = 0
WHERE
	NC_Matched = 1
GO

This script was created for use ona demo/test system and appears to run without adverse effect, but I would recommend caution (via a backup) and thorough testing before using it on any system.

When I do an upgrade of the Web Services For Microsoft Dynamics GP, my usual approach is to ru a two Microsoft supplied scripts which remove all web services objects from the system and company databases; I generally do this as we had problems with the upgrade process within the Configuration Wizard. On a recent upgrade I thought I would give the standard upgrade process aother try, as it is a few years since I last tried it.

When the Configuration Wizard was in progress, it stopped with the below error message:

A loader exception has occurred.
Loader Errors:
- Microsoft.Dynamics.Security.InvalidWindowsAccountException: The provided windows account is not formatted correctly.
   at Microsoft.Dynamics.Security.ConcreteValidator.ValidateAndCanonicalizeMemberName(String member)
   at Microsoft.Dynamics.Security.ConcreteValidator.ValidateAndCanonicalizeMemberList(List`1 memberList)
   at Microsoft.Dynamics.Security.AzManRoleAssignmentServiceImplementation.ValidateForUpdate(SecurityContext context, SecurityObject securityObject)
   at Microsoft.Dynamics.Security.SecurityService.InvokeValidateForUpdate(SecurityContext context, SecurityObject updatedObject)
   at Microsoft.Dynamics.Security.SecurityService.Update(SecurityContext context, SecurityObject updatedObject)
   at Microsoft.Dynamics.GP.GPSecurityMetadataSystemLoader.PerformSpecialConfigTasksForAdminUsers(Action action)
   at Microsoft.Dynamics.InstallData.Loader.Process(String[] args)

The user account being used was the user account the Web Services had been running under since the last upgrade. I reverted back to running the scripts to remove the web services and then doing a fresh deployment of them. It looks like I will be sticking with this approach going forward.