How do I report on memos saved in the history log?

[Laura_Wilke]

I know that the history log is saved in rmobjecthistory. There are memo attributes that reference a memoID in the startvalue and endvalue rather than the actual value. It appears that the values are saved in rmhistmemo. How do I link the two tables?

startvalue	endvalue
memoID:0	memoID:120225
memoID:120226	memoID:120227
memoID:120228	memoID:0

[Barbara_Ebel-La · Accepted solution]

Hi Laura,

Here is a query to get you started.  If you have a large text attribute in your WorkView class, the attribute will be stored in the database as mk#### instead of attr#### (Ex: hsi.rmobjectinstance1174.mk2702 instead of hsi.rmobjectinstance1174.mk2702).  

SELECT memo
FROM hsi.rmobjectinstance1174 rm1174 -- WorkView class
join hsi.rmmemo rm on rm.memoid = rm1174.mk2702 -- Workview large text attribute
;

[Laura_Wilke]

Hi Barbara,

That is good to know, but the memo I am trying to report is not a Workview attribute. It is saved only in the object's history log on rmobjecthistory.  As you can see in my screenshots above, the startvalue and endvalue in the history log reference a memoID but I can't link either of those attributes to the memoID on the rmhistmemo table. I am looking for a way to specifically link those two tables.

[Barbara_Ebel-La]

I didn't realize you could have a memo that's not tied to an attribute.

This took forever to run, but appears to work:

SELECT rmo.transactionid,
rmo.objectid,
rmo.attributeid,
trim(rmo.startvalue),
rmh1.memo StartValue,
trim(rmo.endvalue),
rmh2.memo EndValue
FROM hsi.rmobjecthistory rmo
inner join hsi.rmhistmemo rmh1
on ('memoID:'||(trim(rmh1.MEMOID)) = trim(rmo.startvalue))
inner join hsi.rmhistmemo rmh2
on ('memoID:'||(trim(rmh2.MEMOID)) = trim(rmo.endvalue))
WHERE rmo.transactiondate >= sysdate - 0.01
;

[Laura_Wilke]

Thank you Barbara. That works but only if I omit the where clause. What is it's purpose? I also had to use rtrim(ltrim(rmo.startvalue)) instead of trim(rmo.startvalue). Apparently we are on an older server of SQL Server.