Mixed Case Keywords - Database Reporting Guide - English - Foundation 22.1 - OnBase - external

Database Reporting Guide

Platform
OnBase
Product
Database Reporting Guide
Release
Foundation 22.1
License

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.

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

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.

KEYWORDSET Table Values
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.

KEYWORDSET table index
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.

KEYSETDATA*** Table Values
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.

KEYSETDATA*** Table Indexes
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.

KEYGROUPDATA*** Table Values
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.

KEYGROUPDATA*** Table Indexes
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.

KEYGROUPDATA*** Table Indexes
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