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.
Have you tried to get all documents within a Work Area that are not checked in? It can be a time-consuming process. In this post, I am going to show an alternative method to get all documents not checked in within a Work Area which is much faster.
All of the ProjectWise related cmdlets are available using the PWPS_DAB module. At the time of this post, I am using version 1.30.7.0. Take a look at the help for each of the cmdlets to become familiar with their functionality, available parameters, etc.
- Show-PWFolderBrowserDialog
- Get-PWDocumentsBySearch
- Get-PWFolders
- Select-PWSQL
- Get-PWDocumentsByGUIDs
- Measure-Command
Get Work Area Folder
First thing we need to do is get the folder object for the Work Area containing the documents we are searching for.
# Select Work Area to get document objects from.
$pwFolder = Show-PWFolderBrowserDialog
Get Documents By Search
The standard way of obtaining the document objects is to use the Get-PWDocumentsBySearch. In order for us to determine the document status, we would need to include the -Slow switch parameter which is slow due to the amount of data being retrieved. We only need to check the document status, therefore all of the other information is superfluous.
# Measure the time it takes to return the document objects.
Measure-Command {
<# Get document objects with the -Slow switch parameter.
Filter on the Status of 'I' which indicates the document is checked in. #>
$pwDocsNotCheckedIn = Get-PWDocumentsBySearch -FolderID $pwFolder.ProjectID -Slow |
Where-Object Status -NE 'I'
} # end Measure-Command
The following shows that is took 3.929 seconds to return 11 documents. Yes, I realize this is fast. However, this is being run against a nice clean datasource.
Get Documents using the Alternative Method
Here we will ultimately end up with the same 11 document objects. However, the method used to retrieve them is a bit different. First, we will get a list of folder IDs for all folders contained within the Work Area.
# Measure the time it takes to return the document objects.
Measure-Command {
# Select ALL folder objects within the Work Area.
$pwFolders = Get-PWFolders -FolderID $pwFolder.ProjectID
# Create a string containing all of the Folder IDs separated by a comma.
$FolderIDs = $pwFolders.ProjectID -join ','
The following shows the string generated containing the folder ids.
Next we will use the Select-PWSQL cmdlet to select the document GUID for each of the documents not checked in within any of Work Area folders specified by the folder IDs.
$SQLResults = Select-PWSQL -SQLSelectStatement "SELECT o_docguid AS DocGuid FROM dms_doc WHERE o_projectno IN ($FolderIDs) AND o_dmsstatus NOT LIKE 'I'"
The following shows the list of document GUIDs returned.
Finally, we use the Get-PWDocumentsByGUIDs cmdlet to retrieve the corresponding document objects.
# Retrieve document objects by document GUIDs.
$pwDocsNotCheckedIn = Get-PWDocumentsByGUIDs -DocumentGUIDs $SQLResults.DocGuid
} # end Measure-Command
The following shows that is took 0.440 seconds to return the same 11 documents.
Summary
Again, the purpose of this post is to demonstrate an alternative method to obtain a list of documents which are not checked in within a Work Area. The times depicted here are minor, however, this approach can save you a lot of time with larger Work Areas contained in a large datasource.
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 