Script to Help Check For Missing Microsoft Dynamics GP Workflow Customisations

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