07-29-2021 06:00 AM
I've seen a couple older threads, but wanted to check and see if someone has figured out a way to gather all keywords on a doc type and separate them into their own column. Like others, I can gather the item name, but that is what the auto string is. Any idea on how to separate them out? I can generate a CSV file on all the documents and their keywords, but a query seems to be a better way then locking up the system.
Thank you!
08-02-2021 10:58 AM
I use this alot, I hope it helps
SELECT
dg.itemtypegroupnum as 'Doc Type Group Num',
rtrim(dg.itemtypegroupname) as 'Doc Type Group Name',
xr.itemtypenum as 'Doc Type Num',
rtrim(dt.itemtypename) as 'Doc Type Name',
--rtrim(dt.AUTONAMESTRING) as 'Auto-Name',
xr.keytypenum as 'Key Type Num',
rtrim(kt.keytype) as 'Key Type Name',
xr.seqnum as 'Seq',
--kt.KEYTYPEMASK as 'Mask',
KEYTYPEFLAGS as 'Flags',
DATATYPE as 'Data Type',
KEYTYPELEN as 'Length'
FROM [itemtypexkeyword] XR
JOIN DOCTYPE (NOLOCK) DT ON DT.ITEMTYPENUM = XR.ITEMTYPENUM
JOIN KEYTYPETABLE (NOLOCK) KT ON KT.KEYTYPENUM = XR.keytypenum
JOIN ITEMTYPEGROUP (NOLOCK) DG ON DG.itemtypegroupnum = DT.itemtypegroupnum
WHERE DT.ITEMTYPENAME IN ('<<<DOCUMENT TYPE NAME >>>')
ORDER BY dt.itemtypename, kt.keytype
07-29-2021 09:02 AM
07-30-2021 08:12 AM
Sorry for the delay. I would like to see the keyword values on a document in their own columns. It would pretty much be the same output as the generate csv does.
07-29-2021 09:10 AM
The following query provides a comma separated list of the Keyword Types associated with each Document Type:
/* KeywordsInDocTypesReport.sql * * Roger D. Linhart 11/20/2017 OnBase 17.0.0.57 * Jeff Pinkerton provided initial query. * * Show Keywords in Document Types. * * To list Keywords in all Document Types: * --SET @myDocType = 'EA'; * * To list Keywords in Document Types LIKE 'EA%': * SET @myDocType = 'EA'; * */DECLARE @myDocType nvarchar(51) = '';--SET @myDocType = 'EA';SET @myDocType += '%';SELECT DISTINCT rtrim(A.itemtypename) AS 'Document Type', STUFF ((SELECT ', ' + rtrim(C.Keytype) FROM hsi.itemtypexkeyword B WITH (NOLOCK) INNER JOIN hsi.keytypetable C WITH (NOLOCK) ON B.keytypenum = C.keytypenum WHERE B.itemtypenum = A.itemtypenum ORDER BY B.keytypenum FOR XML PATH('')), 1, 1, '') AS KeywordsFROM hsi.doctype A WITH (NOLOCK) WHERE (A.itemtypename LIKE @myDocType)
07-29-2021 12:27 PM
excellent!
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.