HowTo: Get Document Attributes By View

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.

I recently had a request for assistance on how to generate a report containing document attributes for ONLY those attributes displayed within a View associated with the containing folder.  So, in this post, we will determine which view is associated with a ProjectWise folder and get a list of the columns. We will then create a new datatable and populate it with the document attributes corresponding to the list of columns and finally export the datatable to an Excel spreadsheet. 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.16.3.0. Take a look at the help for each of the cmdlets to become familiar with their functionality, available parameters, etc.

• Get-PWFolders
• Get-PWDocumentsBySearch
• Export-PWViews
• New-XLSXWorkbook

Parameters

First thing we need to do is create our parameter and value pairs. The help messages 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. You will see that I have incorporated “ValidateScript” for one of the parameters. This way I do not have do any additional checks to determine if a specified ProjectWise folder exists.

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

[CmdletBinding()]
param ( 
    # Specifies the ProjectWise folder containing documents to report on.
    [ValidateNotNullOrEmpty()] 
    [ValidateScript({ Get-PWFolders -FolderPath $_ -JustOne })]
    [Parameter(
        HelpMessage = "Specifies the ProjectWise folder containing documents to report on search.",
        Mandatory = $true,
        Position = 0)]
    [string] $FolderPath,

    # Specifies the path and file name for the Excel spreadsheet to be created.
    [ValidateNotNullOrEmpty()]
    [Parameter(
        HelpMessage = "Specifies the path and file name for the Excel spreadsheet to be created.",
        Mandatory = $true,
        Position = 1)]
    [string] $SearchName = 'TEMP_SavedSearch',

    # Specifies the name of the View to be used if the folder does not have one associated with it.
    [ValidateNotNullOrEmpty()]
    [Parameter(
        HelpMessage = "Specifies the name of the View to be used if the folder does not have one associated with it.",
        Mandatory = $false,
        Position = 2)]
    [string] $View
) # end param...

---

Begin

Deliberately left empty.

BEGIN {} # end BEGIN...

---

PROCESS

Now, we will proceed to the PROCESS code block.

Get View

The first thing we will do in the PROCESS section is to determine whether or not the provided ProjectWise folder has a View associated with it. If it does not, we will use the specified View if one is provided.

PROCESS {
    # Get view associated with a folder.
    $pwFolderView = Get-PWFolders -FolderPath $FolderPath -Slow -JustOne | 
    Select-Object -Property View
    
    <# If the folder does not have a View associated with it, 
        use the provided View. #>
    if( [string]::IsNullOrEmpty($pwFolderView.View)) { 
        Write-Warning -Message 'Folder does not have View associated with it.'
        
        # If a View was not provided, exit script.
        if([string]::IsNullOrEmpty($View)) {
            Write-Warning -Message 'A view was not provided. Exiting script.'
            break
        }

        <# Verify the provided View exists within the currend ProjectWise
            datasource. First, use Get-PWViews to select all views defined
            within the current ProjectWise Datasource. Then use the 
            GetEnumerator to filter on and select the provided view. 
            If the provided View is not found, the script will exit. #> 
        $pwFolderView = (Get-PWViews).GetEnumerator() | 
        Where-Object { $_.Value -eq $View } | 
        Select-Object -Property Value
    
        if([string]::IsNullOrEmpty($pwFolderView.Value)){
            Write-Warning -Message "Failed to get view '$View'. Exiting script."
            break
        } else {
            Write-Verbose -Message "Using provided View '$View'."
        }
    }

The following shows the View associated with the ProjectWise Folder.

Get View Columns

Now that we have the view, we can get the attributes (columns) associated with it.

 # Get Columns associated with the View assigned to the folder.
$pwViewColumns = Export-PWViews -ViewNames $pwFolderView.View | 
Select-Object -Property ColumnName

The following shows the attributes (columns) associated with the View.

Select Documents

Next, we will select the documents contained in the provided folder. We are using the -Slow switch parameter to get the document attributes as well.

# Select documents contained with the specified folder.
$pwDocs = Get-PWDocumentsBySearch -FolderPath $FolderPath -Slow -JustThisFolder

Create Datatable

We need to create a datatable to populate with the document attribute data. We will add columns to the datatable which correspond to the attributes (columns) included in the View. We will also add the FullPath and DocumentURN values.

# Create new datatable to populate with document attribute values.
$dt = New-Object -TypeName Data.Datatable -ArgumentList ('DocProperties')
# Add columns to datatable for FullPath and Document URN.
$dt.Columns.Add('FullPath') | Out-Null
$dt.Columns.Add('DocumentURN') | Out-Null
# Add all of the View columns to the datatable.
$dt.Columns.AddRange($pwViewColumns.ColumnName) | Out-Null

Next, we will loop through all of the documents,  grab the document attribute values and populate the datatable.

#Loop through each document and populate the datatable with the appropriate meta-data.
foreach ($pwDoc in $pwDocs) {
    Write-Verbose -Message "Current document '$($pwDoc.Name)'."
    # Create new datarow.
    $dr = $dt.NewRow()

    $dr.FullPath = $pwDoc.FullPath
    $dr.DocumentURN = $pwDoc.DocumentURN

    # Get only the attribute values for the columns associated with the view.
    foreach ($pwViewColumn in $pwViewColumns.ColumnName) {
        <# Resolve column names for General Attributes. 
            They will start with 'PW_Document'. #>
        if($pwViewColumn.StartsWith('PW_Document')){
            # Get Attribute Value for attribute which matches the column name.
            $genAttr = $pwViewColumn.Substring($pwViewColumn.LastIndexOf('_') + 1)

            if($genAttr -eq 'State') {
                $genAttr = 'WorkflowState'
            }

            $dr.$pwViewColumn = $pwDoc.$genAttr
        } else {
            # Use GetEnumerator to get the key / value pair for the column specified.
            $value = $pwDoc.Attributes[0].GetEnumerator() | 
            Where-Object { $_.Key -eq $pwViewColumn } 
            # Populate the datarow with only the value.
            $dr.$pwViewColumn = $value.Value
        } # end if($pwViewColumn.StartsWith... / else...
    } # end foreach ($pwViewColumn...

    # Add datarow to the datatable.
    $dt.Rows.Add($dr)
} # end foreach ($pwDoc...

Export Metadata to Excel

Finally, we finish up the Process code block by export the contents of the datatable to an Excel spreadsheet.

    # Export datatable to an Excel spreadsheet.
    New-XLSXWorkbook -InputTables $dt -OutputFileName $OutputFileName

} # end PROCESS...

---

END

Deliberately left empty.

END {} # end END...

---

Calling the Script

I used the following to call the script passing the parameter values.

$Splat_Report = @{
    FolderPath = 'BSI900 - Adelaide Tower\01-WIP\CAD_Data'
    OutputFileName = "d:\temp\export\Document_Metadata_$(Get-Date -Format yyyyMMddhhmmss).xlsx"
    View = 'WIP'
}
& 'D:\PowerShell\!Blog Post Scripts\HowTo_GetDocumentAttributesByView.ps1' @Splat_Report -Verbose

The following shows the output within PowerShell. The following shows the contents of the spreadsheet.

---

Summary

To recap, we used a View to determine which document attribute values need to be exported. The View was associated with a ProjectWise folder, however, it does not need to be.  We then populated a datatable with the corresponding attribute values for each document contained within the provided folder. Finally, we generated a report by exporting the contents of the datatable to an Excel spreadsheet.

---

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