Month: April 2017

I have been doing a lot of work recently on Purchase Order Processing and Workflow 2.0. One of the requests I had was for a SmartList report which allowed the purchase order (PO) to be compared back to the purchase requisition (PR) it originated from. I had to take a few minutes to investigate as I knew the link from PR to PO was maintained within the system, but I wasn’t sure of the table.

It took me a little longer to determine which table held the link as I was expecting a company table, but instead found that the link was stored in the SOP_POPLink table (SOP60100). One fairly quick view later and I have the basis for the required report, and probably for some others in future too.

Continue reading “SQL Script Linking Purchase Orders to Purchase Requisitions”

The below error message was reported to me by a user performing UAT (User Acceptance Testing) on an implementation of Purchase Order Processing and Workflow 2.0:

This error was somewhat surprising as it was produced when trying to print a purchase order (PO) which had been converted from a purchase requisition (PR). There was a workflow process on the PR, which had been final approved and the PO created, but there was not a workflow on the PO.

I double checked to make sure that the old Purchase Order Enhancements (POE) Approvals functionality was disabled (it was) as the customer had previously been using this.

It was when I was checking for POE Approvals, that I realised that, as part of the project, we had enabled the PO Commitments part of POE.

The warning message was being produced as the PO line had exceeded the commitment budget value. Loading the PO, clicking the save button, accepting the override of the commitment budget value, reloading the PO and clicking the Print button successfully printed the PO.

If you have been paying any sort of attention, you will know that I do quite a lot of work with the Workflow 2.0 module of Microsoft Dynamics GP.

We have created a number of reports in the past which allows the status of transactions or card going through a Workflow Approval process; to simplify matters somewhat, I created a SQL function which can be called with the Workflow Type Name and the Workflow Business Object Key and have the approval status returned.

CREATE FUNCTION dbo.uf_AZRCRV_GetWorkflowApprovalStatus(@WorkflowTypeName CHAR(50),@WfBusObjKey CHAR(200))
	RETURNS VARCHAR(14)
AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://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).

Returns Workflow Approval status of a specified workflow item.

Requires input parameters of WorkflowTypeName and WfBusObjKey

Valid Workflow Type Names are (as of Microsoft Dynamics GP 2016 R2):
	General Ledger Batch Approval
	Receivables Batch Approval
	Payables Batch Approval
	Payables Transaction Approval
	Purchase Order Approval
	Purchase Requisition Approval
	Vendor Approval
	Employee Profile Approval
	Employee Skills Approval
	Payroll Direct Deposit Approval
	Payroll Timecard Approval
	Payroll W4 Approval
	Expense Report Approval
	Timesheet Approval
	Smartlist Designer View Approval
*/
BEGIN
	RETURN ISNULL((
		SELECT TOP 1 
			CASE WHEN ['Workflow History'].Workflow_Action = 1 THEN
				'Submitted'
			WHEN ['Workflow History'].Workflow_Action = 2 THEN
				'Resubmitted'
			WHEN ['Workflow History'].Workflow_Action = 3  THEN
				'Approved'
			WHEN ['Workflow History'].Workflow_Action = 4 THEN
				'Task Complete'
			WHEN ['Workflow History'].Workflow_Action = 5 THEN
				'Rejected'
			WHEN ['Workflow History'].Workflow_Action = 6 THEN
				'Delegated'
			WHEN ['Workflow History'].Workflow_Action = 7 THEN
				'Recalled'
			WHEN ['Workflow History'].Workflow_Action = 8 THEN
				'Escalated'
			WHEN ['Workflow History'].Workflow_Action = 9 THEN
				'Edit'
			WHEN ['Workflow History'].Workflow_Action = 10 THEN
				'Final Approved'
			END
		FROM 
			WF30100 AS ['Workflow History']
		INNER JOIN
			WFI10002 AS ['Workflow Master']
				ON
					['Workflow Master'].WorkflowInstanceID = ['Workflow History'].WorkflowInstanceID
		WHERE
			['Workflow Master'].Workflow_Type_Name = @WorkflowTypeName
		AND
			['Workflow Master'].WfBusObjKey = @WfBusObjKey
		AND
			['Workflow History'].Workflow_Action <> 11
		ORDER BY
			['Workflow History'].DEX_ROW_ID DESC)
	,'Not Submitted')
END
GO

GRANT EXECUTE ON uf_AZRCRV_GetWorkflowApprovalStatus TO DYNGRP
GO

This view can be called from a SmartList created using either SmartList Designer, SmartList Builder, via a Reporting Services or Excel Report or any other type of report.

Updated 09/02/2022 – Added the highlighted clause which is needed to filter out no action required status after final approval (thanks to Calum for the reminder).

I am working with a few different clients on projects to implement Workflow 2.0 and came across an issue with one of them when selecting an AD Group as an approver. I was able to reproduce the issue on my demo machine so knew it was not a client environment specific issue.

When the lookup was performed on the group name, the group was returned, but the login name was truncated:

Continue reading “AD Group Names Have Maximum Lengths If Used in Workflow 2.0”

Microsoft Dynamics GP supports the use of both Active Directory Groups and Users as approvers, but there is a minor catch in the creation of groups which it is easy to stumble over. If you create the group as usual via the Active Directory Users and Computers applet you set a Group name and Group name (pre-Windows 2000):

Continue reading “Workflow 2.0 Requires Display Names for AD Groups Used as Approver”

eConnect is both a very useful tool for integrating data into Microsoft Dynamics GP, and also hugely frustrating. I assisted a client in creating a new test system recently and had some problems with eConnect. I installed it as normal, but the service didn’t start; when I tried to start the service manually, it started and immediately stopped without showing an error message.

I checked in the Windows Event Viewer for any errors and, again, there were none.

I’ve previously had issues with the eConnect service not starting and I figured it was worth a try.

The post linked above, has step by step screenshots, but the gist of what I tried is as follows:

1. Make the eConnect service account an admin in Local Users and Groups.
2. Start the service and note that it started correctly and continued to run.
3. Stop the service
4. Remove the eConnect Service from the Admins group.
5. Start the service and again note that it started and continued to run.

The service has continued to run correctly since then.

I have worked with Microsoft Dynamics GP since 2003, but the first Fabrikam Day passed by without me noticing it. Unfortunately, I am out onsite today so will have to celebrate #FabrikamDay without my team.