Query for EMAIL Related KeyItems

[Tomas_Vera]

Hello All,

I am writing a SQL query for a DocType that contains some EMAIL related Keyitems.

Focusing on the "MAIL to" keyword, I see that it is assigned KeyItem7 (see image). But unlike other Keywords, there doesn't seem to be any associated table in the database that I can query.

How can I query the content of that Keyword / KeyItem?

Thanks,

-tomas

[George_Sialmas · Accepted solution]

@Tomas Vera If I'm not mistaken, all the MAIL* Keyword Types that have a data type of "Alphanumeric" and the storage option is set to "Dual Table" the database able is different to when a Keyword Type is configured as a Single Table Alphanumeric.  The Database Reporting Guide MRG explains this in the Individual Keyword Types section. For the Dual Table Alphanumeric Keyword Type, there are two tables created in which Keyword information is stored: KEYTABLE### and KEYXITEM###, where ### is the Keyword Type ID that can be found by querying the  KEYTYPETABLE.

Regards,

George

[Tomas_Vera]

Thank you for this information!!

[Johnson_Dean]

Here is a full example that you can use to narrow down what you are looking for and narrowed down to a single DH to minimize DB resources. 

select id.itemnum as "DocHandle", k17.keyvaluesmall as "Attachment Count", k14.keyvaluedate as "Date" , k15.keyvaluesmall as "State", k16.keyvaluesmall as "Cerfified", k79.keyvaluetod as "DateTime", kt76.keyvaluechar as "Attachment Name", kt9.keyvaluechar as "Bcc", kt8.keyvaluechar as "Cc", kt26.keyvaluechar as "Cc Address", kt10.keyvaluechar as "From", kt27.keyvaluechar as "From Address", kt12.keyvaluechar as "MessageID", kt12.keywordnum as "keywordnum", kt13.keyvaluechar as "Return Path", kt80.keyvaluechar as "Status", kt11.keyvaluechar as "Subject", kt7.keyvaluechar as "To", kt25.keyvaluechar as "To Address" from hsi.itemdata idleft outer join hsi.keyitem14 k14 on k14.itemnum = id.itemnum --Normal Tableleft outer join hsi.keyitem15 k15 on k15.itemnum = id.itemnum --Normal Tableleft outer join hsi.keyitem16 k16 on k16.itemnum = id.itemnum --Normal Tableleft outer join hsi.keyitem17 k17 on k17.itemnum = id.itemnum --Normal Tableleft outer join hsi.keyitem79 k79 on k79.itemnum = id.itemnum --Normal Tableleft outer join hsi.keyxitem27 kx27 on kx27.itemnum = id.itemnumleft outer join hsi.keytable27 kt27 on kt27.keywordnum = kx27.keywordnum left outer join hsi.keyxitem76 kx76 on kx76.itemnum = id.itemnumleft outer join hsi.keytable76 kt76 on kt76.keywordnum = kx76.keywordnumleft outer join hsi.keyxitem9 kx9 on kx9.itemnum = id.itemnumleft outer join hsi.keytable9 kt9 on kt9.keywordnum = kx9.keywordnum left outer join hsi.keyxitem8 kx8 on kx8.itemnum = id.itemnumleft outer join hsi.keytable8 kt8 on kt8.keywordnum = kx8.keywordnum left outer join hsi.keyxitem26 kx26 on kx26.itemnum = id.itemnumleft outer join hsi.keytable26 kt26 on kt26.keywordnum = kx26.keywordnumleft outer join hsi.keyxitem10 kx10 on kx10.itemnum = id.itemnumleft outer join hsi.keytable10 kt10 on kt10.keywordnum = kx10.keywordnumleft outer join hsi.keyxitem12 kx12 on kx12.itemnum = id.itemnumleft outer join hsi.keytable12 kt12 on kt12.keywordnum = kx12.keywordnumleft outer join hsi.keyxitem13 kx13 on kx13.itemnum = id.itemnumleft outer join hsi.keytable13 kt13 on kt13.keywordnum = kx13.keywordnumleft outer join hsi.keyxitem80 kx80 on kx80.itemnum = id.itemnumleft outer join hsi.keytable80 kt80 on kt80.keywordnum = kx80.keywordnumleft outer join hsi.keyxitem11 kx11 on kx11.itemnum = id.itemnumleft outer join hsi.keytable11 kt11 on kt11.keywordnum = kx11.keywordnumleft outer join hsi.keyxitem7 kx7 on kx7.itemnum = id.itemnumleft outer join hsi.keytable7 kt7 on kt7.keywordnum = kx7.keywordnumleft outer join hsi.keyxitem25 kx25 on kx25.itemnum = id.itemnumleft outer join hsi.keytable25 kt25 on kt25.keywordnum = kx25.keywordnum where id.itemnum = 491826215-- SYS MAIL Attachment Count = 17-- SYS MAIL Attachment Name = 76-- SYS MAIL Bcc = 9-- SYS MAIL Cc = 8-- SYS MAIL Cc Address = 26-- SYS MAIL Certified = 16-- SYS MAIL Date = 14-- SYS MAIL Date Time = 79-- SYS MAIL From = 10-- SYS MAIL From Address = 27-- SYS MAIL MessageID = 12-- SYS MAIL Return Path = 13-- SYS MAIL State = 15-- SYS MAIL Status = 80-- SYS MAIL Subject = 11-- SYS MAIL To = 7-- SYS MAIl To Address = 25

[Tomas_Vera]

Thank you for the detailed example. You have provided exactly what I was looking for.