HowTo: Add Document Descriptions to a Reference Report

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.

In this post, I am going to step through the process of adding addition columns to a reference file report generated using the Get-PWReferenceReportForDocuments. By default, this report does not include the document / file descriptions. I recently had a request to add this information to the report. This is one approach which does not require any changes made to the PWPS_DAB module. And the concept can be used to manipulate other datatables when generating reports.

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.12.2.0. Take a look at the help for each of the cmdlets to become familiar with their functionality, available parameters, etc.

• Get-PWDocumentsBySearch
• Get-PWReferenceReportForDocuments
• New-XLSXWorkbook
• Import-PWDocuments
• Get-Item

---

Parameters

First thing we need to do is create our parameter and value pairs. The comments will explain the purpose for each parameter. I am also going to include a few requires statements to ensure the correct version of PowerShell and the PWPS_DAB module are used. As well as require that the Run As Administrator option is used to run the PowerShell Console or PowerShell ISE. 

#Requires -Version 5.0
#Requires -Modules @{ModuleName="PWPS_DAB";ModuleVersion='1.12.2.0'}
#Requires -RunAsAdministrator

[CmdletBinding()]
param(
    # ProjectWise Rich Project / Work Area / Folder to generate a reference report for.
    [string] $pwFolderPath = 'BSI900 - Adelaide Tower',
    # ProjectWise folder to import document into.
    [string] $pwFolder_Import = 'PowerShell\Reports',
    # Local folder to create report in.
    [string] $Path = 'd:\TEMP\Export',
    # Report path and name.
    [string] $OutputFilePath = "$Path\$($pwFolderPath)_ReferenceReport_$(Get-Date -Format yyyyMMdd).xlsx",
    # Name for datatable. Will correspond to worksheet created in Excel.
    [string] $DataTableName = 'ReferenceReport'
)

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 {
$Continue = $true

$StartTime = Get-Date
Write-Verbose -Message "[BEGIN] Start time: $StartTime"

<# Test to see if the provided path exists. If not, set the variable Continue to false.
      This will be used in the PROCESS code block to exit the script. #>
if( -not (Test-Path -Path $Path -PathType Container -Verbose -ErrorAction Stop)) {
    Write-Warning -Message "[BEGIN] Folder '$Path' not found. Update path variable value and try again."
    $Continue = $false
}
<# Determine if we are currently logged into a ProjectWise datasource.
     If not, display the ProjectWise Login Dialog. #>
if(Get-PWCurrentDatasource) {
    Write-Verbose -Message "[BEGIN] Currently logged into ProjectWise datasource '$(Get-PWCurrentDatasource)'."
} else {
    if(New-PWLogin -UseGui) {
        Write-Verbose -Message "[BEGIN] Successfully logged into ProjectWise datasource '$(Get-PWCurrentDatasource)'."
    } else {
        Write-Error -Message '[BEGIN] Failed to log into ProjectWise datasource.'
        $Continue = $false 
    }
} # end if(Get-PWCurrentDatasource...

} # end BEGIN

PROCESS

Now, we will proceed to the PROCESS code block. Here we will determine if the sub-folders should be updated. If yes, we will get the ProjectWise folder object(s) for each sub-folder.

PROCESS { 

    # Get array of document objects to get reference information for.
    $pwDocuments = Get-PWDocumentsBySearch -FolderPath $pwFolderPath

    # Return reference objects. Excluding the output file path to only return a datatable.
    $dtReferenceData = Get-PWReferenceReportForDocuments $pwDocuments
    
    # Give the table a name. When exported to Excel, this will be used to name the worksheet.
    $dtReferenceData.TableName = $DataTableName
   
    #region ADD Descriptions to report

    <# 
            Generate reference data into a datatable.
            Name the datatable.
            Add two new columns
            - ParentFileDescription
                - Use ParentFolder and Parent file name to get the parent file description
            - ReferenceFileDescription
                - Use ParentFolder and Reference file name to get the reference file description
            Populate the datatable with descriptions
            - Parent File Description
            - Reference File Description
            Generate new report (export to Excel)
            Import new report into ProjectWise
            Delete local copy of the report
    #>

    # Insert a new column for the Parent File descriptions. Columns will be created after the corresponding column.
    $dtReferenceData.Columns.Add("ParentFileDescription").SetOrdinal($($dtReferenceData.Columns | 
            Where-Object ColumnName -EQ 'ParentFileName' | 
        Select-Object Ordinal).Ordinal + 1) | Out-Null
    # Insert a new column for the Reference File descriptions.
    $dtReferenceData.Columns.Add("ReferenceFileDescription").SetOrdinal($($dtReferenceData.Columns | 
            Where-Object ColumnName -EQ 'ReferenceFileName' | 
        Select-Object Ordinal).Ordinal + 1) | Out-Null

    # Loop through each row in the $dtReferenceData datatable to the populate the descriptions.  

    # The following is a counter for the progress bar. 
    $Counter = 1
    $ItemCount = $dtReferenceData.Rows.Count
    # Loop through each item in the collection. 
    foreach ($row in $dtReferenceData.Rows) {

        #region PROGRESS SECTION

        $Progress = @{ 
            Activity = "Getting document information for '$($row.ReferenceFileNameBare)'." 
            Status = "Processing $Counter of $ItemCount" 
            PercentComplete = $([math]::Round($(($Counter / $ItemCount) * 100 ), 2)) 
        } 
        Write-Progress @Progress -Id 1

        # Increment the counter.
        $Counter++

        #endregion PROGRESS SECTION
    
        # Get the parent file description using the ParentFolder and ParentFileName values.
        $Splat_ParentFile = @{
            FolderPath = $row.ParentFolder
            FileName = $row.ParentFileNameBare
        }
        $ParentFileDescription = Get-PWDocumentsBySearch @Splat_ParentFile -JustThisFolder | 
        Select-Object Description
        # Get the reference file description using the ReferenceFolder and ReferenctFileName values.
        $Splat_ReferenceFile = @{
            FolderPath = $row.ReferenceFolder
            FileName = $row.ReferenceFileNameBare
        }
        $ReferenceFileDescription = Get-PWDocumentsBySearch @Splat_ReferenceFile -JustThisFolder | 
        Select-Object Description
    
        $row["ParentFileDescription"] = $ParentFileDescription.Description
        $row["ReferenceFileDescription"] = $ReferenceFileDescription.Description        
    } # end foreach ($row...

    #endregion ADD Descriptions to report


    # Create new report
    New-XLSXWorkbook -InputTables $dtReferenceData -OutputFileName $OutputFilePath -Verbose

    # Import generated report into the specified ProjectWise folder.
    Import-PWDocuments -InputFolder $OutputFilePath -ProjectWiseFolder $pwFolder_Import -CreateVersions -ExcludeSourceDirectoryFromTargetPath 

    # Delete generated report from local drive.
    Get-Item -Path $OutputFilePath | ForEach-Object {$_.Delete()}
      
} # end PROCESS

END

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

END {
    $EndTime = Get-Date
    Write-Verbose -Message "[END] It took $([Math]::Round($EndTime.Subtract($StartTime).TotalMinutes, 2)) minutes to complete process."

    Write-Verbose -Message '[END] Logging out of ProjectWise.'
    Undo-PWLogin
} # end END

---