02-19-2020 03:05 PM
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 |
02-20-2020 05:53 AM
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
;
02-20-2020 05:53 AM
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
;
02-20-2020 01:39 PM
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.
02-20-2020 02:22 PM
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
;
02-21-2020 10:33 AM
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.
Find what you came for
We want to make your experience in Hyland Connect as valuable as possible, so we put together some helpful links.