Enterprise PDM workflows represent internal approval or release processes practiced within your company. You can use the workflow capabilities to automate a manual or papers based process, notify users of the document status and also control user or group access to files. But what happens after you submit that document for approval? It seems the question "Can I set an automatic reminder to alert someone if a file has been in a state beyond a certain amount of days?" comes up frequently when talking about workflows.
With the new task functionality available in EPDM 2010 you can write a task add-in to schedule a task that checks the number of days files have been in a selected workflow state and send a message to a selected individuals or groups for any files that have been in that state beyond a set number of days.
But what about those not familiar with API and are not comfortable with writing an add-in? Using the EPDM report generator you can create a report that will query the database and return a list of files that have been in a workflow state longer then the specified amount of time. The downside to this - no notification is sent out. Now you have this nice report to retrieve the information but someone has to log into the vault to run this report. So now the question is can I automate this report?
Using the SQL Server Reporting Services you can create a report using the same query mentioned above. Once this report has been configured a user can subscribe to this report and have it e-mailed to them on a scheduled basis; for example, every morning. Now each morning the users will get an e-mail containing a list of files that have been in a workflow state longer than the set amount of days.
Pretty simple right? Wait a minute, there is a lot going on behind the scenes in that EPDM database so how do you know what tables to query and how to get all this info put together? Of course you do need to have a basic understanding of T-SQL to create the reports. If you are comfortable with T-SQL listed below is the SQL query needed to get the info:
-- Query to return files in specified workflow state over set amount of days
SELECT Distinct D.Filename As [File Name],
CONVERT(VarChar, TH.Date, 101) AS [Entered State],
DATEDIFF (dd, TH.Date, GETUTCDATE()) as [Days In State]
FROM Status AS S
INNER JOIN Documents AS D ON S.StatusID = D.CurrentStatusID
Join TransitionHistory AS TH ON D.DocumentID = TH.DocumentID
Where S.Name = 'Work in Progress' --<<>
and DATEDIFF (dd, TH.Date, GETUTCDATE()) > '5' --<<>
Order By DATEDIFF (dd, TH.Date, GETUTCDATE()) DESC
So now you have all the tools you need to answer the age old question “How long has that document been waiting for approval?”.
Posts
