HowTo: Get ProjectWise Folders By GUID Using dsqlGetSubFolders

Be sure to check out my Scripting4Crypto initiative. It’s a fun way to get into using cryptocurrencies all while getting your PowerShell needs met.

Did you know that there is a SQL function provided with ProjectWise which will return all folders within a provided ProjectWise folder specified by the corresponding folder id. The name of the function is dsqlGetSubFolders. In this post, I will show an easy way to retrieve all ProjectWise Folder objects for a provided Work Area / Rich Project.

We will be using the following cmdlets to accomplish this task. All of the ProjectWise related cmdlets are available using the PWPS_DAB module. At the time of this post, I am using version 1.23.11.0. Take a look at the help for each of the cmdlets to become familiar with their functionality, available parameters, etc.

• Select-PWSQL
• Get-PWFoldersByGUIDs

For the dsqlGetSubFolders function, you have three argument values you can provide.

1. The first argument value can be either 0 or 1.
   • 0 – Do Not include the parent folder when a folder id is provided in the second argument.
   • 1 – Include the parent folder when a folder id is provided in the second argument.
2. The second argument allows you to provide a folder id for the parent folder to return all subfolders for.
3. This value indicates the level. This will be set to 0 (zero).

I will specify 1 for the first argument value and 177 for the second argument value. This is the folder id of the BSI900 – Adelaide Tower Work Area provided with the ProjectWise examples datasource.

In the following

# Folder ID of the Work Area / Rich Project / Folder to obtain all ProjectWise Folder objects for.
$ProjectID = 177
[int] $IncludeParentFolder = 1

$SQL = "SELECT o_projguid AS GUID
    FROM dms_proj 
    WHERE o_projectno IN (
        SELECT o_projectno
        FROM dsqlGetSubFolders ($IncludeParentFolder, $ProjectID, 0)
)"

# Returns the folder GUIDs for each folder within the provided Work Area folder id.
$results = Select-PWSQL -SQLSelectStatement $SQL 
# Pass the GUIDs to get the ProjectWise Folder objects.
$pwFolders = Get-PWFoldersByGUIDs -FolderGUIDs $results.GUID

Experiment with it and have fun.

Hopefully, you find this useful. Please let me know if you have any questions or comments.  If you like this post, please click the Like button at the bottom of the page.