Share this page

# Hierarchical selection for auto-complete list validation

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

In this topic:

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

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

## 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`

https://admhelp.microfocus.com PPM 8942
Tips to improve your search results... OpenText™ Project and Portfolio Management Help Center PPM Help 26.2-26.3 All View Local Help Center View Online Help Center Version: