The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Tuesday, 21 May 2013

Dynamic Prompts in PeopleSoft

In real business applications, very frequently we will encounter situations where the prompt for some fields needs to be controlled based on the values on other fields or based on the logged in user. PeopleSoft has delivered built-in mechanisms to handle dynamic prompting. Most of the readers will be already aware of this as this is one of the basic lessons in People Tools. However recently I had an interaction with PeopleSoft developer community, where most of them shared the same experience. People either don’t know dynamic prompting or they are not sure about the underlying technology to be used. I thought it will be worth explaining it over here so that people who are new to PeopleSoft may get a fair understanding on dynamic prompting.

There are widely two different techniques used for generating dynamic prompting. First method is using DERIVED record fields or popularly known as Edit Table method. The second popular method is using Dynamic Views. Both methods have its own advantages are used in different use cases.

Derived record field method

There can be business case where based on the value on one field, the prompt for other field should change. The change may include the search fields or list box values displayed on the prompt page. It can also demand an entirely different data set to be prompted for the user. In this type of scenarios the action that we require at the back end is to point the prompt of the fields to different tables or views. To tackle this kind of scenarios you need to use derived work record fields.

Suppose there is a scenario where you have a check box (called effective dated) and a department field on the page. If the check box is checked you need to show only all the effective dated departments on the prompt. Otherwise the prompt should list all the department codes and user should also have a search option to search with the effective date. This is an ideal scenario where you can use derived record field method. The steps to be followed are as below.

1.       Create a view, say DEPT_VW1, which will fetch the effective dated departments.
2.       Create another view, say DEPT_VW2, which will fetch all the departments. Make sure to check the alternate search key and list box item property for the EFFDT field (this is for the stated requirement. You may have to create both the views as per your real requirement).
3.       On the record field properties of the field department, give the prompt table name as %EDITTABLE (it is not mandatory to give %EDITTABLE all the time. You can give % and any field name present on the table DERIVED).
4.       Place the field EDITTABLE (or other fields which you have mentioned in the previous step) to the page where your department field is present. This is mandatory because we will be referencing the field in PeopleCode program in next step. To reference the field in PeopleCode, it is required that the work record field is present on the component buffer. You can hide this field, because it will not make any sense to the business user.
5.       Now on the field change event of the check box field, write the below code.
/* If it is checked then use first view else use second view */
/*Specify the prompt record name here.  Appropriate field name should be used in place of EDITTABLE */

This way you will be able to change the prompt record of a field programmatically. If you want to change the prompt of multiple fields in a page, you can use other fields such as EDITTABLE2 which are present in the record called DERIVED. The % symbol will signal the system that the prompt table for the field is the value present in the specified field in the record DERIVED.

Dynamic Views

Dynamic Views can prove to be powerful prompting mechanism when you come across a requirement where the values fetched by the prompt table needs to be changed based on some system variables such as %OperatorId or %EmployeeId. Take a situation where you want to filter the data present in the prompt table based on the logged in user. In this case you need to use dynamic views as a prompt table.

Take a situation where you have an address id as a field on the page. Your requirement will be to bring up the address id’s for the particular logged in user alone. But if a system administrator logs in, then you should display address id’s corresponding to all the users. In this case you have to use up dynamic views. The step should be followed will be as follows.

1.       Create a record (say PROMPT_DVW) and add necessary fields that need to be prompted.
2.       Set up the key structure in the way you would like the prompt page to be appeared.
3.       Save the record as dynamic view type. No need to build or write sql for these kinds of records.
4.       On the record field property of the address id field, set the dynamic view as the prompt table.
5.       Now on the appropriate event (RowInit, FieldChange etc) write the below code.
/* Note: ADDRESS_ID is the field which requires prompt. We are not writing anything on the dynamic view people code events. */
If %OperatorId = “Admin” Then
RECORD.ADDRESS_ID.SqlText = “select ADDRESS_ID, DESCR from PS_BASE_TABLE where EMPLOYEE_ID = ‘”|%EmployeeId|”’”;

The SqlText property will dynamically act as a view sql and bring up the corresponding result in the prompt page.

Complex Scenarios

There can be very complex scenarios where you need to change the prompt based on the logged in user or system variables and other multiple conditions based on selections user make in the page or setups. In such scenarios you can use a mix of both dynamic views and derived record fields.

These kind of scenarios are very rare in nature and I have seen only this once in my carrier.

If you have encountered/done any challenging or innovative method of creating dynamic prompts, please share it in the comments section so that others will get benefitted too.


  1. Thanks for listing out Dynamic Views and EditTable use cases. I've an interesting one: The client is asking for a scenario where we have to use dynamic SQLs as Prompt Tables for a few conditions and Record names stored in a Table as Prompt Tables for some(In the latter case basically we are looking at a scenario where the record name stored in a table is queried, unique row returned with the record name, which should be the prompt table for the record field.) I guess we have to use both the dynamic view and the edittable for achieving this. But the problem is at any one point of time I can use only one of them on the field. I tried passing SQL object or a record object populated with the concerned data as arguments to the edittable but obviously it is not working. Can you suggest anymore options to make this thing work?

    1. Hi,

      As per my understanding, your requirement is to have a prompt with dynamic sqls for some case and with random record names for some other cases. My suggestion would be to try a blend of both. Create a dynamic view which supports the basic structure of the prompt record and use edittable as the prompt table. Whenever you want to use dynamic sql as prompt, assign the edit table value as the dynamic view you have created. And assign the sql to the base field for which prompt is required as SQLText using the methods mentioned in the post. In the case where you have to assign record name as the prompt, directly assign the record name to the ediittable field. In case you encounter any issue the last resort would be to have two fields and hide one of them based on the criteria :) .

  2. This comment has been removed by the author.

  3. This comment has been removed by a blog administrator.