5 Replies Latest reply on Aug 10, 2011 10:47 AM by Michael Dekoning

    Query to find locked files in a folder (project)

    Chris Mueller

      I spent a little time trying to aquaint myself with the SQL queries used to gather data from EPDM.  I was able to make some small changes and get the query to run, but I am not skilled enough to create a query that I really want.  Here is the objective:

       

      A query that will return a list of all locked files within a certain project, even files that have never been checked into EPDM (i.e. created on the user's computer, but not ever checked in).  Such files are problematic for me because I cannot see them using the usual EPDM tools, such as search (I'm a local admin and don't have access to the Admin account, from which these files can apparently be seen).  These files are also the files that prevent me ultimately from doing things like changing folder names.

       

      Here is where I started.

       

      @[Checked Out Files]

      §Name [Checked Out Documents]

       

      §Company [BiffsBolagAB]

       

      §Description

      [This query lists all documents checked out.]

       

      §Version [1.0]

       

      §Arguments

      [

      String   Days [N] [Enter minimum days to report.]

      ]

       

      §Sql

      [          

      SELECT  DateDiff(day, D.LockDate, GetDate()+1) as 'Days Checked Out'

                          , U.Username as 'Checked Out By'

                          , D.Filename as 'Document'

                          , D.LockDomain as 'Locked on Computer'

                          , D.LockPath as 'Locked in Folder'

      FROM          Documents as D

      INNER JOIN Users as U

      On D.UserID = U.UserID

      And U.UserID <> '1'

      WHERE DateDiff(day, D.LockDate, GetDate()+1) >= {days}

      And D.Deleted = 0

      ORDER BY U.Username, 'Days Checked Out' DESC

      ]

       

      This query was written by another forum user to generate a list of checked out files that have been checked out more than a certain number of days.  This query is ROBUST! It returns everything that could even think about being a checked out (locked) file (i.e. even local files on users machines that never have been checked in).

       

      Now I want to remove the condition that the files be checked out for a number of days, but add the condition that they lie within a certain folder (project).  I have seen some other queries where the argument is the ProjectID:

       

      §Arguments

      [

      ProjectID pProjectID [1] [Select folder. E.g "$\Documents", or browse for folder.]

      ]

       

      And I think that I somehow need to JOIN the projectID with the list created by the query above, right?  But this is where I get stuck due to my lack of SQL experience, programming skills, examples, etc.

       

      Anyone that can help?

        • Re: A query about a query
          Michael Dekoning

          Chris,

           

          The ProjectID is actually stored in the Documents table for "locked" files as LockProject. Try the code below.

           

          @[Checked Out Files]

          §Name [Checked Out Documents]

           

          §Company [BiffsBolagAB]

           

          §Description

          [This query lists all documents checked out.]

           

          §Version [1.0]

           

          §Arguments

          [

          ProjectID pProjectID [1] [Select folder. E.g "$\Documents", or browse for folder.]

          ]

           

          §Sql

          [          

          SELECT U.Username as 'Checked Out By'

                              , D.Filename as 'Document'

                              , D.LockDomain as 'Locked on Computer'

                              , D.LockPath as 'Locked in Folder'

          FROM          Documents as D

          INNER JOIN Users as U

          On D.UserID = U.UserID

          And U.UserID <> '1'

          WHERE D.LockProject = {pProjectID}

          And D.Deleted = 0

          ORDER BY U.Username

          ]

           

          Mike

          EPDM 2011 SP2

           

          Modified my original response to correct the ORDER BY clause.

            • Re: A query about a query
              Chris Mueller

              I got an Sql error.  Did this run well on your report generator?

               

              This is the SQL-code that was executed:

              SELECT U.Username as 'Checked Out By'

                                  , D.Filename as 'Document'

                                  , D.LockDomain as 'Locked on Computer'

                                  , D.LockPath as 'Locked in Folder'

              FROM          Documents as D

              INNER JOIN Users as U

              On D.UserID = U.UserID

              And U.UserID <> '1'

              WHERE D.LockProject = 15802

              And D.Deleted = 0

              ORDER BY U.Username, 'Days Checked Out' DESC