Trying to locate Original Keyword values

[Jim_Percival]

Hi all, I am new to Hyland and Hyland Reporting, and I've been asked to create some reports to help with the QA process. Basically I'm looking to create a report that (per document) will show Before and After values of QA'd and modified keywords. For example...

Document 123:
Original Descriptor = Consent for Anesthesia... New Descriptor = Designation of Caregiver

Original Doc Type = Advance Directives and Living Will... New Doc Type = MOLST.

In the process of trying to wrap my head around all these tables and columns, I'm finding that the keyitem### tables will store the NEW keyword values. I also noticed column hsi.transactionxlog.tmessage will store some explanation of the transaction. BUT, my question is: is the ORIGINAL keyword value stored in a discrete field anywhere, after it has been changed? If anyone has any pointers at all for this type of goal please let me know.

I apologize if this forum isn't the right place for a question like this.

Thanks
Jim

[Edward_Washing2]

Not sure how specific you want the formatting, but here is a query that would return the information you are looking for. 

This was written on an Oracle Database.

In regards to the Original keywords value, vs the new one, the assumption is that there was a keyword value at the time of creation. 

as well as that every time a keyword is deleted, another is added in its place. 

What about documents that have had their keyword values changed more than 2 times ? 

This report will bring you back a list. The first columns will count the number of changes, and start over every time a new document handle is found. 

Warning......these are extremely active tables in OnBase and over use of this SQL query could affect performance. 

select row_number() over (partition by t.itemnum order by t.transactionnum )"rNum",

t.transactionnum,

rtrim(t.TMESSAGE),

t.itemnum,t.logdate,

t.usernum,

ua.username
from hsi.TRANSACTIONXLOG t

join hsi.useraccount ua on t.usernum = ua.usernum /*brings back the username*/
join hsi.itemdata id on t.itemnum = id.itemnum /*allows us to filter by document type #*/

where action in (1035,1034)

/* the actions I found for when deleting / adding a keyword*/

and

trunc(logdate) BETWEEN to_date('06/01/2018','MM/DD/YYYY')

and

to_date('06/30/2018','MM/DD/YYYY')

/*date filtering in Oracle*/

and id.ITEMTYPENUM in (315)

/*Document type #*/

order by t.itemnum, t.TRANSACTIONNUM