sql query to find the original document associated with a workflow

I need to figure out how a workflow relates back to the original document that it was initiated from. I’ve gone through the workflow tables and can’t seem to find it. I can find the attachment that the workflow creates, but then I can’t find the original document. I’m assuming there must be a way that the system keeps track of this since I know that you can see when a document is in workflow on the general tab of the original document.

Find more posts tagged with

Comments

praveen_nagarajan

Hi,

Here is the SQL query to get the source document dataid.

SELECT extendeddata FROM dtree WHERE dataid = <dataid of the document in the workflow attachments volume>
Ex Output: A<1,?,'WF_OrigID'=28181>
where WF_OrigID in the Assoc holds the source document's data id

I hope this helps.

Best Regards
Praveen
Supai Systems

On Mon, Oct 24, 2011 at 9:53 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

sql query to find the original document associated with a workflow ? Posted byjosephine.bush@res-americas.com (Bush, Josephine)On 10/24/2011 12:11 PM
?
I need to figure out how a workflow relates back to the original document that it was initiated from.? I?ve gone through the workflow tables and can?t seem to find it.? I can find the attachment that the workflow creates, but then I can?t find the original document.? I?m assuming there must be a way that the system keeps track of this since I know that you can see when a document is in workflow on the general tab of the original document.? ?
[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

Josephine_Bush

Hi Praveen,

Very good info - thanks! Now I just have to figure out how to extract the orig data id from extended data and be able to compare it to the dataid as an int.

Josephine_Bush

comparing extracted extendeddataid with dataid

create view vw_extendeddatanvarchar as

select distinct convert ( nvarchar ( max ), extendeddata ) as extendeddataconvert from dtree where extendeddata like '%WF_OrigID%'

select

substring ( PARSENAME ( REPLACE ( SUBSTRING ( extendeddataconvert , 2 , len ( extendeddataconvert )), '=' , '.' ), 1 ),

1 ,

( len ( PARSENAME ( REPLACE ( SUBSTRING ( extendeddataconvert , 2 , len ( extendeddataconvert )), '=' , '.' ), 1 )) - 1 )) as orig_id , convert ( varchar ( 20 ), dataid ) as dataidconvert , d .*

from dtree d , vw_extendeddatanvarchar

where convert ( varchar ( 20 ), substring ( PARSENAME ( REPLACE ( SUBSTRING ( extendeddataconvert , 2 , len ( extendeddataconvert )), '=' , '.' ), 1 ),

1 ,

( len ( PARSENAME ( REPLACE ( SUBSTRING ( extendeddataconvert , 2 , len ( extendeddataconvert )), '=' , '.' ), 1 )) - 1 ))) = convert ( varchar ( 20 ), dataid )

and dataid = 5562837