SQL Function To Return Workflow Approval Status

● Ian Grieve ●  ● 5 Comments   ● 

Microsoft Dynamics GPIf 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(20))
	RETURNS VARCHAR(14)
AS
/*
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).

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
		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.

● Categories: Dynamics, GP, Microsoft, Workflow ● Tags: , , , , , , , , , , , ,  ● Permalink ● Shortlink ●

AD Group Names Have Maximum Lengths If Used in Workflow 2.0

● Ian Grieve ●  ● 3 Comments   ● 

Microsoft Dynamics GPI 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:

Workflow User Selection

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

● Categories: Dynamics, GP, Microsoft, Workflow ● Tags: , , , , ,  ● Permalink ● Shortlink ●

Workflow 2.0 Requires Display Names for AD Groups Used as Approver

● Ian Grieve ●  ● 4 Comments   ● 

Microsoft Dynamics GPMicrosoft 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):

Active Directory Users and Computers - New Object Group

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

● Categories: Dynamics, GP, Microsoft, Workflow ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

When exec wfDeployCLRAssemblies Just Isn’t Enough

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPI’ve posted a few times about errors in Workflow requiring the wfDeployCLRAssemblies stored procedure to be run again (1 2 3), but the Dynamics GP Support and Services Blog has posted an article about how this is sometimes not sufficient to resolve the problem and the procedures, functions and assemblies deployed by the wfDeployCLRAssemblies stored procedure need to be deleted.

To this end, the blog post contains a series of scripts which need to be run before thew wfDeployCLRAssemblies stored procedure is executed. There are versions of the script for Microsoft Dynamics 2013 R2, 2015 (including 2015 R2) and 2016 (including 2016 R2).

If your problem still isn’t fixed, then a support case would need to be raised.

● Categories: Dynamics, GP, Microsoft, Workflow ● Tags: , , , , , , , , , ,  ● Permalink ● Shortlink ●

Microsoft Dynamics GP Workflow 2.0 Second Edition Now Available on Amazon

● Ian Grieve ●  ● 3 Comments   ● 

Microsoft Dynamics GPMy sixth book is now available to buy in both eBook format from azurecurve Publishing and paperback from CreateSpace, Amazon.com and Amazon.co.uk.

This is an update to the original Workflow 2.0 book I wrote a couple of years ago. This edition includes coverage of the new functionality introduced in Microsoft Dynamics GP 2016, a new chapter on adding table joins to workflow to allow additional conditions to be created, and some chapters and sections have been rewritten to either expand or make clearer the topics being covered.

Dynamics GP includes a variety of tools and modules to assist in controlling processes and data; one of the major modules for this was the Dynamics Workflow module. However, this module had major flaws which very much limited its usefulness; it was slow, clunky and difficult to install, configure and maintain.

eBook Paperback
azurecurve Publishing Amazon.com Amazon.co.uk

Continue reading → Microsoft Dynamics GP Workflow 2.0 Second Edition Now Available on Amazon

● Categories: azurecurve Publishing, Dynamics, GP, Microsoft, Publishing, Workflow ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●

MS Connect Suggestion: Allow Additional Fields to be Avalable to Workflow Notification Emails

● Ian Grieve ●  ● 3 Comments   ● 

Microsoft Dynamics GPMy fourth Workflow suggestion has been submitted to Microsoft Connect for you to vote on.

The fields available to the Workflow email notifications are very limited, and there is no front end way of making them available. I’d like to see functionality added to Workflow Maintenance to flag fields as available to the notification emails; this would include fields from any tables available to the workflow type, including from an additional table joined to the workflow type through the enhanced Query Editor.

You can vote for this suggestion here.

● Categories: Dynamics, GP, Microsoft, Microsoft Connect, Workflow ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

Purchase Order Approval Workflow Automatically Rejected

● Ian Grieve ●  ● 4 Comments   ● 

Microsoft Dynamics GPA client reported a problem with their PO Approval Workflow process failing whenever a PO was submitted for approval. As soon as the Submit button was pressed, the document was rejected, but did not show a user:

Purchase Order Entry

Continue reading → Purchase Order Approval Workflow Automatically Rejected

● Categories: Dynamics, GP, Microsoft, Workflow ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●

Microsoft Dynamics GP Workflow 2.0 Second Edition Now Available in Paperback

● Ian Grieve ●  ● 6 Comments   ● 

Microsoft Dynamics GPMy sixth book is now available to buy in both eBook format from azurecurve Publishing and paperback. Currently the paperback is available only via CreateSpace, but should be on Amazon in the next week or so.

This is an update to the original Workflow 2.0 book I wrote a couple of years ago. This edition includes coverage of the new functionality introduced in Microsoft Dynamics GP 2016, a new chapter on adding table joins to workflow to allow additional conditions to be created, and some chapters and sections have been rewritten to either expand or make clearer the topics being covered.

Dynamics GP includes a variety of tools and modules to assist in controlling processes and data; one of the major modules for this was the Dynamics Workflow module. However, this module had major flaws which very much limited its usefulness; it was slow, clunky and difficult to install, configure and maintain.

Microsoft Dynamics GP Workflow 2.0 Second Edition

Continue reading → Microsoft Dynamics GP Workflow 2.0 Second Edition Now Available in Paperback

● Categories: azurecurve Publishing, Dynamics, GP, Microsoft, Publishing, Workflow ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●

Workflow Error Sending Email: “Execution Of User Code In The dotNET Framework Is Disabled”

● Ian Grieve ●  ● 1 Comment   ● 

Microsoft Dynamics GPThis has come up twice fairly recently. In the first case it was following an upgrade to Microsoft Dynamics GP 2016 and workflow was being introduced for the first time, and in the second it was when a client had created a new test system:

Execution Of User Code In The dotNET Framework Is Disabled

Microsoft Dynamics GP

[Microsoft][SQL Server Native Client 11.0][SQL Server]Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

Continue reading → Workflow Error Sending Email: “Execution Of User Code In The dotNET Framework Is Disabled”

● Categories: Dynamics, GP, Microsoft, SQL Server, Workflow ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

MS Connect Suggestion: Allow SQL Views to be Included in Workflow Conditions

● Ian Grieve ●  ● 4 Comments   ● 

Microsoft Dynamics GPContinuing my list of suggestions for improving Workflow, I have another one for you to vote for.

Through enabling a Dex.ini switch, an enhanced Workflow Condition Query Editor can be enabled, which allows Dexterity tables to be joined into the workflow. However, there have been a few times recently when being able to join in a SQL View would have been most beneficial. This functionality is available in SmartList Designer where Dex tables can be selected and at the bottom of the list is a node for SQL Views which displays all of the views in the database and allows them to be selected.

I’d like the same functionality made available to Workflow Maintenance to add flexibility to workflow design.

You can vote for this suggestion here.

● Categories: Dynamics, GP, Microsoft, Microsoft Connect, Workflow ● Tags: , , , , , ,  ● Permalink ● Shortlink ●