2 Replies Latest reply on May 2, 2012 2:51 PM by Michael Woodward

    Report of files that have gone from one state to another

    Michael Woodward

      Is it possible, and if so, how, to create a report that would give me a list of files that have gone from one particular state to another? What I am being asked to deliver is the count of the number of files that are being Declined. When they are Declined, they are going from a state of Waiting For Approval to Pending Documentation. I would like to know everytime that that transition has occured. That information must be available somewhere in the database because you can see in the history of an item if that has indeed occured.

       

      Any help would be greatly appreciated. Thanks everyone.

        • Re: Report of files that have gone from one state to another
          Jason Capriotti

          The table is called TransitionHistory. It contains the DocumentID, TransitionID, and Date you can build the SQL query from. You'll have to get the TransitionID(s) from the "Transtions" table to filter it down.

           

          Here's a smaple query......looks for a transition named "Reject" in which we have several rejection transitions from various states. If you want a particular one, you'll have to track down its ID.

           

          SELECT d.filename as 'File Name', th.date as 'Date Transtioned', TH.TransitionID, T.Name as 'Transition Name', TH.Comment, Usr.FullName as 'Transitioned by'

          FROM TransitionHistory TH

          inner join Transitions T on TH.TransitionID=T.TransitionID

          inner join Documents D on D.DocumentID=TH.DocumentID

          inner join Users Usr on Usr.UserID=TH.UserID

          where t.name like 'reject'

          order by Date desc