Category: GP (Great Plains)

I was doing some work for a client to build a workflow process in Microsoft Dynamics GP recently and we only got the SMTP details quite late in the process. This meant that we built quite a large workflow process without being able to enable the email notifications. Once we did have the SMTP available we needed to go back through all of the steps and enable the email notifications.

Rather than do this manually, which would have been a time consuming process, I looked at the tables and identified the table and fields which would need to be updated. This was the Workflow Step Table (WF100003) table; the below script can be used to enable the notifications, set the Message ID and enable the include Document Attachments (this was for a payables batch approval process):

/*
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
	WF100003
SET
	EmailMessageID = '^ASSIGN PO APPROVAL'
	,Workflow_Step_Send_Email = 1
	,WFIncludeDocumentAttach = 1
WHERE
	Workflow_Step_Send_Email = 0

As always with scripts, make sure you have a good backup and test before using on a live system.

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:

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.

A client reported a problem with some VBA customisations in Microsoft Dynamics GP where the code was stopping as if at a debug point in two places even thought there were no debug points present. Once a user hit F5 to continue the code past a “debug” point, they would not see the problem again until the next time they logged in.

I did a lot of investigation and fiddling around, but could not work out what the issue was.

Eventually, I used Customisation Maintenance to export the two problematic packages and then imported them again. When we tested, the problem had gone away.

I do not know what caused the problem, but it looks like even though there were no visible debug points there was something in the two files making Dynamics GP appear as if there was.

I’d be interested in hearing from anyone who has encountered this issue before and might be able to explain what was happening.

We have a project on the go to replace the VBA modifications, which are quite complex, so for this client the should only be a short term possibly of it reoccurring.

I worked on a Microsoft Dynamics GP upgrade project recently for a client who uses the old Adobe statement process. I am not all that familiar with this as most clients are now using the On Blank Paper statement and the standard emailing functionality. Once the upgrade was complete and we were doing some basic testing we encountered an error when trying to email the statements:

I did some digging around and found there is a path stored in the Dex.ini file for the e-mail status report:

EmailStatusPath=\\fileserver\Dynamics Central\EmailStatus\

This can be set through the client, but with a number of clients to update it was quicker to do by adding the line to the Dex.ini file.

I received an urgent call from a client early one morning a short time ago. All users logging into Microsoft Dynamics GP were receiving multiple errors across the system rendering it unusable.

The first reported error was displayed on login and encountered by all users:

Continue reading “Multiple Errors in Microsoft Dynamics GP”