When creating a value set, you must specify a validation type. If you select the validation type "Table", then you must define the table whose values comprise the value set.

Table value sets build queries that draw valid values from a table/view. For example, you have identified the table EMP as having a value column EMP_ID, and the ID column EMP_NAME. If a user searches for a person named "Smith", the query is directed to the table EMP. The search value entered is "Sm", so the actual search value used is "Sm%". The value set builds a search of the EMP table to find all employees whose names begin with "Sm". A list of values containing all employees whose last names begin with "Sm" is returned to the user. Then the user selects the correct employee, and that employee ID is the value that gets stored for the attribute.

   To identify a table for a value set:

1. In the Applications tree menu, click the "Setup Workbench" link.

2. On the Search: Catalog Categories page, click the Value Sets tab. Find and select the value set for which you want to enter validation table information.

3. In the Value Set Details page, Validation Table Information region, click Update.

4. On the Enter Validation Table Information page, enter the following information:

   Application Name

   The name of the application in which the table is located.

   Table Name

   The name of the database table or view in the schema.

5. In the Value Column section, enter the following information:

   Name

   The name of the column that stores the internal value.

   Type

   The data type of the value column.

   Size

   The size of the value column.

6. In the Meaning Column section, enter the following information:

   Name

   The name of the column that stores the description of the value.

   Type

   The data type of the meaning column.

   Size

   The size of the meaning column.

7. In the ID Column section, enter the following information:

   Name

   The name of the column that stores the display value.

   Type

   The data type of the ID column.

   Size

   The size of ID column.

8. In the "Where Clause" section, enter an additional Where clause to further constrain a query. For example, go back to the example above. Say you wish to further constrict the search results by only searching for current employees. In this case, you would add an additional Where clause defining the JOB_STATUS as ACTIVE.

   You can also use bind values in Where clauses in the following ways:

   • You can refer to other attributes in the same attribute group as the attribute that uses this value set by using the following syntax:

     :$ATTRIBUTEGROUP$.<your attribute's internal name>

     For example:

     (lookup_type='EGO_EF_Industry_TYPE' and instr(:$ATTRIBUTEGROUP$.Attr1 , tag) > 0 )

     Attention: In the example above, you must include a space between the comma and the word "tag" in order for the sql string to parse correctly.

   • You can refer to primary keys for the object to which the attribute group is associated by using the following syntax:

     :$OBJECT$.<the object's primary key column name>

9. Click Apply.