As of OnBase version 6.2, Single Table and Dual Table Alphanumeric Keywords can store alphanumeric values in mixed case rather than all uppercase. This is accomplished by adding an additional column to the KEYITEM### and KEYTABLE### tables named keyvaluecharcs. The text value is still stored in uppercase in the keyvaluechar column for searching purposes.
For a Single Table Alphanumeric keyword, the datatype=13 in KEYTYPETABLE.
For a Dual Table Alphanumeric keyword, the datatype=12 in KEYTYPETABLE.
The following section details the Autofill Keyword Set, Keyword Type Group, and Multi-Instance Keyword Type Group table structure and naming conventions.
KEYWORDSET
The KEYWORDSET table has one entry for every Autofill Keyword Set, Keyword Type Group, or MultiInstance Keyword Type Group in the OnBase system. Like the KEYTYPETABLE, this table is not typically one of the main, driving tables in a query, but rather one that can be joined against to obtain additional information. The KEYWORDSET table is located in DBSpace8.
Column Name | Data type | Description |
---|---|---|
keysettablenum | bigint | The unique identifier for the Keyword Set or Group. |
keysetname | char(80) | |
tablecreated | bigint | |
updatekeyset | bigint | |
updatekeysetdocs | bigint | |
removeunusedkeyset | bigint | |
iskeytypegroup | bigint | 0 = Autofill Keyword Set, 1 = Keyword Type Group, 2 = Multi-Instance Keyword Type Group. |
autonamestring | char(150) | |
vbscriptnum | bigint | The ID of the VB Script used by an External Autofill Keyword Set. |
selectstring | char(16) | The SQL string used to retrieve Keyword values by an External Autofill Keyword Set. |
connectstring | char(255) | The ODBC source, user name, and password used by an External Autofill Keyword Set. |
The index for the KEYWORDSET table is located in DBSpace8.
Index Name | Included Columns |
---|---|
keywordset1 | Keysettablenum |
KEYSETDATA***
Each Autofill Keyword Set in the system has a KEYSETDATA*** table, where *** is the unique identifier of the Keyword Set (keysettablenum in KEYWORDSET). There can be multiple Keyword Types assigned to an Autofill Keyword Set. Each Keyword Type has its own ks### column in the table. All ks### columns are a char data type, regardless of the data type specified for the Keyword Type in the KEYTYPETABLE table. The KEYSETDATA*** table is located in DBSpace6.
Column Name | Data Type | Description |
---|---|---|
keysetnum | bigint | The unique identifier for each row in the table. |
ks### | char( length ) | The Keyword Type assigned to the Autofill Keyword Set, where ### is the unique identifier for the Keyword Type (cross-referenced to KEYTYPETABLE). |
useagecount | bigint |
The indexes for the KEYSETDATA*** table are located in DBSpace6i.
Index Name | Included Columns |
---|---|
Akeysetdata*** | ks### (the primary Keyword Type in the set) |
keysetdata***_2 | Keysetnum |
KEYGROUPDATA***
Each Keyword Type Group in the system has a KEYGROUPDATA*** table, where *** is the unique identifier of the Keyword Type Group (keysettablenum in KEYWORDSET). There can be multiple Keyword Types assigned to a Keyword Type Group. Each Keyword Type will have its own kg### column in the table. If the Keyword Type Group is configured to include Document Date, the itemdate column is included in the table. There can only be one row for each itemnum in the table. The KEYGROUPDATA*** table is located in DBSpace6.
Column Name | Data Type | Description |
---|---|---|
itemnum | bigint | The Document Handle. |
itemdate | bigint | This column exists only if Document Date is included in the group. |
kg### | Determined by Keyword Type Data Type | The Keyword Type assigned to the Keyword Type Group, where ### is the unique identifier for the Keyword Type (cross-referenced to KEYTYPETABLE). |
kgcs### | keyvaluechar ( length ) |
This column exists only for Mixed Case Alphanumeric Keyword Types (in addition to the kg### column). |
cf### | bigint | This column exists only for Specific Currency Keyword Types (in addition to the kg### column). |
The indexes for the KEYGROUPDATA*** table are located in DBSpace6i.
Index Name | Included Columns |
---|---|
Akeygroupdata***_### | kg###, cf###, itemnum Note: The cf###
column only exists for Specific Keyword Type
Index.
|
Akeygroupdata***_### | kg###, itemdate, itemnum Note: The itemdate
column only exists for Keyword Type Groups that include Document
Date.
|
keygroupdata***_2 | Itemnum |
KEYRECORDDATA***
Each Multi-Instance Keyword Type Group in the system has a KEYRECORDDATA*** table, where *** is the unique identifier of the Multi-Instance Keyword Type Group (keysettablenum in KEYWORDSET). There can be multiple Keyword Types assigned to a Multi-Instance Keyword Type Group. Each Keyword Type has its own kg### column in the table. If the Keyword Type Group is configured to include Document Date, the itemdate column is included in the table. There can be multiple rows for each itemnum in the table, each having a different recordnum. The KEYRECORDDATA*** table is located in DBSpace6.
Column Name | Data Type | Description |
---|---|---|
itemnum | bigint | The Document Handle. |
recordnum | bigint | The unique identifier for each row in the table. |
itemdate | bigint | This column exists only if Document Date is included in the group. |
kg### | Determined by Keyword Type Data Type | The Keyword Type assigned to the Keyword Type Group, where ### is the unique identifier for the Keyword Type (cross-referenced to KEYTYPETABLE). |
kgcs### | keyvaluechar ( length ) |
This column exists only for Mixed Case Alphanumeric Keyword Types (in addition to the kg### column). |
cf### | bigint | This column exists only for Specific Currency Keyword Types (in addition to the kg### column). |
The indexes for the KEYRECORDDATA*** table are located in DBSpace6i.
Index Name | Indexed Columns |
---|---|
Akeygroupdata***_### | kg###, cf###, itemnum Note: The cf###
column only exists for Specific Keyword Type
Index.
|
Akeygroupdata***_### | kg###, itemdate, itemnum Note: The itemdate
column only exists for Keyword Type Groups that include Document
Date.
|
keygroupdata***_2 | Itemnum |