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 another method of obtaining the full path of a ProjectWise folder(s). The idea for this stemmed from a post on the Bentley Communities site where a user took advantage of the dsql_GetSubFolders SQL function provided with the ProjectWise datasource. The problem was there wasn’t an easy way to obtain the fullpath to the returned folders. We will be creating three functions 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.24.0.0. Take a look at the help for each of the cmdlets to become familiar with their functionality, available parameters, etc.- Select-PWSQL
Import-Module -Name D:\PowerShell\Get-PWFolderPaths.psm1 -Force -VerboseThe following will call the function and return all of the subfolders with the path values based on the ProjectID provided. In this example we are working with the ‘BSI900 – Adelaide Tower’ Work Area provided with the example ProjectWise datasource.
$ProjectData = Get-BMFPWFolderPaths -ProjectID 177 $ProjectData | Format-TableThe following shows the results of the dsql_GetSubfolders SQL function.
The following shows the results within the $ProjectData variable after the path values have been added.
FUNCTION Get-BMFPWFolderPaths
This function will run the dsql_GetSubfolders SQL function and return the folder and subfolder data based on the provided ProjectID. The returned datatable will be modified to add a Path column and populate that column with the full path of each folder using the provided functions. The following is the function wrapper which will contain the remainder of the code. This is the primary function for gathering the ProjectWise folder data including the full paths.FUNCTION Get-BMFPWFolderPaths { [CmdletBinding()] param (...) # end param... BEGIN {...} # end BEGIN... PROCESS {...} # end PROCESS... END{...} # end END... } # end FUNCTION Get-BMFPWFolderPaths... Export-ModuleMember -Function Get-BMFPWFolderPaths
Parameters
First thing we need to do is create our parameters. There is only one parameter for this function. You will see that I have incorporated “ValidateScript” to ensure the provide ProjectID is valid. If it is not, an error will be thrown.[CmdletBinding()] [OutputType([Data.DataTable])] param ( # Specifies the ProjectWise folder id to be used. [ValidateNotNullOrEmpty()] [ValidateScript({ -not ([string]::IsNullOrEmpty(Get-PWFolders -FolderID $_ -JustOne).FullPath) })] [Parameter( HelpMessage = "ProjectWise Folder ID.", Mandatory = $true, Position = 0)] [string] $ProjectID ) # 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. Here we are simply returning a Verbose message stating the time we entered the function and the name of the function. This will be consistent for the three functions. I will not repeat this section.BEGIN {
$CmdletName = $MyInvocation.MyCommand.Name
$StartTime = Get-Date
Write-Verbose -Message "[BEGIN] $StartTime - Entering '$CmdletName' Function..."
} # end BEGIN...
PROCESS
Now, we will proceed to the PROCESS code block. Here we run the dsql_GetSubfolders SQL function to obtain the folder and subfolder data for the provided ProjectID. We then loop through the data based on the folder level and update the path value. We populate the $ProjectIDs variable with the Project id from each of the folders within the current level. These will then be used as the parent id(s) in the next iteration of the loop.PROCESS {
try {
# Retrieve initial data from the ProjectWise datasource.
$SQL = "SELECT o_projectno AS ProjectID, o_projectname AS ProjectName, o_parentno AS ParentID, level FROM dbo.dsqlGetSubFolders (1, $ProjectID, 0) ORDER BY level "
$results = Select-PWSQL $SQL -ErrorAction Stop
# Add Path column to the data table to store full path values.
$results.Columns.Add("Path") | Out-Null
# Determine folder level for provided folder id.
$LevelCount = $results | Select-Object -Unique level
# Loop through each folder level and update the Path values.
$ProjectIDs = 0
for ($lvl = 0; $lvl -lt $LevelCount.Count; $lvl++) {
$temp = Get-BMFResults -dt ([ref]$results) -ID $ProjectIDs
Update-BMFPWFolderPath -dt ([ref]$results) -dtLEVEL ([ref]$temp)
$ProjectIDs = ($results.Rows | Where-Object ProjectID -IN ($temp.ProjectID)).ProjectID
}
Write-Output $results
} catch {
Write-Warning -Message "Error occurred retrieving folder data."
} # end try/catch...
} # end PROCESS...
END
Lastly, we will proceed to the END block of code. Here we will return another Verbose message letting you know how long it took to complete the process within the function. As with the BEGIN block, this will be consistent with all three functions. I will not repeat this section.END {
$EndTime = Get-Date
Write-Verbose -Message "[END] It took $($EndTime - $StartTime) to complete. Exiting '$CmdletName'..."
} # end END...
Function Get-BMFResults
This function returns a datatable containing filtered data from the original datatable.Parameters
First thing we need to do is create our parameters. There are two parameters for this function. We are referencing the original datatable obtained using the Select-PWSQL cmdlet in the previous function in the $dt parameter. The $IDs parameter contains an array of folder ids to be used to filter the data.[CmdletBinding()] param ( [ValidateNotNullOrEmpty()] [parameter( HelpMessage = "Reference to the returned data from SQL Query.", Mandatory = $true )] [Data.DataTable][ref]$dt, [ValidateNotNullOrEmpty()] [parameter( HelpMessage = "Array of Project ID numbers to return data for.", Mandatory = $true )] [int[]]$IDs ) # end param...
BEGIN…
PROCESS
Now, we will proceed to the PROCESS code block. Here we will be filtering the referenced data ($dt) based on the folder ids provided within the $IDs parameter. We will return a datatable where the provided folder IDs are equal to the parent folder ID.PROCESS {
Write-Verbose -Message "[PROCESS] ProjectIDs: $($IDs -join ', ')"
$dtReturn = $dt.Rows | Where-Object ParentID -IN $IDs | Sort-Object ParentID, ProjectID
Write-Output $results
} # end PROCESS...
END…
Function Update-BMFPWFolderPath
This function will update the folder path for the provided folders.Parameters
First thing we need to do is create our parameters. There are two parameters for this function. Again, we are referencing the original datatable obtained using the Select-PWSQL cmdlet in the previous function in the $dt parameter. The $dtLEVEL parameter contains an array of datarows used to update the folder path values.[CmdletBinding()] param( [ValidateNotNullOrEmpty()] [parameter( HelpMessage = "Reference to the returned data from SQL Query.", Mandatory = $true )] [Data.DataTable][ref]$dt, [ValidateNotNullOrEmpty()] [parameter( HelpMessage = "Array of datarows to update path value.", Mandatory = $true )] [Data.DataRow[]]$dtLEVEL ) # end param...
BEGIN…
PROCESS Now, we will proceed to the PROCESS code block. Here we will loop through each of the entries within the $dtLEVEL parameter. The Path value within the primary datatable’s (referenced $dt) corresponding record will be updated with the concatenated value of the parent folder path and the current folder name.PROCESS {
foreach ($object in $dtLEVEL) {
$index = $dt.Rows.IndexOf($object)
$parent = $dt.Select("ProjectID = $($object.ParentID)")
$projectName = ($dt.Rows[$index]).ProjectName
$fullPath = "$($parent.Path)\$($projectName)"
$fullPath = $fullPath.Trim('\\')
Write-Verbose -Message "[PROCESS] Updating path for '$($projectName)' to '$fullPath'."
$dt.Rows[$index].Path = $fullPath
}
} # end PROCESS...
END…
The following is a link to the full PowerShell script. Get-PWFolderPaths 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.
powerwisescripting.blog 