Custom Field Tables and Views
System and Custom objects can have custom fields associated with them. To support these custom fields, categories are created in TeamConnect. Each of these categories has associated tables in the Warehouse to store custom fields data. The tables are identified by WHCF_XXXX and WHCC_XXXX, which work together to build the WH_CF_XXXX view, and also, WHCF_V_XXXX and WHCC_V_XXXX tables which work together to build the WH_CF_V_XXXX view.
Note: XXXX represents the tree position of the category in the database. For example, a custom field is created for an Account under a category called Settlement. The Settlement category has a tree position of ACCT_SETT, so any custom fields associated with Settlement will be in a view called WH_CF_ACCT_SETT.
The information about all of these custom fields and categories are stored in tables called WH_CF_MAPPINGS and WH_CC_MAPPINGS. A view called WH_MAPPING_CF consists of the mappings between the table and column in the TeamConnect relational database and the table and column in the Data Warehouse database.
WHCF_XXXX tables consist of custom fields of all types. However, List type holds the actual value, and the Involved type holds the involved_id.
WHCC_XXXX tables consist of custom fields of Involved, List, and Date. Involved type holds the contact_id, List type holds the primary key of the lookup item, and Date type holds the date key.
WHCC_V_XXXX tables consist of custom fields of multi-select type and hold the tree_position of the multi-select type.
WHCF_V_XXXX tables consist of custom fields of multi-select type and hold the actual value of the multi-select type.
WH_CF_XXXX views consist of custom fields of all types except multi-select type.
WH_CF_V_XXXX views are constructed to include the logic to make the multi-select custom field vertical list into a comma-delimited list. For example, if the multi-select values for a record are listed as follow:
Record A Value1
Record A Value2
The comma-delimited value for Record A in the WH_CF_V_XXXX table would be “Value1, Value2”
The data type of that concatenated field is CLOB in Oracle and NTEXT in SQL Server to allow a multi-selection list that's longer than 2000 characters.
The first column in each custom field table will be an identification column. This column will contain the primary key of the object that is associated with the data in the custom fields. It will be named based on the object type that the custom field is related to. The following table describes the identity column names.
Field Table Identify Column Names
|
Object Type |
ID Column Name |
|---|---|
|
ACCOUNT |
ACCOUNT_ID |
|
APPOINTMENT |
APPOINTMENT_ID |
|
CONTACT |
CONTACT_ID |
|
DOCUMENT |
DOCUMENT_ID |
|
EXPENSE |
EXPENSE_ID |
|
HISTORY |
HISTORY_ID |
|
INVOICE |
INVOICE_ID |
|
INVOLVED |
INVOLVED_ID |
|
LINEITEM |
LINEITEM_ID |
|
MILESTONE |
MILESTONE_ID |
|
PROJECT |
PROJECT_ID |
| TASK | TASK_ID |
Each custom field is included in the warehouse when the Include in Reporting (or Include in Data Warehouse) box is checked in TeamConnect. Each custom field becomes a column in the custom field Data Warehouse table created for the respective category. These columns are added after the first identification column and are named after the custom field names (not labels). For example, if a custom field is named HowReported, the corresponding column in the Data Warehouse table is called HowReported.
Important: Data Warehouse has very strict requirements for custom field names.
The views that are associated with custom fields are:
- WH_INVOLVED_XXXX_CF_VW
- WH_MILESTONE_XXXX_CF_VW
- WH_PROJECT_XXXX_CF_VW
...where XXXX represents the unique code of the underlying object definition.
Note: Creating a custom field with a name in the format <EntityName>_ID [Example being INVOICE_ID on invoice object] will cause Data Warehouse to fail. This is due to these column names are what is used in the same tables as custom fields in Data Warehouse. This only occurs in Data Warehouse tables, so any field named this way will not be able to be added to the Data Warehouse.
Note: There may be other views present in the target database, with naming patterns such as WH_INVOLVED_XXXX_1_CF_VW, WH_INVOLVED_XXXX_2_CF_VW, etc. These are "building blocks" whose information is consolidated into one of the three main views named above (in this example, WH_INVOLVED_XXXX_CF_VW.) These building block views should not ordinarily be used for analysis and reporting.
WH_MAPPING_CF
WH_MAPPING_CF view contains the mapping between TeamConnect transactional tables to the Data Warehouse tables. These are custom fields and are dynamic. They are linked to Y_OBJ_DETAIL_FIELDS table.
|
Column Name |
NULL TYPE |
Oracle Data Type |
MSSQL Data Type |
|---|---|---|---|
|
TABLE_NAME |
NOT NULL |
VARCHAR2(30) |
VARCHAR(30) |
|
COLUMN_NAME |
NOT NULL |
VARCHAR2(30) |
VARCHAR(30) |
|
SOURCE_TABLE |
NOT NULL |
VARCHAR2(252) |
VARCHAR(252) |
|
SOURCE_COLUMN |
NOT NULL |
VARCHAR2(30) |
VARCHAR2(30) |