Try using the tool included with knowledge solution S-037719
Is there an example of a SQL Query / Report that lists all files in an Enterprise PDM vault that have passed through a specified workflow state?
The attached SQL query and Enterprise report shows how to get a list of files that have passed through a workflow state between certain dates.
An attached second query will include the date the file entered the state as well as a calculation of the number of days the file has been in that state if it remains there when the sql query is run. This can be useful for identifying files that will trigger the "delayed in state" notification.
Thanks for the quick response, Joy. Unfortunately, that didn't yield any results. The query works for other states though. Very strange.
Might be BOMs, in that query 6 lines from the bottom change DocumentsInProjects to BomsInProjects and run it again
Good idea, Charley. Unfortunately, that didn't get any results either.
Maybe just as simple as possible:
Declare @WorkFlowName nvarchar(50)
Declare @StateName nvarchar(50)
Set @WorkFlowName = 'Default Workflow' --<< Replace with Workflow name
Set @StateName = 'Approved' --<< Replace with State name to check
Declare @WorkflowID Int
Set @WorkflowID = (Select WorkflowID From Workflows where Name = @WorkFlowName)
Declare @StateID Int
Set @StateID = (Select StatusID From Status Where Name = @StateName And Enabled = 1 And WorkflowID = @WorkflowID)
inner join TransitionHistory th on d.DocumentID = th.DocumentID
inner join transitions t on t.TransitionID = th.TransitionID where t.Destination = @StateID
BINGO! That did it, Charley! You win!
Of course, I have to give a lot of credit to Joy also, not just for pointing me to the query in the solution ID, but teaching me how to understand the intricacies of SQL in her T-SQL classes at SWW.
Thanks again both of you!