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 |