Script to Remove Microsoft Dynamics Workflow History Where No Action Is Required

Ian Grieve

22,782

Like ( 0 )

Report

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 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).
*/
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