Share this page

# Configure SQL validations

You can use an SQL statement to generate the values in a validation. SQL can be used as a validation method for drop-down lists, auto-complete fields and check box components.

Tip: Check if an existing validation meets your process requirements; if so, use the validation in your process. Alternatively, copy and modify validations that are similar to your desired one. For a list of validations that are delivered with the product, see View system validations.

In this topic:

## SQL validated Drop Down List/Check Box

To define a dynamic list for a drop down list or check box field:

1. Open a new or existing validation, see Configure validations.

2. In the Component Type field, select Drop Down List or Check Box.

   Note: The Check Box option is available only when the Enable Checkbox List Validation feature toggle is turned on. For details, see Use feature toggles to turn on/off features.

   The fields in the Validation window change dynamically, depending on your selection.

3. In the Validated By field, select SQL.

4. In the SQL field, provide the Select statement that queries the necessary database information. An ending semicolon is not necessary.

5. To add token to your SQL statement, click Tokens, select the token text that you want, copy the text, close the Tokens window, and paste the text into the SQL statement where it is needed.

## SQL validated auto-complete Lists

Auto-completed lists can be validated by the following SQL statements:

Category | Description
--- | ---
Custom auto-completes or validations (Validated by: SQL-Custom) | Can be configured for short or long list formats
User auto-completes or validations (Validated by: SQL-User) | Contains the following default filter fields: Primary field. This field takes the name of the auto-complete field First name Last name The user auto-completes always appear in the long list format, which use the paging interface to display the items. Additionally, user auto-completes display a different icon.

To configure a dynamic list for an auto-complete list field:

1. Open a new or existing validation, see Configure validations.

2. In the Component Type field, select Auto Complete List.

   The fields in the Validation window change dynamically, depending on your selection.

3. In the Validated By field, select SQL - Custom or SQL - User.

4. For SQL - Custom, Selection mode, select one of the following:

   Selection | Description
   --- | ---
   Starts with | Type characters and if the list is not automatically filtered, then type the Tab key. The selection window opens and lists entries that begin with the specified characters.
   Contains | Type characters, and if the list is not automatically filtered, then type the Tab key. The selection window opens and lists entries that contain the specified character string. This is the same behavior as a wild card search, which uses the % character at the beginning of the search text.

   This setting only controls the matching on the Select page. Matching in the auto-complete field is controlled by including specific clauses in the auto-complete's SQL.

5. For SQL - Custom, in the Expected list length field, select one of the following:

   Selection | Description
   --- | ---
   Short | Auto-completes configured as short lists load all values when the window is opened. This can adversely affect performance.
   Long | Auto-completes configured as long lists load a limited set of values when the window is opened. By default, 50 results are shown per page. End users can page through the results or further limit the results by specifying text in one of the available filter fields at the top of the page.

6. If you selected a long list, in the Number of results per page, indicate the number of results you want displayed on each page.

7. On the Configuration tab, in the SQL field, provide the Select statement that queries the necessary database information. An ending semicolon is not necessary.

   For example, XYZ Corporation creates an auto-complete field that lists all users in the Engineering department and is validated by SQL.

       SELECT U.USER_ID, U.USERNAME, U.FIRST_NAME, U.LAST_NAME
       FROM KNTA_USERS U, KNTA_SECURITY_GROUPS SG, KNTA_USER_
       SECURITY US
       WHERE SG.SECURITY_GROUP_ID = US.SECURITY_GROUP_ID AND
       US.USER_ID = U.USER_ID
       AND SG.SECURITY_GROUP_NAME = 'Engineering'
       and UPPER(u.username) like UPPER('?%')
       and (u.username like upper(substr('?',1,1)) || '%'
       or u.username like lower(substr('?',1,1)) || '%')
       order by 2

   After a new user account is created and added to the Engineering security group, that user is automatically included in the auto-complete. If you are using an auto-complete component, you can define headers for the selected columns. These column headers are used in the window that opens if a value from an auto-complete is selected.

   Note: If you use the same token in both the WHERE clause of the SQL statement and the SQL statement of the validation's filter field, OpenText PPM may return no value for the ACL field when you change the value of the field associated with the token in OpenText PPM Web pages.

   This is because the value of the token used in the WHERE clause of the ACL validation's SQL statement is retrieved from the Web pages, while the value of the token used in the SQL statement of the validation's filter field is retrieved from OpenText PPM database.

   To avoid this issue, we recommend that you not use the same token in the WHERE clause of an ACL validation's SQL statement and the SQL statement of the validation's filter field.

8. To configure "starts with" matching from the auto-complete window to the selection window, add the following to the SQL WHERE clause:

       UPPER(value) like UPPER('?%') and (value like
       upper(substr('?',1,1)) || '%' or value like
       lower(substr('?',1,1)) || '%')

9. To configure "contains" matching from the auto-complete window to the selection window, add the following to the SQL WHERE clause:

       UPPER(value) like UPPER('%?%') and (value like '%' ||
       upper(substr('?',1,1)) || '%' or value like '%' ||
       lower(substr('?',1,1)) || '%')

10. Under the Column Headers table, click New.

11. Specify values for the following:

    Field Name | Description
    --- | ---
    Column Header | Column name to display in the auto-complete window.
    Display | Determines whether or not the column is visible. The first column is never visible and the second column is always visible.

12. For short lists, you are done, click Save.

13. For long lists and for SQL - User, you can continue with Add search fields to auto-complete validations.

## New drop-down and auto-complete lists

PPM enhances the functionality of the drop-down and auto-complete lists on these pages: the request details page, project details page, and program details page.

### Enhanced functionality

The enhanced drop-down and auto-complete lists have the following functionality:

List type | Enhanced functionality
--- | ---
Drop-down list | Displays the first 50 values when the list field contains more than 50 values. You can type to search for the target values.
Auto-complete list | Appears like drop-down list, displaying the first 50 values when the list field contains more than 50 values. You can type to search for the target values. The new functionality applies to auto-complete lists: Validated by List, SQL-Custom or SQL-User With no filter fields With no dependencies defined (have no tokens used in the SQL) Whose validation results contain only one column

### Use new drop-down and auto-complete lists

For version 24.3 and later:

1. Turn on the Use New Drop-Down and Auto-Complete Lists feature toggle.
2. To use the enhanced auto-complete list, turn on the Use New Auto-Complete Lists sub-feature toggle.
3. To use the enhanced drop-down list, turn on the Use New Drop-Down sub-feature toggle.

For versions 24.1-24.2:

- Turn on the Use New Drop-Down and Auto-Complete Lists feature toggle to use both new drop-down and auto-complete lists.

For details, see Use feature toggles to turn on/off features.

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: