Viewing System Lookup Tables

You can access system lookup tables from the main Designer screen to add, rename, inactivate, or delete items.

To open the system lookup tables screen

1. On main Designer screen, from the Go to drop-down list, select Lookup Tables.
   The System Lookup Tables screen appears, as shown in the following image.
   
2. Select the appropriate lookup table in the Show items belonging to drop-down list.
   
   If the lookup table has a hierarchical tree structure, another drop-down list appears, labeled Show items in node. If necessary, select the appropriate node in the Show items in node drop-down list to view the items that are listed within that item.
   
   The items that have been added to the lookup table (or to the selected item within the lookup table) appear.

The following table describes the items in the System Lookup Tables screen.

System Lookup Tables Screen

Lookup Table Structure

You may configure the display order of lookup table items and organize them in a hierarchical structure. As you add items to a lookup table, specify their tree positions so they are uniquely identified in the database.

Hierarchical Tree Structure

Many lookup tables consist of one level, so they appear as a flat list of items. However, lookup tables may also have a hierarchical structure with multiple sub-levels. These lookup tables also display in a drop-down list and appear in the end-user interface with headings and indents. The names of the sub-levels appear in their appropriate positions in the hierarchy, as shown in the following image.

Levels in the lookup table structure that have their own sub-levels are referred to as Nodes. The tree originates with the Root node, which is the first level and has the following properties:

• Cannot be deleted.
• Not displayed in the end-user interface. Thus, items directly below the Root node appear to end users as first level items.
• In both system and custom lookup tables, the tree position of the Root node is the predefined unique code of the table. For a list of system table unique codes, see the System Lookup Table Information table.

When adding countries and states that you want to appear in the State/Province drop-down lists in the Contact Address block, the following applies:

• If you want a list of states to appear for a country selected in the contact address, be sure that the country is added to the state table with same unique code as the country table and there are states defined as child items of the country item.
• When adding a new country to the Country lookup table, you must also add the new country and its unique code to the state table, and then, if necessary, add the states as child items of the country.

Tree Positions

All items in lookup tables are assigned a unique key called the tree position. In the Designer user interface, the tree position appears as four alphanumeric characters in uppercase, such as BUS1.

When you add items to a lookup table, you specify their tree positions so they can be uniquely identified in the database.

In hierarchical tables, each item in a lookup table is identified by the path or "tree" that leads to it within the table's hierarchy. This tree originates with tree position of the Root and continues to the tree position of an item in the hierarchy.

The full tree position of a lookup table item is a combination of tree positions assigned to the item and all items above it in the hierarchy, starting with the Root. Each level is separated by underscores.

For example, if an item with tree position CCCC is in the second level node of a lookup table item with the tree position BBBB and the Root's tree position is AAAA, the full tree position of the item is AAAA_BBBB_CCCC.

Order

When you add lookup table items, you can specify an integer to determine the order in which the item appears with respect to the other items in the same hierarchical level (belonging to the same node). Lookup table items with the same order appear alphabetically.

For example, in the following image, the Order for Cost Center might be 5, because it is the fifth item in its level. Time Period might have an Order of 1 because it is the first (and only) item in its level.

Adding Lookup Table Items

You can add items to system lookup tables to meet your organization's needs. For example, you could add the is court of item to the Contact Relation Type lookup table to support relations to contact records of courts.

To add an item to a system lookup table

1. On the System Lookup Tables screen, select the appropriate lookup table in the Show items belonging to drop-down list.
   If the lookup table has a hierarchical tree structure, the Show items in node drop-down list appears. Select the appropriate node in the Show items in node drop-down list.
2. Select the Number of entries you would like to add from the corresponding drop-down list.
3. For each data entry row, fill in the appropriate fields as described in the System Lookup Tables Screen table, such as its unique tree position.
4. Click add more.
   
   The newly added item appears in the lookup item list.

Editing System Lookup Tables

You can edit system lookup table contents as needed by modifying the name or order of items. For example, you could change the name of the Lap Top item in the Resource Type lookup table to Computer and change its position in the list so that it appears next to the Projector item.

You cannot edit the tree position of an item, but you can add a new item and inactivate or delete the old item.

If renaming the item changes the item's meaning, you should only rename it on a newly installed system. Otherwise, existing data captured using the tree position of the lookup table item will effectively be altered.

To edit a system lookup table item

1. On the System Lookup Tables screen, select the appropriate lookup table in the Show items belonging to drop-down list.
   If the lookup table has a hierarchical tree structure, select the appropriate node in the Show items in node drop-down list.
2. Select the check-box next to each item you would like to change.
3. Click edit.
   The corresponding fields of the selected item or items become editable.
4. Make the necessary changes to the Item Name and Order of the selected items.
5. Click ok.
   Your changes are reflected in the lookup item list.

Inactivating Lookup Table Items

You can inactivate lookup table items so that users are prevented from selecting them from the corresponding fields in the user interface. Inactivating items allows you to prevent the use of unwanted values yet maintain existing data in records that include inactive items.

Important: Before inactivating lookup table items, read Points To Remember.

To inactivate lookup table items

1. On the System Lookup Tables screen, select the appropriate lookup table in the Show items belonging to drop-down list.
   If the lookup table has a hierarchical tree structure, select the appropriate node in the Show items in node drop-down list.
2. Select the check-box next to each item you would like to inactivate.
3. Click inactivate.
   If the items do not have any child lookup table items, the selected items are inactivated, users cannot select them in any fields, and you are finished with this procedure.
   
   If any of the selected items have at least one child lookup table item, a message box with the following question appears:
   Are you sure you want to inactivate these items?
4. Do one of the following actions:
   1. If you want to inactivate the items and all of their child items, select the check-box next to Include all child items and click ok.
   2. If you want to inactivate just the items within the selected node but not any of the child items, click ok.
   3. If you decide not to inactivate any items, click cancel.
      Inactivated items are indicated in the Active column of the lookup item list.

Important: Record the status of all inactive items so that this information is available if you ever need to convert your TeamConnect data.

Points To Remember

Consider the following points before inactivating lookup table items:

• If an inactive value is assigned to a field via templates, rules, or the XML layer, an exception will be thrown to prevent the creation or update of the record. For example, if a template specifies an item as the default value of a field to be set on Create, but the item is inactivated, the record cannot be created.
• You cannot inactivate lookup table items used by required custom fields or custom fields that set a default value. If you attempt to do so, you will receive an error.
• Inactivated items are visible to end users in records if they were previously added to a record. Depending on the circumstances, they may be displayed in gray. If accessibility settings are enabled, (inactive) is appended to inactive item names and they are not displayed in gray.
• Users can specify inactivated items as search criteria and report qualifiers. Inactivated items appear in gray or (inactive) is appended to the item names.
• If a user changes the value of a field that specified an inactive item and then clicks save, they cannot revert to the prior inactive value unless a TeamConnect solution developer temporarily activates the item.· Existing records with inactive items retain the inactive values. Users can edit records with previously selected inactivated items and click save.
• If you ever need to convert your TeamConnect data, you must first activate all inactive categories and lookup table items. Once the data conversion is completed, you can restore the inactive items manually.
• When you inactivate lookup table items, their status is recorded in the Active column of the lookup table screen.
• If an item has parent or child items, you cannot readily determine their active status without navigating to each level in the hierarchy. Therefore, you should record the status of all inactive items so that this information is readily available if you ever need to convert your TeamConnect data. Otherwise, you will have to manually search for any inactive items.

Activating Lookup Table Items

You can activate previously inactivated lookup table items so that users can select them from the corresponding fields in the user interface.

To activate lookup table items

1. On the System Lookup Tables screen, select the appropriate lookup table in the Show items belonging to drop-down list.
   If the lookup table has a hierarchical tree structure, select the appropriate node in the Show items in node drop-down list.
2. Select the check-box next to each item you would like to activate.
3. Click activate.
   If the items do not have any child lookup table items, the selected items are activated, users can select them from corresponding fields, and you are finished with this procedure.
   If any of the selected items have at least one child lookup table item, a message box with the following question appears:
   Are you sure you want to activate these items?
4. Do one of the following actions:
   1. If you want to activate the items and all of their child items, select the check-box next to Include all child items and click ok.
   2. If you want to activate just the items within the selected node but not any of the child items, click ok.
   3. If you decide not to activate any items, click cancel.
      Activated items are indicated in the Active column of the lookup item list.

Important: Update the status of all activated lookup table items so that this information is available if you ever need to do any k ind of conversion of your TeamConnect data.

Deleting Lookup Table Items

System lookup tables themselves cannot be deleted. However, you can delete items in a system lookup table if they are not needed. TeamConnect automatically checks to make sure that each item is not being referenced by records. If an item is being used, you are prompted to confirm its deletion.

You must consider the impact of deleting lookup table items—especially on an existing system that has records using the item. If the tree position of the item is used by rules or reports, you must consider the impact of deleting it and make any necessary modifications. You may be able to inactivate or rename the item rather than delete it.

Important: If you delete an item, all record data referencing it is lost. If you delete a node, all of its sub-levels and their data entries are also removed.

To delete an item from a system lookup table

1. On the System Lookup Tables screen, select the appropriate lookup table in the Show items belonging to drop-down list.
   If the lookup table has a hierarchical tree structure, another drop-down list appears, Show items in node. Select the appropriate node in the Show items in node drop-down list.
2. Select the check-box next to each item in the list that you would like to delete.
3. Click delete.
   The items you deleted are immediately removed from the lookup item list and all screens where the lookup table is accessible in the end-user interface.

Viewing Custom Lookup Tables

The Custom Lookup Tables screen allows you to view existing custom lookup tables. If you need to create new ones, use this procedure to access the screen, and then see Adding Custom Lookup Tables.

To open the custom lookup tables screen

1. In the Designer window, in the Go to drop-down list, select Lookup Tables.
2. Click the Custom tab.
   The Custom Lookup Tables screen appears.
3. Select the appropriate user-defined lookup table in the Show items belonging to drop-down list.
4. Select the appropriate node in the in node drop-down list, if necessary.
   A list of items available for the selected lookup table or its selected node appears below the drop- down lists.

The following table describes the items in the custom lookup tables screen.

Custom Lookup Tables Screen

Adding Custom Lookup Tables

You can add as many custom lookup tables for custom fields of type List as you need. Remember, however, that you can re-use the same table for multiple custom fields, for example, if you need to add a field with the same options under different categories or even in different object definitions.

Tip: Always make sure that the names you assign to your custom tables are self-explanatory and indicative of the items they contain or for which fields they are created.

To add a new custom lookup table

1. On the Custom Lookup Tables screen, in the New lookup table name field, enter a name for the table.
   The table name can be 50 characters maximum in length.
2. In the Unique Code field, type four alphanumeric characters for a unique identifier for the table.
3. Click Add Table.
   The newly added table appears alphabetically in the Show items belonging to drop-down list.
4. Add the desired items to the table. For details, see Adding Items to Custom Lookup Tables. The custom lookup table is created and available for use in custom fields of type List.

Deleting Custom Lookup Tables

You can delete custom lookup tables that are no longer necessary for custom fields. However, you will not be able to delete a custom lookup table if it is used by a custom field or if any of its items is used in a project auto naming pattern in custom object definitions.

To delete a custom lookup table

1. On the Custom Lookup Tables screen, select the custom lookup table you would like to delete in the Show items belonging to drop-down list.
2. Click Delete Table.
3. When asked to confirm your action, click OK.
   If the table is already being used in records, you will receive the appropriate message, otherwise the table with all of its items is deleted from the database.

Adding Items to Custom Lookup Tables

You need to add items to custom lookup tables to maintain the appropriate list of selections for your users, according to your business needs.

To add new items to a custom lookup table

1. On the Custom Lookup Tables screen, in the Show items belonging to drop-down list, select the custom lookup table to which you need to add items.
2. In the in node drop-down list, select the appropriate level where the items should be.
3. Select the Number of entries you would like to add.
4. For each data entry row, enter the following information:
   • Name of the item as it should be displayed in the field.
   • Its display order.
   • A four-character alphanumeric combination that is unique in the selected node.
   • If necessary, for more specific details, see the Custom Lookup Tables Screen table.
5. Click add more.
   The newly added item appears in the lookup item list.

Editing Custom Lookup Table Items

You can edit custom lookup table contents when you want to display them in a different order in the user interface, or if you want to change the name that appears for each item.

You cannot edit the tree position of a lookup item. During the design stage, when no records have been created yet, you can add a new item and then delete the item that has the incorrect tree position.

To edit an item in a custom lookup table

1. On the Custom Lookup Tables screen, in the Show items belonging to drop-down list, select the custom lookup table where you need to edit items.
2. In the In Node drop-down list, select the appropriate level where you want to make changes.
3. Select the check-box next to each item you need to change.
4. Click Edit.
   The corresponding fields of the selected items become editable.
5. Make the necessary changes to the Item Name and Order of the selected items.
6. Click OK.
   Your changes are reflected in the lookup item list.

Inactivating Custom Lookup Table Items

You can inactivate lookup table items so that users are prevented from selecting them from the corresponding fields in the user interface. Inactivating items allows you to prevent the use of unwanted values yet maintain existing data in records that include inactive items.

Important: Before inactivating lookup table items, read Points To Remember.

To inactivate lookup table items

1. On the Custom Lookup Tables screen, select the appropriate lookup table in the Show items belonging to drop-down list.
   If the lookup table has a hierarchical tree structure, select the appropriate node in the Show items in node drop-down list.
2. Select the check-box next to each item you would like to inactivate.
3. Click Inactivate.
   If the items do not have any child lookup table items, the selected items are inactivated, users cannot select them in any fields, and you are finished with this procedure.
   If any of the selected items have at least one child lookup table item, a message box with the following question appears:
   Are you sure you want to inactivate this item?
4. Do one of the following actions:
   1. If you want to inactivate the items and all of their child items, select the check-box next to Include all child items and click ok.
   2. If you want to inactivate just the items within the selected node but not any of the child items, clear the check-box next to Include all child items and click ok.
   3. If you decide not to inactivate any items, click Cancel.
      Inactivated items are indicated in the Active column of the lookup item list.

Important: Record the status of all inactive items so that this information is available if you ever need to convert your TeamConnect data.

Points To Remember

Consider the following points before inactivating lookup table items:

• If an inactive value is assigned to a field via templates, rules, or the XML layer, an exception will be thrown to prevent the creation or update of the record. For example, if a template specifies an item as the default value of a field to be set on Create, but the item is inactivated, the record cannot be created.
• You cannot inactivate lookup table items used by required custom fields or custom fields that set a default value. If you attempt to do so, you will receive an error.
• Inactivated items are visible to end users in records if they were previously added to a record. Depending on the circumstances, they may be displayed in gray. If accessibility settings are enabled, (inactive) is appended to inactive item names and they are not displayed in gray.
• Users can specify inactivated items as search criteria and report qualifiers. Inactivated items appear in gray or (inactive) is appended to the item names.
• Existing records with inactive items retain the inactive values. Users can edit records with previously selected inactivated items and click save.
• If a user changes the value of a field that specified an inactive item and then clicks save, they cannot revert to the prior inactive value unless a TeamConnect solution developer temporarily activates the item.
• If you ever need to convert your TeamConnect data, you must first activate all inactive categories and lookup table items. Once the data conversion is completed, you can restore the inactive items manually.
• When you inactivate lookup table items, their status is recorded in the Active column of the lookup table screen.

If an item has parent or child items, you cannot readily determine their active status without navigating to each level in the hierarchy. Therefore, you should record the status of all inactive items so that this information is readily available if you ever need to convert your TeamConnect data. Otherwise, you will have to manually search for any inactive items.

Activating Custom Lookup Table Items

You can activate previously inactivated lookup table items so that users can select them from the corresponding fields in the user interface.

To activate lookup table items

1. On the Custom Lookup Tables screen, select the appropriate lookup table in the Show items belonging to drop-down list.
   If the lookup table has a hierarchical tree structure, select the appropriate node in the Show items in node drop-down list.
2. Select the check-box next to each item you want to activate.
3. Click activate.
   If the items do not have any child lookup table items, the selected items are activated, users can select them from corresponding fields, and you are finished with this procedure.
   If any of the selected items has at least one child lookup table item, a message box with the following question appears:
   Are you sure you want to activate this item?
4. Do one of the following actions:
   1. If you want to activate the items and all of their child items, select the check-box next to Include all child items and click ok.
   2. If you want to activate just the items within the selected node but not any of the child items, clear the check-box next to Include all child items and click ok.
   3. If you decide not to activate any items, click cancel.
      Activated items are indicated in the Active column of the lookup item list.

Important: Update the status of all activated lookup table items so that this information is available if you ever need to convert your TeamConnect data.

Deleting Custom Lookup Table Items

You can delete items in a custom lookup table when they are no longer needed. For example, in the design stage of implementation, you might need to clean up the contents of a custom lookup table to have the associated field display the appropriate selections.

TeamConnect automatically checks to make sure that each lookup item is not being used in records. If an item is being used as data in a record, you are notified and asked to confirm your decision to delete it.

Caution: Deleting a lookup item also deletes the item's data entries in records, all of its child items, and the child items' data entries.

To delete an item from a custom lookup table

1. On the Custom Lookup Tables screen, in the Show items belonging to drop-down list, select the custom lookup table where you need to delete items.
2. In the in node drop-down list, select the appropriate level where you want to make changes.
3. Select the check-box next to each item you want to delete.
4. Click delete.
5. When asked to confirm your action, click OK.
6. If the item or one of its child items is being used as data in records, another confirmation is required. Click OK.
   The item and all of its children, if there are any, are deleted along with their record entries.