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

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

I use a SQL function that ships with PW named sqlGetSubFolders.  If you provide a DMS folder number it will get all subfolders.

I use this to quickly (seconds) get all folders in a datasource. The second '0' will get the subfolders of that DMS number only

SELECT p.o_projectname, o_projectdesc, 'pw:\\(server):(datasource)/Documents/P{' + p.o_projguid + '}/' as GUID
FROM dms_proj p
WHERE p.o_projectno in (SELECT o_projectno FROM dbo.dsqlGetSubFolders (0, 0, 0))

If you need the server storage location you can loop in dms_stor to get the path on the server.

Thanks Dave, very interesting! Makes sense that Bentley already has a function to build those paths when required. Will have to test it out and see how it works

As a side note, I ended up downloading the dms_doc and dms_proj tables locally to an sqlite db using PowerShell, then built a class that walks backwards through the folder tree to build out the path and add that 'full path' to the table. Unfortunately this approach was quite time consuming when working on a very large dataset around ~100,000 folders; so I am looking at ways to optimise this further by instead walking forward through the folder tree, and also applying the full path to multiple rows at a time. This should net significant improvements since walking forward in the tree means I can focus on just the specific areas that I'm interested in, and writing multiple rows in a single operation will also reduce the processing overheads incurred by iterating over a single row at a time. Hopefully I can get this process down to something a bit more reasonable once these 2 items have been completed!
Cheers,

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