Using "Select-PWSQL" cmdlet, which table can I find the document "FullPath"?

Thanks Kevin,
There is a table called dms_proj that has all the folder names / guids, I assume that is the one you meant... Looking at that table there is o_pprjguid which should be the parent folder. With this information I can then build a fullpath by recursively walking backwards through the folder tree as you suggested; thanks for the tip that's a really good idea!

With regards to using the pwps_dab search cmdlets for returning paths, I did want to go that route however I found in practice it was far too slow to generate the list of documents when running on a very large datasource, hence looking to go the SQL route since I can download the tables I need 10x faster than a search, and then do my processing locally using PowerShell and an SQLite db.

Thanks!

Ed

Here is another variation of what Dave Cole is doing.  For dsqlGetSubFolders, if you provide a 1 for the first value, the folder specified by the folder id in the second value will be included.  The end result for this will be an array of folder objects with the fullpath values included.  Hopefully, this helps.

    $ProjectID = 177
    $SQL = "SELECT o_projguid AS GUID
        FROM dms_proj 
        WHERE o_projectno IN (
            SELECT o_projectno
            FROM dbo.dsqlGetSubFolders (1, $ProjectID, 0)
        )"

    $results = Select-PWSQL -SQLSelectStatement $SQL 
    $pwFolders = Get-PWFoldersByGUIDs -FolderGUIDs $results.GUID 

Thanks Brian! Will give that a go too... unfortunately things got busy again with other work and I haven't had a chance to finish this script off yet. Always gets a bit crazy this time of year!
Cheers,

Ed

Non PowerShell option

If you are only interested certain sub-folders you can capture the path information as a Projectwise attribute. Either at time document is created or by using a Trigger attribute. Need to use PW Admin to create the attribute(s) and works on a per environment basis.

i.e.

_PW_PATH attribute then stores path information as per follows

pw:\\XXXXXX.bentley.com:XXX\Documents\Training Test Area\XXX-YYY-9999\03 Design\FOLDER_CURRENT

Thanks Steve! That's a great idea, I hadn't thought about doing it that way... I was originally hoping that the $VAULT.FULLNAME$ variable was performing a lookup function (and then I could just find it in one of the tables) but realised afterwards this is a derived value as others mentioned in this thread. It didn't even occur to me I could instead populate these values through an environment attribute for easy access later on. Very creative, would certainly make subsequent processing a lot faster if these values have been predetermined / filled out!
Cheers,

Ed

Regarding the environment solution I also recommend adding the document GUID somewhere in the environment as well.  Then you can link the dms_doc and environment table later in SQL if needed.

Regarding the environment solution I also recommend adding the document GUID somewhere in the environment as well.  Then you can link the dms_doc and environment table later in SQL if needed.