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

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