HowTo: Get Folder Hierarchy

In this post, we will be creating a PowerShell function script file (.psm1) which will return an array of ProjectWise folder data including the fullpath. We will be using the following cmdlets to accomplish this task. Most of this will be done using SQL so you will need to be using a ProjectWise administrator account. All of the ProjectWise related cmdlets are available using the PWPS_DAB module. At the time of this post, I am using version 23.3.2.0. Take a look at the help for each of the cmdlets to become familiar with their functionality, available parameters, etc.

• Get-PWFolders
• Select-PWSQL
• [pwwrapper]::aaApi_ExecuteSqlStatement()

FUNCTION Definition

The following is the function wrapper which will contain the remainder of the code.

FUNCTION Get-PWFolderHierarchyWithFullPath {

    [CmdletBinding()]
    param (...) # end param...

    BEGIN {...} # end BEGIN...

    PROCESS {...} # end PROCESS...

    END{...} # end END...
} # end FUNCTION Get-PWFolderHierarchyWithFullPath...
Export-ModuleMember -Function Get-PWFolderHierarchyWithFullPath

Parameters

First thing we need to do is create our parameter and value pairs. The help messages will explain the purpose for each parameter.  You will see that I have incorporated “ValidateScript” for a few of the parameters. This way I do not have do any additional checks to determine if a specified ProjectWise folder exists. I have created this function with the intentions to enhance the filtering capabilities.

[CmdletBinding()]
param ( 
    # Parent folder ID to build hierarchy for.
    [ValidateNotNullOrEmpty()] 
    [ValidateScript({ Get-PWFolders -FolderID $_ -JustOne })]
    [Parameter(
        HelpMessage = "Parent folder ID to build hierarchy for.",
        Mandatory = $true,
        Position = 0)]
    [string] $FolderID,

    # Specifies the filter type. At the moment FileName is the only filter type.
    [ValidateNotNullOrEmpty()]
    [ValidateSet('FolderName')]
    [Parameter(
        HelpMessage = "Specifies the filter type. At the moment FileName is the only filter type.")]
    [string] $FilterType,

    # Filter value. Accepts one or more folder names.
    [ValidateNotNullOrEmpty()]
    [Parameter(
        HelpMessage = "Filter value. Accepts one or more folder names.")]
    [string[]] $Filter

) # end param...

---

BEGIN

I want to take a second and point out a couple of conventions that I use in my scripts. First, you may notice that I put a comment at the end of many of the blocks of code. Doing this makes it easier for me to determine where a block of code starts and ends. Comes in handy when troubleshooting a script.  I also add text ([BEGIN], [PROCESS], [END]) in the output messages (Write-Verbose, Write-Warning, Write-Error) corresponding to the section of the script I am in. Again, this makes it obvious where something occurs within the script.

BEGIN {  
    $CmdletName = $MyInvocation.MyCommand.Name
    $StartTime = Get-Date
    Write-Verbose -Message "[BEGIN] $(Get-Date) - Entering '$CmdletName' Function..."

} # end BEGIN...

---

PROCESS

Now, we will proceed to the PROCESS code block. Here we will get the ProjectWise folder object for the parent folder. Then we will create a database view containing the folder hierarchy information. This will build the fullpath for each folder.  The issue is that it requires the use of the nvarchar(MAX) datatype definition. This causes an issue with the Select-PWSQL cmdlet within PowerShell. This is the reason for creating the view. We can query the view and cast the fullpath value to something the Select-PWSQL can use.

PROCESS {
    
    try {
        # Select folder using provided FolderID.
        $pwFolder = Get-PWFolders -FolderID $FolderID -JustOne -ErrorAction Stop
        $pwFolder.GetFullPath() | Out-Null
        # Get folder path for provided folder. This is used to populate fullpath values.
        $FolderPath_Parent = $pwFolder.FullPath

        $ViewName = "v_FolderHierarchyWithFullPath"
        #region CREATE VIEW

        $SQL = "CREATE VIEW $ViewName AS
            WITH proj_hierarchy (o_projectno, o_projectname, o_projectdesc, o_projguid, o_parentno, o_envno, fullpath, lvl) AS ( 
                SELECT o_projectno, o_projectname, o_projectdesc, o_projguid, o_parentno, o_envno, 
                   CAST(o_projectname AS nvarchar(MAX)) AS fullpath, 1 AS lvl 
                FROM dms_proj 
                WHERE o_parentno = $FolderID 
                UNION ALL 
                SELECT P.o_projectno, P.o_projectname, P.o_projectdesc, P.o_projguid, P.o_parentno, P.o_envno,
                    CONCAT(H.fullpath, '/', p.o_projectname), H.lvl + 1 AS lvl 
                FROM proj_hierarchy H 
                JOIN dms_proj P ON H.o_projectno = P.o_parentno
            )
            SELECT * FROM proj_hierarchy;"
        if([pwwrapper]::aaApi_ExecuteSqlStatement($SQL)){
            Write-Verbose -Message "Successfully created view '$ViewName'."
        } else {
            throw "Failed to create the view '$ViewName'."
        }

        #endregion CREATE VIEW

In the next section we will be determining if there was a filter provided and then query the view we just created. You’ll see that we cast the nvarchar to some Select-PWSQL understands.

#region GET DATA

$WHERE = [string]::Empty
if(( -not ([string]::IsNullOrEmpty($FilterType))) -and 
    ( -not ([string]::IsNullOrEmpty($Filter)))) {
    $Column = [string]::Empty
    $Compare = "LIKE"
    switch ($FilterType.ToLower()){
        'foldername' {
            $Column = "o_projectname"
            break
        }
        Default {
            break
        }
    }
    if($Filter.Count -eq 1){
        $Filter = "'$Filter'"
    } elseif($Filter.Count -gt 1){
        $Compare = "IN"
        $Filter = $Filter -join "','"
        $Filter = "('$Filter')"
    }

    $WHERE = "WHERE $Column $Compare $Filter"
}

$SQL = "SELECT o_projectno AS ProjectID, o_projectname AS ProjectName, 
        o_projectdesc AS ProjectDesc, o_projguid AS ProjectGUID,
        o_parentno AS ParentID, o_envno AS EnvironmentID,
        CONCAT('$($pwFolder.FullPath)/', CAST(fullpath AS NVARCHAR(1024))) AS FullPath,
        lvl AS Level
        FROM $ViewName $WHERE ORDER BY fullpath"
Write-Verbose -Message "Getting data. This could take a minute."
if($results = Select-PWSQL -SQLSelectStatement $SQL){
    if($results.GetType().Name -eq 'DataTable'){
        $Count = $results.Rows.Count
    } elseif ($results.GetType().Name -eq 'DataRow'){
        $Count = 1
    }
    Write-Verbose -Message "$Count rows of data returned."
} else {
    throw "No data returned."
}

#endregion GET DATA

Once we’ve retrieved the folder hierarchy data, we need to remove the SQL view previously created.

#region DROP VIEW 

if( -not ([pwwrapper]::aaApi_ExecuteSqlStatement("DROP VIEW $ViewName"))){
    throw "Failed to remove existing view. Cancelling."
}

#endregion DROP VIEW

Finally, we return the data collected.

# Output resulting query.
Write-Output $results

If any errors occurred during the process, an error would have been thrown and handled in the catch block of code.

    } catch {
       Write-Warning -Message $_
    } # end try/catch...
} # end PROCESS...

END

Lastly, we will proceed to the END block of code.

END {

    $EndTime = Get-Date
    Write-Verbose -Message "[END] It took $($EndTime - $StartTime) to complete the process."
    Write-Verbose -Message "[END] $EndTime - Exiting '$CmdletName' Function..."

} # end END...

Using the Function

To use the function you simply need to provide a folder id and the filter parameter value desired. In this example we are retrieving all of the folder hierarchy data for the specified folder. There are 117 rows of data returned, meaning there are 117 folders in the hierarchy.

$Results = Get-PWFolderHierarchyWithFullPath -FolderID 342 -Verbose

The following shows the output using the Out-GridView: In this example, we are retrieving only the folders within the folder hierarchy which match the name(s) listed in the filter. There are 4 rows of data returned, meaning there are 4 folders in the folder hierarchy matching the folder name(s).

$Splat_GetHierarchy = @{
    FolderID = 342
    FilterType = 'FolderName'
    Filter = @('03-Published', '05-Incoming')
}
$Results = Get-PWFolderHierarchyWithFullPath @Splat_GetHierarchy -Verbose

The following shows the filtered output using the Out-GridView:

---

The following is a link to the full PowerShell script. FUNCTION_Get_PWFolderHierarchy_Update 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.