Hierarchical selection for auto-complete list validation

This topic provides details on how to enable hierarchical selection for auto-complete list validations.

Example of hierarchical selection

Hierarchical selection is the ability to list selectable field values in the form of a hierarchy or tree. If the field values can be defined hierarchically, the selectable field values can be presented to the user in a hierarchical list.

For example, when creating a resource pool (ResourcePool_1a), you can assign a parent resource pool. If ResourcePool_1 is set as the parent of ResoucePool_1a, Resource_Pool_1a appears as a child of ResourcePool_1 in the hierarchy.

Figure 2-1. Hierarchical selection example

Back to top

Enable hierarchical selection

Hierarchical selection must be enabled by the system administrator and is only available for auto-complete list validations.

To enable hierarchical selection:

  1. Log on to PPM.

  2. From the menu bar, select Open > Administration > Open Workbench.

  3. On the shortcut bar of the PPM Workbench, click Configuration > Validations.

  4. From the Validations Workbench, do the following:

    • If you are creating a new validation, click New Validation. Enter the required fields (Name and Reference Code). For Component Type, select Auto Complete List. For Validation By, select SQL - Custom.
    • If you are updating a validation, click List, and then open the target validation.

  5. Select the Enable Hierarchical Selection? checkbox. The Hierarchical Display tab is enabled.

    Note: If the Enable Hierarchical Selection? checkbox cannot be selected, that means hierarchical selection cannot be enabled for the validation.

  6. On the Hierarchical Display tab, set the following fields:

    Field

    Description

    Is folder selectable?

    In a hierarchical selection, a parent node can either be a container for selectable values or a selectable value.

    • Select the checkbox if the the parent node is a selectable value. This allows the parent node value to be chosen as a field value.
    • Clear the checkbox if the parent node is only a container or folder. This does not allow the parent node value to be selected as a field value.

    SQL

    Provide the Select statement that queries the database information. An ending semicolon is not necessary. See Hierarchical Display tab query example for more information.

  7. Enter any other information for the validation.

  8. Select OK.

If a field uses hierarchical selection, the hierarchical selection icon ( ) appears next to the field.

Back to top

Hierarchical Display tab query example

A basic SQL query used in the Hierarchical Display tab contains the following:

Select <PrimaryIDColumn> id,
<ParentIDColumn> parentId,
<FolderDefinition> isFolder,
<BusinessKeyColumn> name,
<DisplayInfo_1>, <DisplayInfo_2>, ... , <DisplayInfo_n>
from <Table_1>, <Table_2>, ... , <Table_n>
where <Condition_1> and <Condition_2> and ... and <Condition_n> and NVL(<ParentIDColumn>,0) = :parentId

where

<PrimaryIDColumn>

Required. Column name whose data type is numeric. Identifies the object that is displayed in the hierarchical selection list, such as a resource pool (rpl.resource_pool_id) or skill (sk.skill_id).

<ParentIDColumn>

Required. Column name whose data type is numeric and is a parent. Identifies the parent of the selected object such as a parent resource pool (rpl.parent_resource_pool_id) or parent skill (sk.parent_skill_id).
For detailed steps on how to create skills with hierarchical selection, see Hierarchical selection for auto-complete list validation.

<FolderDefinition>

Required. A column name (such as a flag) or sub-query that determines if the object is a folder. The value or result must be either `Y' or `N.'

An example of a column name is: sk.is_skill_folder_flag

An example of a sub-query is: decode(sign((select count(rpl2.resource_pool_id) from RSC_RESOURCE_POOLS rpl2 where rpl2.enabled_flag='Y' and rpl2.parent_resource_pool_id= rpl.resource_pool_id)),1,'Y','N')

<BusinessKeyColumn>

Required. The user-defined name of the object that is displayed in the hierarchical selection list.

<DisplayInfo_1>, ... , <DisplayInfo_n>

Optional. Additional information about the object to be displayed in the hierarchical selection list.

<Table_1>, ... , <Table_n>

Required. The name of the table from which to query. For example, RSC_RESOURCE_POOLS or RSC_SKILLS.

<Condition_1> and ... and <Condition_n>

Conditions to be met in order for the object to be selected to display in the hierarchical selection list.

At a minimum, you must include the following condition: NVL(<ParentIDColumn>,0) = :parentId

The following is an example of a hierarchical display query that could be used for a resource pool validation:

select rpl.resource_pool_id id,

rpl.parent_resource_pool_id parentId,

decode(sign((select count(rpl2.resource_pool_id) from
RSC_RESOURCE_POOLS rpl2 where rpl2.enabled_flag='Y' and rpl2.parent_resource_pool_id=rpl.resource_pool_id)),1,'Y','N') isFolder,

rpl.resource_pool_name name,

krsc_utils.get_pool_managers_list(rpl.resource_pool_id), rpl.description

from RSC_RESOURCE_POOLS rpl

where rpl.enabled_flag='Y' and
NVL(rpl.parent_resource_pool_id, 0) = :parentId