Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
ProjectWise
  • Product Communities
ProjectWise
ProjectWise PowerShell Extensions Forum Get ProjectWiseWebLink with Get-PWDocumentsBySearchWithReturnColumns
    • Sign In

    • State Not Answered
    • Replies 12 replies
    • Subscribers 65 subscribers
    • Views 273 views
    • Users 0 members are here

    Get ProjectWiseWebLink with Get-PWDocumentsBySearchWithReturnColumns

    Neil White
    Offline Neil White 29 days ago

    I have up until recently been producing an Excel report using "Get-PWDocumentsBySearch" that I then export a datatable from. This excel report has a number of custom attributes and most importantly I get the "ProjectWiseWebLink" and "DocumentURN". This report is now taking nearly an hour to run, and it's only going to get bigger, which causes me some issues.

    I have now tried updating my scripts to use Get-PWDocumentsBySearchWithReturnColumns which returns the data tables in only a few minutes, but I can't return the below fields of data, 

    ['DocumentURN'] = $doc.DocumentURN
    ['URL'] = $doc.ProjectWiseWebLink

    I get that these are all document properties and not "columns" of custom attributes, but is there a way I can get these into my report.

    Happy to modify my environments to add new columns if needed but cant work out the select statements to get these.

    Any help appreciated!

    • Sign in to reply
    • Cancel
    Parents
    • MWBSI
      0 MWBSI Mon, Sep 11 2023 5:04 PM

      The Expand-PWDocumentInformation cmdlet should do the trick:

      Try something like

      $docs = Get-PWDocumentsBySearchWithReturnColumns(...)
      $expandedDocs = Expand-PWDocumentInformation $docs

       The document objects in $expandedDocs should have all the properties that you are interested in populated.

      Mark Weisman | Bentley Systems

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Brian Flaherty
      0 Offline Brian Flaherty Mon, Sep 11 2023 5:45 PM in reply to MWBSI

      I think it will really depend on what you are trying to achieve. If all you are looking for are the URN and PWLink values, I would build them. I you are looking for all of the additional data, the Expand-PWDocumentInformation is the way to go.  I ran a measure-command on both for 1800 documents. Building the values took 67 milliseconds.  Using Expand-PWDocumentInformation, although pretty quick at 44 seconds, is much slower. Consider the number of documents being processed. 

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Neil White
      0 Offline Neil White Tue, Sep 12 2023 9:12 AM in reply to Brian Flaherty

      Thank you both for your response. Still struggling though, it's probably my terrible Powershell skills, but hopefully one of you can set me straight.

      The below is my existing script which achieves what I require, but takes about 1 hour to run through the current 3900 documents and will only get slower as the number of documents increases.  

      $docs = Get-PWDocumentsBySearch -FolderPath "02.0 Projects\" -Filename "%.*" -Attributes @{PW_DOC_GROUP = "ITT Document"} -GetAttributes
      
      # Create new datatable
      $dt = New-Object System.Data.DataTable
      $dt.TableName = 'Document Info'
      # Column Names to be added to datatable
      $ColumnNames = 'Scheme',"Document Title",'Revision Number','Revision Date','Revision Note','State','Assigned to','Reviewed by','Date Reviewed','Document Group','File Path','URN','Web'
      # Loop thru list of column names and create new column within datatable.
      foreach ($columnName in $ColumnNames) {
          $dt.Columns.Add($columnName)
      }
      
      # Loop thru each document
      foreach ($doc in $docs)
      {
          # Create datarow
          $dr = $dt.NewRow()
          
          # Populate datarow with appropriate document attribute data.
          $dr['Scheme'] = $doc.CustomAttributes.FI_PROJECTREF_CODE
          $dr["Document Title"] = $doc.CustomAttributes.TB_FILE_ID
          $dr['Revision Number'] = $doc.CustomAttributes. RV_REV_1
          $dr['Revision Date'] = $doc.CustomAttributes. RV_DATE_1
          $dr['Revision Note'] = $doc.CustomAttributes. RV_NOTE_1
          $dr['State'] = $doc.CustomAttributes.PW_STATE
          $dr['Assigned to'] = $doc.CustomAttributes.TE_ASSIGN_TO
          $dr['Reviewed by'] = $doc.CustomAttributes.TB_DRAWN_BY
          $dr['Date Reviewed'] = $doc.CustomAttributes.TB_DRAWN_DATE
          $dr['Document Group'] = $doc.CustomAttributes.PW_DOC_GROUP
          $dr['File Path'] = $doc.CustomAttributes.PW_FILEPATH
          $dr['URN'] = $doc.DocumentURN
          $dr['Web'] = $doc.ProjectWiseWebLink
      
          
          # Add datarow to the table
          $dt.Rows.Add($dr)
      }
      # Delete old file#
      Remove-Item -Path "C:\pwworkdir\Drive\CSL\00.0 Resources\02.0 Powershell Outputs\07 ITT Documents\ITT Documents.xlsx"-Force
      # Export to Excel Spreadsheet
      New-XLSXWorkbook -InputTables $dt -OutputFileName 'C:\pwworkdir\Drive\CSL\00.0 Resources\02.0 Powershell Outputs\07 ITT Documents\ITT Documents.xlsx' -PreserveLinksInExistingWorkbook -verbose
      ############
      Start-Sleep -s 2
      #########
      Get-PWDocumentsBySearch -FolderPath "00.0 Resources\02.0 Powershell Outputs\07 ITT Documents\" -DocumentName "ITT Documents.xlsx" -JustThisFolder -Verbose |
                          Update-PWDocumentFile -NewFilePathName 'C:\pwworkdir\Drive\CSL\00.0 Resources\02.0 Powershell Outputs\07 ITT Documents\ITT Documents.xlsx' -Verbose

      I was hoping to use the "Get-PWDocumentsBySearchWithReturnColumns" option instead, as it returns the output in less than 2 min.But I can't get the URN or Pwise Link.

      So, am I missing something with Get-PWDocumentsBySearch that is causing it to take so long?

      Any pointers appreciated.

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Brian Flaherty
      0 Offline Brian Flaherty Tue, Sep 12 2023 9:33 AM in reply to Neil White

      What are you attempting to do with this -Filename "%.*"?  

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Neil White
      0 Offline Neil White Tue, Sep 12 2023 10:10 AM in reply to Brian Flaherty

      That is just a hangup from a similar script where I just want PDF files.

      Get-PWDocumentsBySearch -FolderPath "02.0 Projects\" -Filename "%.pdf" -Attributes @{PW_DOC_GROUP = "MST-WPP"}

      Didn't think leaving it as a wildcard would have an effect, but guessing your question means it might?

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Brian Flaherty
      0 Offline Brian Flaherty Wed, Sep 13 2023 8:05 AM in reply to Neil White

      If you aren't filtering on specific document extensions, I would recommend removing it.

      How long is it taking to return the documents with the Get-PWDocumentsBySearch?  Most likely this is what is taking so long.

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    Reply
    • Brian Flaherty
      0 Offline Brian Flaherty Wed, Sep 13 2023 8:05 AM in reply to Neil White

      If you aren't filtering on specific document extensions, I would recommend removing it.

      How long is it taking to return the documents with the Get-PWDocumentsBySearch?  Most likely this is what is taking so long.

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    Children
    • Neil White
      0 Offline Neil White Wed, Sep 13 2023 9:42 AM in reply to Brian Flaherty

      Thanks again Brian.

      So it takes 35-40 min to run this script and removing the -Filename "%.*" doesn't seem to change that.

      When I alter the script to use "Get-PWDocumentsBySearchWithReturnColumns" it runs in about 2-3 mins, returns all my 'customattributes' I just don't get the URN or WebLink 

          $SC7dr['Document Group'] = $doc.CustomAttributes.PW_DOC_GROUP
          $SC7dr['File Path'] = $doc.CustomAttributes.PW_FILEPATH
          $SC7dr['URN'] = $doc.DocumentURN
          $SC7dr['Web'] = $doc.ProjectWiseWebLink

      I'm assuming this is because they aren't 'CustomAttributes' in my attribute table and therefore aren't returned as a column. This is the modified one that i'm trying.

      $SC7docs = Get-PWDocumentsBySearchWithReturnColumns -FolderPath "02.0 Projects\" -Attributes @{PW_DOC_GROUP = "ITT Document"} -ColumnsToReturn "FI_PROJECTREF_CODE", "TB_FILE_ID","RV_REV_1","RV_DATE_1","RV_NOTE_1","PW_STATE","TE_ASSIGN_TO","TB_DRAWN_BY", "TB_DRAWN_DATE", "PW_DOC_GROUP", "PW_FILEPATH", "URN", "Web" -Verbose
      # Create new datatable
      $SC7dt = New-Object System.Data.DataTable
      $SC7dt.TableName = 'Document Info'
      # Column Names to be added to datatable
      $SC7ColumnNames = 'Scheme',"Document Title",'Revision Number','Revision Date','Revision Note','State','Assigned to','Reviewed by','Date Reviewed','Document Group','File Path','URN','Web'
      # Loop thru list of column names and create new column within datatable.
      foreach ($SC7columnName in $SC7ColumnNames) {
          $SC7dt.Columns.Add($SC7columnName)
      }
      
      # Loop thru each document
      foreach ($doc in $SC7docs)
      {
          # Create datarow
          $SC7dr = $SC7dt.NewRow()
          
          # Populate datarow with appropriate document attribute data.
          $SC7dr['Scheme'] = $doc.CustomAttributes.FI_PROJECTREF_CODE
          $SC7dr["Document Title"] = $doc.CustomAttributes.TB_FILE_ID
          $SC7dr['Revision Number'] = $doc.CustomAttributes. RV_REV_1
          $SC7dr['Revision Date'] = $doc.CustomAttributes. RV_DATE_1
          $SC7dr['Revision Note'] = $doc.CustomAttributes. RV_NOTE_1
          $SC7dr['State'] = $doc.CustomAttributes.PW_STATE
          $SC7dr['Assigned to'] = $doc.CustomAttributes.TE_ASSIGN_TO
          $SC7dr['Reviewed by'] = $doc.CustomAttributes.TB_DRAWN_BY
          $SC7dr['Date Reviewed'] = $doc.CustomAttributes.TB_DRAWN_DATE
          $SC7dr['Document Group'] = $doc.CustomAttributes.PW_DOC_GROUP
          $SC7dr['File Path'] = $doc.CustomAttributes.PW_FILEPATH
          $SC7dr['URN'] = $doc.DocumentURN
          $SC7dr['Web'] = $doc.ProjectWiseWebLink
      
          
          # Add datarow to the table
          $SC7dt.Rows.Add($SC7dr)
      }
      # Delete old file#
      
      
      Remove-Item -Path "C:\pwworkdir\Drive\CSL\00.0 Resources\02.0 Powershell Outputs\07 ITT Documents\ITT Documents.xlsx"-Force
      # Export to Excel Spreadsheet
      New-XLSXWorkbook -InputTables $SC7dt -OutputFileName 'C:\pwworkdir\Drive\CSL\00.0 Resources\02.0 Powershell Outputs\07 ITT Documents\ITT Documents.xlsx' -PreserveLinksInExistingWorkbook -verbose
      ############

      Having looked at your suggestion of building the link myself I'm struggling to get the GUID into my attribute table so that "Get-PWDocumentsBySearchWithReturnColumns"  can return a value to build from? 

      Is that achievable with an select statement in a custom attribute ?

        

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Brian Flaherty
      0 Offline Brian Flaherty Wed, Sep 13 2023 12:01 PM in reply to Neil White

      Try adding the following immediately after getting your documents. This should include the DocumentURN and ProjectWiseWebLink values.

      $SC7docs.GetGeneralProperties()

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Neil White
      0 Offline Neil White Fri, Sep 15 2023 2:43 AM in reply to Brian Flaherty

      Thanks Brian, that does get the info I need.  It does seem quicker than the "Get-PWDocumentsBySearch"  so will give it a whirl.

      Not as quick as the "Get-PWDocumentsBySearchWithReturnColumns" option but every little helps.

      Thanks again for your help!

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Adrian Crowfoot
      0 Offline Adrian Crowfoot Fri, Sep 15 2023 9:35 AM in reply to Neil White

       Neil White - another option is to not get the custom attributes until you process the document. Remember that you can use the PW Document methods to process individual documents. This way you can filter down your documents to the needed documents BEFORE grabbing the custom attributes.

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Neil White
      0 Offline Neil White Fri, Sep 15 2023 9:52 AM in reply to Adrian Crowfoot

      That sounds promising, don't suppose you have any pointers on how to achieve that.

      Still very new to this Powershell (and ProjectWise ) game!

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel

    Communities
    • Home
    • Getting Started
    • Community Central
    • Products
    • Support
    • Secure File Upload
    • Feedback
    Support and Services
    • Home
    • Product Support
    • Downloads
    • Subscription Services Portal
    Training and Learning
    • Home
    • About Bentley Institute
    • My Learning History
    • Reference Books
    Social Media
    •    LinkedIn
    •    Facebook
    •    Twitter
    •    YouTube
    •    RSS Feed
    •    Email

    © 2023 Bentley Systems, Incorporated  |  Contact Us  |  Privacy |  Terms of Use  |  Cookies