Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
ProjectWise
  • Product Communities
ProjectWise
ProjectWise PowerShell Extensions Forum Using "Select-PWSQL" cmdlet, which table can I find the document "FullPath"?
    • Sign In

    • State Verified Answer
    • Replies 15 replies
    • Subscribers 68 subscribers
    • Views 3231 views
    • Users 0 members are here
    • powershell
    • pwps_dab
    • SQL
    • ProjectWise Programming

    Using "Select-PWSQL" cmdlet, which table can I find the document "FullPath"?

    Edward Ashbolt
    Offline Edward Ashbolt over 2 years ago

    As per title I want to return the full path of all documents on the server. I found that the "dms_doc" table has everything I need for a specific task except the full path for each document. Any ideas which table I can look at that will contain the fully qualified path of each folder?

    Hopefully once I know which table contains the full path it will also have either project GUID / project number; then I can relate that back to those columns in dms_doc to expand it to include a full path to each file.

    Thanks!



    Ed

    • Sign in to reply
    • Cancel

    Top Replies

    • Brian Flaherty
      Offline Brian Flaherty Mon, Nov 23 2020 9:45 AM +1
      The full path values are derived. There isn't a table in ProjectWise containing those values.
    • Kevin van Haaren
      Offline Kevin van Haaren Mon, Nov 23 2020 10:53 AM +1 verified
      If I remember correctly, dms_doc has a guid of the folder it's in, then you have to jump to another table (I think it's called dms_vault or project, but I'm not on my work computer and can't look it up…
    • Brian Flaherty
      0 Offline Brian Flaherty Mon, Nov 23 2020 9:45 AM

      The full path values are derived. There isn't a table in ProjectWise containing those values. 

      • Cancel
      • Vote Up +1 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Kevin van Haaren
      +1 Offline Kevin van Haaren Mon, Nov 23 2020 10:53 AM

      If I remember correctly, dms_doc has a guid of the folder it's in, then you have to jump to another table (I think it's called dms_vault or project, but I'm not on my work computer and can't look it up). In that table each folder lists its parent folder, so then you have to jump up to the parent and check it's parent, and so on until you find the root folder. Basically you have to walk the tree backwards to build it.

      Why not use one of the PWPS_DAB cmdlets to do what you wan?. When I want to build a report with a large number of items I typically break it down a bit so I'll get all the folders at the root level then process each of those folders independently. That way I don't need gigabytes of local memory to hold all the objects.

      I believe Get-PWFolders and Get-PWDocumentsBySearch can both return full paths.

       

      Answer Verified By: Edward Ashbolt 

      • Cancel
      • Vote Up +1 Vote Down
      • Sign in to reply
      • Reject Answer
      • Cancel
    • Brian Flaherty
      0 Offline Brian Flaherty Mon, Nov 23 2020 12:26 PM in reply to Kevin van Haaren

      You can get the o_projguid value for each document and pass that to the Get-PWFoldersByGUIDs cmdlet.

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Edward Ashbolt
      0 Offline Edward Ashbolt Mon, Nov 23 2020 4:54 PM in reply to Brian Flaherty

      OK thanks Brian, I assume when you say they are derived that you mean I need to recurse through the folder tree to populate all the paths myself, as Kevin mentions below?

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Edward Ashbolt
      0 Offline Edward Ashbolt Mon, Nov 23 2020 5:09 PM in reply to Kevin van Haaren

      Thanks Kevin,
      There is a table called dms_proj that has all the folder names / guids, I assume that is the one you meant... Looking at that table there is o_pprjguid which should be the parent folder. With this information I can then build a fullpath by recursively walking backwards through the folder tree as you suggested; thanks for the tip that's a really good idea!

      With regards to using the pwps_dab search cmdlets for returning paths, I did want to go that route however I found in practice it was far too slow to generate the list of documents when running on a very large datasource, hence looking to go the SQL route since I can download the tables I need 10x faster than a search, and then do my processing locally using PowerShell and an SQLite db.

      Thanks!

      Ed

      • 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