Individual Keyword Types - Database Reporting Guide - English - Foundation 22.1 - OnBase - external

Database Reporting Guide

Platform
OnBase
Product
Database Reporting Guide
Release
Foundation 22.1
License

The following section details the OnBase Keyword Type table structure and naming conventions, which are based on the data type of the Keyword Type.

Note: If a document does not contain an entry for a particular Keyword value, there is no record in the associated Keyword table.

For all but one Keyword Type, a single table is created in which the Keyword values are stored. This table is named KEYITEM###, where ### is the Keyword Type ID that can be found by querying the KEYTYPETABLE. The KEYITEM### table is cross-referenced between the OnBase document and its Keyword Type value. Any document can have more than one record with the same itemnum in this table.

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.

There is only one occurrence of a specific alphanumeric string in KEYTABLE###. The keywordnum is used to associate the alphanumeric string with the KEYXITEM### table.

The KEYXITEM### table is cross-referenced between the OnBase document and its Keyword Type value (keyvaluechar in KEYTABLE###). Any document can have more than one record with the same itemnum in this table.

Keyword Type Tables

Keyword Type Tables
Table Name Table Location Datatype in KEYTYPETABLE Keyword Type (OnBase Configuration)
KEYITEM### DBSpace6 6 Numeric (Up to 9 Digits)
KEYITEM### DBSpace6 1 Numeric (Up to 20 Digits)
KEYITEM### DBSpace6 4 Date
KEYITEM### DBSpace6 9 Date & Time
KEYITEM### DBSpace6 3 Currency
KEYITEM### DBSpace6 11 Specific Currency
KEYITEM### DBSpace6 5 Floating Point
KEYITEM### DBSpace6 10 Alphanumeric Single Table
KEYTABLE### DBSpace6 2 Alphanumeric Dual Table
KEYXITEM### DBSpace3 2 Alphanumeric Dual Table
KEYITEM### Table Design with datatype 6
Column Data Type Description
itemnum bigint The Document Handle.
keyvaluesmall bigint The 9-digit (maximum) numeric Keyword value for the document.
keysetnum bigint  
KEYITEM### Table Design with datatype 1
Column Data Type Description
itemnum bigint The Document Handle.
keyvaluebig decimal (20,0) The 20-digit (maximum) numeric Keyword value for the document.
keysetnum bigint  
KEYITEM### Table Design with datatype 4
Column Data Type Description
itemnum bigint The Document Handle.
keyvaluedate datetime The date Keyword value for the document.
keysetnum bigint  
KEYITEM### Table Design with datatype 9
Column Data Type Description
itemnum bigint The Document Handle.
keyvaluetod datetime The date/time Keyword value for the document.
keysetnum bigint  
KEYITEM### Table Design with datatype 3
Column Data Type Description
itemnum bigint The Document Handle.
keyvaluecurr decimal(20,2) The currency Keyword value for the document.
keysetnum bigint  
KEYITEM### Table Design with datatype 11
Column Data Type Description
itemnum bigint The Document Handle.
keyvaluecurr decimal(20,2) The currency Keyword value for the document.
keysetnum bigint  
currencyformatnum bigint The currency format for the specific Keyword value cross-referenced to the CURRENCYFORMAT table.
KEYITEM### Table Design with datatype 5
Column Data Type Description
itemnum bigint The Document Handle.
keyvaluefloat float The floating point Keyword value for the document.
keysetnum bigint  
KEYITEM### Table Design with datatype 10
Column Data Type Description
itemnum bigint The Document Handle.
keyvaluechar char(length) The alphanumeric Keyword value for the document, where length is the length of the Keyword, up to 251.
keysetnum bigint  
KEYTABLE### Table Design with datatype 2
Column Data Type Description
keywordnum bigint The unique identifier for the alphanumeric string.
keyvaluechar char(length) The alphanumeric Keyword value for the document, where length is the length of the Keyword, up to 251.
KEYXITEM### Table Design with datatype 2
Column Data Type Description
itemnum bigint The Document Handle.
keywordnum bigint The unique identifier of the KEYTABLE### record associated with the document.
keysetnum bigint  

The indexes for the KEYITEM### tables are located in DBSpace6i.

KEYITEM### Table Indexes
Index Name Included Columns
keyitem###1 itemnum, keyvalue<datatype>
keyitem###2 keyvalue<datatype>, itemnum, currencyformatnum (exists only for Specific Currency Keyword Type index.)

The indexes for the KEYTABLE### tables are located in DBSpace6i.

KEYTABLE### Table Indexes
Index Name Included Columns
keytable###1 keywordnum
keytable###2 keyvaluechar, keywordnum

The indexes for the KEYXITEM### tables are located in DBSpace3i.

KEYXITEM### Table Indexes
Index Name Included Columns
keyxitem###1 itemnum, keywordnum
keyxitem###2 keywordnum, itemnum