Skip to main content
Mitratech Success Center

Custom Field Tables and Views

System and Custom objects can have custom fields associated with them. In order to support these custom fields, categories are created in TeamConnect. Each of these categories have an associated table in the warehouse to store custom field data. The table is identified by WHCF_XXXX and WHCC_XXXX, which work together to build the WH_CF_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.

WHCF_XXXX tables consist of custom fields of all types. However, List type holds the actual value, and Involved type hold 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.

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 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.

  • Was this article helpful?