HowTo: Generate Folder Report Using Two Criterion

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 will demonstrate how to generate a report for folders that meet certain criteria. We will use two bits of data to get only the folders that match our criteria, the folder must begin with a specified string value, and must have been created after a specified date.  We will populate a datatable which will be exported out to an Excel workbook. The new report will then be imported into a ProjectWise folder. If a document with the same name exists, we will create a new version.

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

• Get-PWCurrentDatabase
• Get-PWFoldersImmediateChildren
• Get-PWFolders
• New-XLSXWorkbook
• Get-PWDocumentsBySearch
• New-PWDocument
• New-PWDocumentVersion

---

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.1.0'}
#Requires -RunAsAdministrator

[CmdletBinding()]
param(
    # ProjectWise folder to report on.
    [string] $PWFolderPath = 'PowerShell',
    # Local folder to create report in.
    [string] $Path = 'D:\TEMP',
    # Excel file name to be created.
    [string] $OutputFileName = "ProjectName_Check_$(Get-Date -Format yyyy_MM_dd).xlsx",
    # Description to be used when importing the report into ProjectWise.
    [string] $PWReportDesc = 'Projects Report',
    # ProjectWise Folder to import report into.
    [string] $PWReportFolder = '!Administrative\Reports',

    # Search string to be used to filter on Folders to be reported on.
    [string] $Folder_SearchString = 'SUB*',
    # Search date to be used to filter on Folders to be reported on.
    [string] $Date_SearchString = '4/20/2019',

    # When included or set to true, the newly created Excel file will immediately open.
    [switch] $OpenExcelFile = $false,
) # end param(...

---

Begin

The next thing we will do is ensure the specified folder exists, and that we are logged into ProjectWise.  I am going to add these steps into the BEGIN section of the script.

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 that we verified our local folder exists, and we are logged into a ProjectWise datasource, we can proceed to the PROCESS code block.

You can see that if the Continue variable equals false, a warning message will be displayed and we will proceed to the END code block to log out of ProjectWise and exit the script.

PROCESS {
    if( -not ($Continue)) {
        Write-Warning -Message "[PROCESS] Exiting script."
    } else { ... }
} # end PROCESS

Before we do anything else, we are going to create a datatable and add five columns to it. Just to be clear, all of the following is occurring within the else section indicated by the    { … } above. I am using the Out-Null to suppress any output from the column add method.

<# This builds a data table to store our folder output, which will be      exported to an Excel workbook. #>
$dt = New-Object System.Data.Datatable ("ProjectName")

# Add columns to the datatable.
$dt.Columns.Add('ProjectName') | Out-Null
$dt.Columns.Add('ProjectFullPath') | Out-Null
$dt.Columns.Add('ProjectURN') | Out-Null
$dt.Columns.Add('CreatedBy') | Out-Null
$dt.Columns.Add('CreatedDate') | Out-Null

Now we have our datatable built with the columns required.

Next, we are going to get ONLY the immediate child folders of the folder specified in the PWFolderPath parameter. I am wrapping the Get-PWFoldersImmediateChildren cmdlet in a try / catch block to capture any errors that may occur.

# Get all immediate child folders for the specified ProjectWise folder.
try { 
    $pwParentFolders = Get-PWFoldersImmediateChildren -FolderPath $PWFolderPath -ErrorAction Stop
} catch {
    Write-Warning -Message "[PROCESS] Error occurred while attempting to get immediate child folders for $PWFolderPath. $($Error[0].Exception)"
}

If any ProjectWise Folder objects are returned, we will loop through them and get their immediate child folders which match the value contained in the Folder_SearchString parameter (“SUB*). Basically, we want to look at the folders two levels deep in the folder hierarchy.

I am going to add a Write-Progress section to the foreach loop to  display the progress. Notice the use of the Math assembly to round the PercentComplete value and the use of Splatting.

Another code convention I use, is the #region / #endregion, which will allow me to collapse a block of code. This way I do not have to do as much scrolling.

# The following is a counter for the progress bar. 
$Counter_ParentFolders = 1
$ItemCount_ParentFolders = $pwParentFolders.Count
# Loop through each of the folder objects in the pwParentFolders array.
foreach($pwParentFolder in $pwParentFolders) {
    #region PROGRESS SECTION

    $Progress_ParentFolders = @{ 
        Activity = "Getting folder information for '$($pwParentFolder.Name)'." 
        Status = "Processing $Counter_ParentFolders of $ItemCount_ParentFolders" 
        PercentComplete = $([math]::Round($(($Counter_ParentFolders / $ItemCount_ParentFolders) * 100 ), 2)) 
    } 
    Write-Progress @Progress_ParentFolders -Id 1

    # Increment the counter.
    $Counter_ParentFolders++

    #endregion PROGRESS SECTION

    try {                
        $pwFolders = Get-PWFoldersImmediateChildren -FolderID $pwParentFolder.ProjectID | 
        Where-Object {$_.Name -match $Folder_SearchString} 
    } catch {
        Write-Warning -Message "[PROCESS] Error occurred while attempting to get immediate child folders for $($pwParentFolder.Name). $($Error[0].Exception)"
    }
    
    ....

} # end forEach($pwParentFolder...

The shows the progress bars being displayed when the script is run.

The pwFolders variable contains all immediate child folders which begin with “SUB”.

The next step is to determine if any of the ProjectWise folders were created after the date specified in the Date_SearchString parameter. To do this, we will use another foreach loop.  If a folder was created after the date specified, we will add a new row to our datatable containing the corresponding folder properties.

I have added another Write-Progress code block. For this to work, each of the Write-Progress calls need to have a unique Id, and counter, progress splat, etc.. I simply used     -Id 1 and -Id 2.

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

    #region PROGRESS SECTION

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

    # Increment the counter.
    $Counter++

    #endregion PROGRESS SECTION

    <# Here we need to get addition information pertaining to the current folder object. We will be         comparing the create date with the specified date in the Date_SearchString parameter. To get this         addition data, we will use the -Slow switch parameter with the Get-PWFolders cmdlet. This will         also give us the addition data we want to output; FullPath, URN, and the name of the user who created the folder. #>
    $CurrentFolder = Get-PWFolders -FolderID $pwFolder.ProjectID -JustOne -Slow

    # If current folders create date is greater than the specified search date, add to datatable.
    if($CurrentFolder.CreateDateTime -ge $Date_SearchString) {
        # Create new datarow to be added to datatable.
        $dr = $dt.NewRow()
        # Populate datarow.
        $dr.ProjectName = $CurrentFolder.Name
        $dr.ProjectFullPath = $CurrentFolder.FullPath
        $dr.ProjectURN = $CurrentFolder.ProjectURN
        $dr.CreatedBy = $CurrentFolder.FolderCreatorName
        $dr.CreatedDate = $CurrentFolder.CreateDateTime
        # Add new datarow to the datatable.
        $dt.Rows.Add($dr)
    } # end if($CurrentFolder.CreateDateTime...
} # end forEach ($pwFolder...

So, why take this approach? Why not just use the -Slow for all folders previously returned?  Using the -Slow switch parameter takes a bit longer to return a folder object as it is getting the FullPath, URN, etc.  So, we filter the folder objects returned based on the folder name, which should process quickly.  Once we have a smaller set of folder we use the -Slow to retrieve the additional folder data which includes the create date which is our second comparison. By doing this, the entire process will take much less time to complete.

The following shows one of the folders that met the criteria, name begins with SUB and the create date was after 4/20/2019.

Next, we will export the datatable to an Excel workbook and import it into the specified ProjectWise folder. If a document with the same name already exists in the ProjectWise folder, we will create a new version of the document.

Keep in mind, we are still in the PROCESS code block.

# Export data to Excel spreadsheet and import into ProjectWise.
try { 
    New-XLSXWorkbook -InputTables $dt -OutputFileName "$Path\$OutputFileName" -Open:$OpenExcelFile -ErrorAction Stop
    Write-Verbose -Message "[PROCESS] Successfully exported the datatable to Excel '$OutputFileName'."

    # Import new report into ProjectWise. If file already exists, create new version.
    try { 
        $pwDocReport = Get-PWDocumentsBySearch -FolderPath $PWReportFolder -FileName $OutputFileName -JustThisFolder -ErrorAction Stop

        if([string]::IsNullOrEmpty($pwDocReport)) {
            # Import document into ProjectWise folder.
            New-PWDocument -FolderPath $PWReportFolder -FilePath "$Path\$OutputFileName" -Description $PWReportDesc -ErrorAction Stop
        } else {
            # Create new version of the report document.
            New-PWDocumentVersion -InputDocument $pwDocReport -Verbose -ErrorAction Stop
        }

    } catch {
        Write-Warning -Message "[PROCESS] Error occurred while attempting to import report into ProjectWise folder '$PWReportFolder'. $($Error[0].Exception)"
    } # end try / catch
} catch {
    Write-Warning -Message "[PROCESS] Error occurred while attempting to export datatable to Excel. $($Error[0].Exception)"
} # end try / catch

The following  shows that the report was imported into the specified ProjectWise folder.

---

END

Lastly, we will proceed to the END block of code to log out of our ProjectWise session.

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

The following shows the contents of the report generated.

To recap, we created a report which lists all folders that matched our specified name criteria and were created after a specified date. We then imported the report into a ProjectWise folder.

---

The following is a link to the full PowerShell script.

HowTo_GenerateFolderReportUsingCriteria

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.