Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
ProjectWise
  • Product Communities
ProjectWise
ProjectWise Design Integration Forum Populating Document Attribute with 'Connected Project GUID' With SQL Statement
    • Sign In

    • State Verified Answer
    • Replies 5 replies
    • Subscribers 62 subscribers
    • Views 490 views
    • Users 0 members are here
    • ProjectWise
    • Attributes
    • SQL
    • ProjectWise Documents

    Populating Document Attribute with 'Connected Project GUID' With SQL Statement

    Henrik Thornkvist
    Offline Henrik Thornkvist 11 months ago

    Hi all,

    I've been trying for a while now to cobble together an attribute that contains a "Get Link"-like address to the target document. The intention is to keep a trail to the source document whenever it's exported (through attribute exchange), attribute data is exported or files are transmitted via deliverables management. I've managed to get the object ID and Work Area ID but the Connected Project ID still eludes me. The reason I'm wanting the ouput to look like the below is that I have some users that exclusively use web services and some users that exclusively use PW Explorer.

    Here's an example of the link I'm trying to cobble together:

    https://connect-projectwisewac.bentley.com/pwlink?datasource=[MY_SERVER]~3A[MY_DATASOURCE]&objectId=36f7c030-fee5-4114-bacd-81f06da922c9&objectType=doc&workAreaId=0c172821-a7e0-4c36-9dcd-1dae455b32fe&projectId=02455478-3a9a-4813-949d-f91cc00cc041

    Object ID and Work Area ID I'm getting like this (Thank you, Brian Flaherty!):

    SELECT o_docguid FROM dms_doc WHERE o_projectno LIKE $VAULT$  AND o_itemno LIKE $DOCUMENT$

    SELECT o_projguid AS GUID FROM dms_proj WHERE o_projectname LIKE '$EDIT#PROJ_FOLDER_NAME$'

    Is there similar SQL that I can use to get the Connected Project GUID? Also, if there's a better way to get the project ID (like sourcing the parent connected project without returning the value through a folder name / description) that would be super helpful as well. I don't have a very good understanding of the tables so I'm mostly guessing my way to syntax / table and attribute names.

    Cheers,

    Henrik

    • Sign in to reply
    • Cancel

    Top Replies

    • Peter Willmen
      Offline Peter Willmen Tue, Dec 20 2022 9:53 AM +1 verified
      Hi Henrik, I don't think you actually need the Work Area or Project ID's for PWE or Web Connection Try this format:- connect-projectwisewac.bentley.com/pwlink?datasource=[MY_SERVER]~3A[MY_DATASOURCE…
    • Peter Willmen
      Offline Peter Willmen Wed, Oct 25 2023 11:14 AM in reply to Henrik Thornkvist +1
      The Work Area ID ( workAreaId=0c172821-a7e0-4c36-9dcd-1dae455b32fe&projectId=02455478-3a9a-4813-949d-f91cc00cc041) can be copied from the Connected Project URL and added to an attribute for re-use in your…
    Parents
    • Peter Willmen
      +1 Offline Peter Willmen Tue, Dec 20 2022 9:53 AM

      Hi Henrik,

      I don't think you actually need the Work Area or Project ID's for PWE or Web Connection

      Try this format:-

      connect-projectwisewac.bentley.com/pwlink?datasource=[MY_SERVER]~3A[MY_DATASOURCE]&objectId=36f7c030-fee5-4114-bacd-81f06da922c9&objectType=doc&app=pwe

      'objectType=folder' also works with the folder GUID as the objectId

      You should then get the option to open the link via PWE or Web

      Answer Verified By: Henrik Thornkvist 

      • Cancel
      • Vote Up +1 Vote Down
      • Sign in to reply
      • Verify Answer
      • Reject Answer
      • Cancel
    • Henrik Thornkvist
      0 Offline Henrik Thornkvist Wed, Jan 4 2023 6:09 AM in reply to Peter Willmen

      Happy new year, Pete! You're a genius. It works perfectly and does exactly what I need it to do. By not having to include Work Area and Project IDs I don't have to worry about the folder references, etc, in the SQL and can trim a few of the helper attributes I was resigned to creating.

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Byron Hickey
      0 Offline Byron Hickey Thu, Oct 19 2023 2:30 PM in reply to Henrik Thornkvist

      Hi Henrik. I am trying to do the same thing, but I cannot seem to figure out the sql query.  Did you use Concat to combine parts of the url as a string with the values you returned from the select statements you posted in the original post? It seems that if I try anything more complicated than selecting columns from  a table nothing gets returned. Could you share your query?

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    Reply
    • Byron Hickey
      0 Offline Byron Hickey Thu, Oct 19 2023 2:30 PM in reply to Henrik Thornkvist

      Hi Henrik. I am trying to do the same thing, but I cannot seem to figure out the sql query.  Did you use Concat to combine parts of the url as a string with the values you returned from the select statements you posted in the original post? It seems that if I try anything more complicated than selecting columns from  a table nothing gets returned. Could you share your query?

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

    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