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:
Microsoft Dynamics GP
You cannot print an unauthorized purchase order
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(20))
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
Employee Profile Approval
Employee Skills Approval
Payroll Direct Deposit Approval
Payroll Timecard Approval
Payroll W4 Approval
Expense Report Approval
Smartlist Designer View Approval
SELECT TOP 1
CASE WHEN ['Workflow History'].Workflow_Action = 1 THEN
WHEN ['Workflow History'].Workflow_Action = 2 THEN
WHEN ['Workflow History'].Workflow_Action = 3 THEN
WHEN ['Workflow History'].Workflow_Action = 4 THEN
WHEN ['Workflow History'].Workflow_Action = 5 THEN
WHEN ['Workflow History'].Workflow_Action = 6 THEN
WHEN ['Workflow History'].Workflow_Action = 7 THEN
WHEN ['Workflow History'].Workflow_Action = 8 THEN
WHEN ['Workflow History'].Workflow_Action = 9 THEN
WHEN ['Workflow History'].Workflow_Action = 10 THEN
WF30100 AS ['Workflow History']
WFI10002 AS ['Workflow Master']
['Workflow Master'].WorkflowInstanceID = ['Workflow History'].WorkflowInstanceID
['Workflow Master'].Workflow_Type_Name = @WorkflowTypeName
['Workflow Master'].WfBusObjKey = @WfBusObjKey
['Workflow History'].DEX_ROW_ID DESC)
GRANT EXECUTE ON uf_AZRCRV_GetWorkflowApprovalStatus TO DYNGRP
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.
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:
- Make the eConnect service account an admin in Local Users and Groups.
- Start the service and note that it started correctly and continued to run.
- Stop the service
- Remove the eConnect Service from the Admins group.
- Start the service and again note that it started and continued to run.
The service has continued to run correctly since then.
I’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.
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.
I’ve been doing some work recently on upgrading some Dexterity code to work with Microsoft Dynamics GP 2016 (both the RTM and R2). After compiling the new dictionary, I started Microsoft Dynamics GP and as the home page was loading got the following error:
Unhandled script exception:
SCRIPTS - data area
Continue reading “Dexterity Development ‘Unhandled script exception’ on data area”
I was working on upgrading some Dexterity code a little while ago and encountered a problem in the code. I tried to enter debug mode and received a message that Utilities for the custom dictionary needed to be run:
I spent a little while looking into why this was happening and found a few posts saying to copy the Dex.ini file into a Data folder in the dictionary location, but this had no effect.
Instead the Dex.ini file should be copied into a folder under the install location of Dexterity, which for Dexterity 16 (Microsoft Dynamics GP 2016) is, by default:
%ProgramFiles(x86)%\Microsoft Dexterity\Dex 16.0
Once the Dex.ini file had been copied into the installation folder of Dexterity, I was able to enter debug mode without further issue.