- Multiple Fields in Possible Values Drop Down

Multiple Fields in Possible Values Drop Down

When creating a Possible Values operation, there are times where you may want the Possible Values Drop Down to include two or more fields to give the user more feedback and information. When creating a Possible Values operation, such as the one created, the first field selected is the value or field that is inserted into the database. The second field is the display value, which is what are going to change so that both department number and name are shown in the possible values drop down. In the possible value operation, we need to change the SQL code so that it adds the deptno field and the deptname field together as shown below:

Operation Code:

SELECT distinct deptno, (deptno || ' - ' || deptname) FROM pjdata.department

When accessing data from an iSeries, you should use the || operator to concatenate fields together for the display value. If you are using MySQL, then you need to use the CONCAT() function instead of the || command. In this case the operation code would look like this:

Operation Code:

SELECT DISTINCT deptno, CONCAT(deptno,CONCAT(' – ', deptname)) FROM pjdata.department

In the above example, the deptno is displayed with a dash and then the department name is shown:

When "B01 – Planning" is selected, the SQL statement that runs uses "B01" as the actual value: