Deleting Already Used Workflows and States in SOLIDWORKS PDM

If you’re a SOLIDWORKS PDM Administrator, you have most likely run into this issue: You need to change your workflow but if you try to delete a workflow state and save you get the dreaded message “The workflow was successfully saved but some states could not be deleted since there are files that are or have been in those states.”

The reason this is strictly controlled is because of the file history. If the file is ever rolled back to the point in time it was in that workflow state, it needs to exist.

Disclaimer: The solutions provided in this blog involve manual manipulation of the database, which is not officially supported by SOLIDWORKS. You should always make a backup of the database before making any changes. It is also highly recommended that you perform the changes in a test environment before implementing in your production vault.

Traditionally, there are a couple of solutions to this problem. The first is to find the files that have been in the workflow and delete/destroy them. This can be done with a simple search. In the Complete Search, go to the Workflow tab and select the workflow state in question then check the box to ‘include files that have been in the specified state at least once’.

If the files cannot be deleted, traditionally, the only way to handle it would be to either repurpose the state or disconnect it from the transitions and leave it in the workflow. It is possible to point these file versions to another workflow state, which then will allow you to remove the state from the workflow.

First, get a full understanding of what files will be affected by the change. Run the following query inside SQL Server Management Studio:

declare @State as varchar(50) = ‘%’

declare @Workflow as varchar(50) = ‘%’

select

d.filename, t.Name as [transition name], w.Name [workflow name], s.name [Destination], ss.name [Source], th.TransitionNr [transition number]

from

TransitionHistory th

inner join documents d on d.DocumentID = th.DocumentID

inner join transitions t on t.TransitionID = th.transitionid

inner join status s on s.StatusID = t.Destination

inner join status ss on ss.StatusID = t.Source

inner join workflows w on w.workflowid = t.WorkflowID

where

w.name like @workflow and (s.name = @State or ss.name = @State)

Replace the % characters with the full name of the State and Workflow you wish to get rid of. It will show you the files, transition name used at the time, workflow name, status, and transition number. The transition number can be matched up to the rows in the file history.

Once you have confirmed what state you wish to re-reference the history to you can run the following two queries. The first will change the source for all transitions that started from that state. The second will change the destination for all transitions that ended at that state:

Update Source:

declare @NewState as varchar(50) = ‘%’

declare @NewWorkflow as varchar(50) = ‘%’

declare @OldState as varchar(50) = ‘%’

declare @OldWorkflow as varchar(50) = ‘%’;

update transitions

set transitions.source = (select s.StatusID from status s inner join workflows w on w.WorkflowID = s.WorkflowID where s.name = @NewState and w.name = @NewWorkflow)

from

transitionhistory th

inner join documents d on d.DocumentID = th.DocumentID

inner join transitions t on t.TransitionID = th.transitionid

inner join status de on de.StatusID = t.Destination

inner join status ss on ss.StatusID = t.Source

inner join workflows w on w.workflowid = t.WorkflowID

where

w.name like @oldworkflow and (ss.name = @oldState)

Update Destination:

declare @NewState as varchar(50) = ‘%’

declare @NewWorkflow as varchar(50) = ‘%’

declare @OldState as varchar(50) = ‘%’

declare @OldWorkflow as varchar(50) = ‘%’;

update transitions

set transitions.destination = (select s.StatusID from status s inner join workflows w on w.WorkflowID = s.WorkflowID where s.name = @NewState and w.name = @NewWorkflow)

from

transitionhistory th

inner join documents d on d.DocumentID = th.DocumentID

inner join transitions t on t.TransitionID = th.transitionid

inner join status de on de.StatusID = t.Destination

inner join status ss on ss.StatusID = t.Source

inner join workflows w on w.workflowid = t.WorkflowID

where

w.name like @oldworkflow and (de.name = @oldState)

Replace the % characters with the full name of the State and Workflow for the old state and the new state you wish to replace it with.

Note: For this to work, the files can not currently be in the state to be deleted, otherwise the CurrentStatusID in the Documents table will need to be updated before the workflow can be deleted. Additionally, if you are planning on completely deleting the workflow, you must update the workflowID for the transitions so it doesn’t associate at all.

Once complete, you should then be able to delete the state in the workflow.

Related Articles

SOLIDWORKS PDM Administrator 101: Topology & Latency (Part 1)

Are You Sure it’s a SOLIDWORKS PDM Permission?

Resetting the PDM Admin Password in SOLIDWORKS

About the Author

Lindsay Early is an Application Engineer Consultant and has been with Fisher Unitech for six years. Lindsay is a Certified SOLIDWORKS Expert and Elite Application Engineer having earned over 14 SOLIDWORKS Certifications including Mechanical Design, Simulation, and Data Mangement.