Year: 2022

A little while ago I did some work with a client who was migrating their Microsoft Dynamics GP VBA modifications into C# so that they could make more use of the web client. We had the C# code created, and the VBA-mess modified forms and reports loaded into Dynamics GP. The next step is to use dag.exe to create the managed code assembly which provides access to resources in an application dictionary.

On one of the machines we installed Dexterity in order to get access to dag.exe and then ran the command required to create the managed code assembly. Unfortunately, the following error was produced:

Unhandled exception: System.IO.FileNotFoundException: Could not load file and assembly 'Microsoft.Dynamics.GP.BusinessObjects, Version=18.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad64e35' or ne of its dependencies. The system cannot find the file specified.

It was only seeing the error message that I remembered having been told about this by the developer. The Microsoft.Dynamics.GP.BusinessObjects.dll file needs to be copied from the Dynamics GP install folder (typically %ProgramFiles(x86)%\Microsoft Dynamics GP) into the Dexterity install folder (typically %ProgramFiles(x86)%\Microsoft Dexterity\Dex 18.0).

Once this has been done, the dag.exe command can be run successfully.

I created the script in this post for a client a while ago who wanted to use workflow approvals for purchase orders, but didn’t quite understand how a workflow process should be built.

He needed approximately thirty different approvals and build a step for each approver, all correct so far. Unfortunately, he created them each as a single step under a separate workflow process instead of thirty steps under one workflow process. Asking him to manually recreate all of the steps correctly under one workflow process was not going to be feasible so I did a little table exploring and came up with the below script which will move all steps for a specified workflow type under a single specified workflow process.

The two highlighted parameters at the top are where you need to specify a valid workflow type and destination workflow process. because of how the client had named his workflow processes and steps this script will change the name and description of each step to be the workflow process name and step name concatenated together and separated with a hyphen.

/*
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 @WorkflowType VARCHAR(50) = 'Purchase Order Approval'
DECLARE @WorkflowName VARCHAR(50) = 'PO Approval v1';

WITH WFUpdate AS(
	SELECT
		['Workflow Step Table'].Workflow_Name
		,['Workflow Step Table'].Workflow_Step_Name
		,ROW_NUMBER() OVER(ORDER BY ['Workflow Step Table'].Workflow_Name,['Workflow Step Table'].Workflow_Step_Sequence) * 10 AS ROWNUMBER
	FROM
		WF100003 AS ['Workflow Step Table'] -- Workflow Step Table (WF100003)
	INNER JOIN
		WF100002 AS ['Workflow Master - Step Parent'] -- Workflow Master (WF100002)
			ON
				['Workflow Master - Step Parent'].Workflow_Name = ['Workflow Step Table'].Workflow_Name
	WHERE
		['Workflow Master - Step Parent'].Workflow_Type_Name = @WorkflowType
	AND
		['Workflow Step Table'].Workflow_Step_Order = 0
)

UPDATE
	['Workflow Step Table']
SET
	Workflow_Name = @WorkflowName
	,Workflow_Step_Name = LEFT(RTRIM(['Workflow Master - Step Parent'].Workflow_Name) + - + RTRIM(['Workflow Step Table'].Workflow_Step_Name), 50)
	,WF_Step_Description = LEFT(RTRIM(['Workflow Master - Step Parent'].Workflow_Name) + - + RTRIM(['Workflow Step Table'].WF_Step_Description), 100)
	,Workflow_Step_Sequence = ISNULL(WFUpdate.ROWNUMBER, ['Workflow Step Table'].Workflow_Step_Sequence)
	,WF_Step_Predecessor = CASE WHEN LEN(['Workflow Step Table'].WF_Step_Predecessor) > 0 THEN
								LEFT(RTRIM(['Workflow Master - Step Parent'].Workflow_Name) + - + RTRIM(['Workflow Step Table'].WF_Step_Predecessor), 100)
							ELSE
								''
							END
FROM
	WF100003 AS ['Workflow Step Table'] -- Workflow Step Table (WF100003)
INNER JOIN
	WF100002 AS ['Workflow Master - Step Parent'] -- Workflow Master (WF100002)
		ON
			['Workflow Master - Step Parent'].Workflow_Name = ['Workflow Step Table'].Workflow_Name
LEFT JOIN
	WFUpdate
		ON
			WFUpdate.Workflow_Name = ['Workflow Step Table'].Workflow_Name
		AND
			WFUpdate.Workflow_Step_Name = ['Workflow Step Table'].Workflow_Step_Name
WHERE
	['Workflow Master - Step Parent'].Workflow_Type_Name = @WorkflowType

As always with scripts, before you run the one above please make sure you understand what the script will do and have a good backup of your company database.

A common request from clients using Microsoft Dynamics GP workflow was a way to easily be able to review to whom workflow steps were assigned; this was often a request from auditors.

I produced a SQL view which could easily be deployed for use in any reporting tool (SmartList Designer or SmartList Builder, SSRS, refreshable Excel, etc.) and which returned who the step was assigned as well showing the predecessor step.

/*
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 VIEW uv_AZRCRV_GetWorkflowSetupStepAssignment AS
SELECT
       ['Workflow Step Instance Table'].Workflow_name
       ,['Workflow Step Instance Table'].WF_Step_Predecessor
       ,['Workflow Step Instance Table'].Workflow_Step_Name
       ,['Workflow Step Instance Table'].WF_Step_Description
       ,['Workflow Step Instance Table'].EmailMessageID
       ,['Workflow Users'].ADLogin
       ,['Workflow Users'].ADDisplayName
FROM
       WF100003 AS ['Workflow Step Instance Table']
LEFT JOIN
       WF40200 AS ['Workflow Users']
ON
       ['Workflow Step Instance Table'].Workflow_Step_Assign_To = ['Workflow Users'].UsersListGuid
GO

GRANT SELECT ON uv_AZRCRV_GetWorkflowSetupStepAssignment TO DYNGRP
GO

I did some work for a client a while ago where we created a lot of email steps in Microsoft Dynamics GP workflow after which the client decided they would be using email after all and needed email on those steps activating and a custom message assigned. Rather than going through each step manually and switching the email on, I created a small script to do the job for us.

The script was for a PM Batch Approval type so also enables the include Document Attachment option.

The highlighted section is the name of the E-Mail Message ID to assign:

UPDATE
	WF100003
SET
	EmailMessageID = 'AZRCRV PO APPROVAL'
	,Workflow_Step_Send_Email = 1
	,WFIncludeDocumentAttach = 1
WHERE
	Workflow_Step_Send_Email = 0

As always before running a script make sure you understand what it does, test it on a test system before deploying live and make sure you have a good backup before running and verify afterwards.