12-06-2023 05:34 AM
I have to generate report which has the following Column :
12-06-2023 08:05 AM
That might help point you towards your keyword tables. In OnBase all keywords have a keytype number. That number usually corresponds to a table name like hsi.keyitem79 in the database with the keyword you are looking for. Then for example, you can join the itemnum in the hsi.keyitem79 to the hsi.itemdata table.
Reporting Dashboards does have an OOB document query that you could try and see if that would fit your needs before writing a custom sql query. I would think that it should give you a baseline of what your asking. Only thing I'm not sure on would be how you could count the number of documents with more than one value for that keyword. That isn't something I have tried yet.
Does that help point you in the right direction? Let me know if you have any other questions.
12-08-2023 02:17 AM
If there are 150 keyword then how can I search keyword table for each keyword & find out the more keyword values.
12-08-2023 06:16 AM
I have to do this for each table there is any short cut for this
select dt.itemtypename, kt1.itemnum [Doc Handle], kt11.keyvaluechar [Keyword Value]
from hsi.keyxitem1 kt1
inner join hsi.keytable1 kt11 on kt11.keywordnum = kt1.keywordnum
inner join hsi.itemdata item on item.itemnum = kt1.itemnum
inner join hsi.doctype dt on dt.itemtypenum = item.itemtypenum
group by dt.itemtypename , kt1.itemnum , kt11.keyvaluechar having count(kt1.itemnum) > 1
union
select dt.itemtypename, kt2.itemnum [Doc Handle], kt22.keyvaluechar [Keyword Value]
from hsi.keyxitem1 kt2
inner join hsi.keytable1 kt22 on kt22.keywordnum = kt2.keywordnum
inner join hsi.itemdata item on item.itemnum = kt2.itemnum
inner join hsi.doctype dt on dt.itemtypenum = item.itemtypenum
group by dt.itemtypename , kt2.itemnum , kt22.keyvaluechar having count(kt2.itemnum) > 1
union
select dt.itemtypename, item.itemnum [Doc Handle], k126.keyvaluechar [Keyword Value]
from hsi.keyitem126 k126
inner join hsi.itemdata item on item.itemnum = k126.itemnum
inner join hsi.doctype dt on dt.itemtypenum = item.itemtypenum
group by dt.itemtypename ,item.itemnum , keyvaluechar having count(k126.itemnum) > 1
12-15-2023 09:37 AM
Normally, I do something like this when running a report on keywords on a document type. However, I'm usually limiting my query to a specific document type group or document type. The below is just for a single document type where documents are active (not deleted). Then you could add in your having clause in there. Not sure if that helps simply your query above or not.
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.