Day: November 22, 2022

I created the script in this post for a client a while ago who wanted to use workflow approvals for purchase orders, but didn’t quite understand how a workflow process should be built.

He needed approximately thirty different approvals and build a step for each approver, all correct so far. Unfortunately, he created them each as a single step under a separate workflow process instead of thirty steps under one workflow process. Asking him to manually recreate all of the steps correctly under one workflow process was not going to be feasible so I did a little table exploring and came up with the below script which will move all steps for a specified workflow type under a single specified workflow process.

The two highlighted parameters at the top are where you need to specify a valid workflow type and destination workflow process. because of how the client had named his workflow processes and steps this script will change the name and description of each step to be the workflow process name and step name concatenated together and separated with a hyphen.

/*
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 @WorkflowType VARCHAR(50) = 'Purchase Order Approval'
DECLARE @WorkflowName VARCHAR(50) = 'PO Approval v1';

WITH WFUpdate AS(
	SELECT
		['Workflow Step Table'].Workflow_Name
		,['Workflow Step Table'].Workflow_Step_Name
		,ROW_NUMBER() OVER(ORDER BY ['Workflow Step Table'].Workflow_Name,['Workflow Step Table'].Workflow_Step_Sequence) * 10 AS ROWNUMBER
	FROM
		WF100003 AS ['Workflow Step Table'] -- Workflow Step Table (WF100003)
	INNER JOIN
		WF100002 AS ['Workflow Master - Step Parent'] -- Workflow Master (WF100002)
			ON
				['Workflow Master - Step Parent'].Workflow_Name = ['Workflow Step Table'].Workflow_Name
	WHERE
		['Workflow Master - Step Parent'].Workflow_Type_Name = @WorkflowType
	AND
		['Workflow Step Table'].Workflow_Step_Order = 0
)

UPDATE
	['Workflow Step Table']
SET
	Workflow_Name = @WorkflowName
	,Workflow_Step_Name = LEFT(RTRIM(['Workflow Master - Step Parent'].Workflow_Name) + - + RTRIM(['Workflow Step Table'].Workflow_Step_Name), 50)
	,WF_Step_Description = LEFT(RTRIM(['Workflow Master - Step Parent'].Workflow_Name) + - + RTRIM(['Workflow Step Table'].WF_Step_Description), 100)
	,Workflow_Step_Sequence = ISNULL(WFUpdate.ROWNUMBER, ['Workflow Step Table'].Workflow_Step_Sequence)
	,WF_Step_Predecessor = CASE WHEN LEN(['Workflow Step Table'].WF_Step_Predecessor) > 0 THEN
								LEFT(RTRIM(['Workflow Master - Step Parent'].Workflow_Name) + - + RTRIM(['Workflow Step Table'].WF_Step_Predecessor), 100)
							ELSE
								''
							END
FROM
	WF100003 AS ['Workflow Step Table'] -- Workflow Step Table (WF100003)
INNER JOIN
	WF100002 AS ['Workflow Master - Step Parent'] -- Workflow Master (WF100002)
		ON
			['Workflow Master - Step Parent'].Workflow_Name = ['Workflow Step Table'].Workflow_Name
LEFT JOIN
	WFUpdate
		ON
			WFUpdate.Workflow_Name = ['Workflow Step Table'].Workflow_Name
		AND
			WFUpdate.Workflow_Step_Name = ['Workflow Step Table'].Workflow_Step_Name
WHERE
	['Workflow Master - Step Parent'].Workflow_Type_Name = @WorkflowType

As always with scripts, before you run the one above please make sure you understand what the script will do and have a good backup of your company database.