How To: Get ProjectWise Documents by Owner

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 user ask if it was possible to search for ProjectWise documents based on the owner of the documents. Unfortunately, at the time of this post, we do not have the owner as one of the document properties being returned.

So, how to get around this limitation.

You can use the SQL cmdlets available within the PWPS_DAB module.

There are 3  Select SQL cmdlets:

• Select-PWSQL
• Select-PWSQLDataTable
• Select-PWSQLDataTableToSQLite

In the following example I use the Select-PWSQLDataTable cmdlet to directly query the ProjectWise database to obtain document GUIDs for the documents the specified user is the owner of. The GUIDs can then be used to obtain the document objects desired.

# Specify user to use as the owner.
$UserName = 'pwadmin'
# Obtain user ID,
$UserID = Get-PWUsersByMatch -UserName $UserName | 
Select-Object -ExpandProperty ID
<# SQL Select statement to obtain the document GUIDs using
    the UserID as the manager number. #>
$SQL = "SELECT o_docguid FROM dms_doc WHERE o_managerno = $UserID"
<# Pass the SQL statement to the Select-PWSQLDatatable cmdlet and 
    populate the Result parameter with the data returned. #>
$Results = Select-PWSQLDataTable -SQLSelectStatement $SQL -Verbose
<# Finally, obtain the document information desired based
    on each document GUID in the Results variable. #>
$Docs = Get-PWDocumentsByGUIDs -DocumentGUIDs $Results.Rows.o_docguid