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.

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.

Back to top

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.

Back to top

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.

Back to top