Dynamic view in PeopleSoft

Views are a useful feature of SQL databases, letting us create virtual tables based on SQL select statements.
PeopleSoft 8 provides the functionality to create dynamic views. These are essentially SQL statements executed on the fly by the PeopleSoft component processor. We can use dynamic views in Peoplesoft pages only because they are PeopleTools objects, not SQL Objects.

Using Dynamic view as Prompt Table

In this article, we will see the use of Dynamic view as a prompt table. Please note that a dynamic view can be used for other purposes such as search records which will not be discussed in the article.

One major question that pops up to many of us the first time we use a dynamic view, why not use a normal view instead of a dynamic view. A dynamic view’s select statement may include Peoplesoft’s meta-SQL, and it may be replaced by a different SQL statement while the user is using the page. And also there might be situations where which you do not save the view in the database.

Using Edit Table for prompt tables

Assume that you have a page in which the user selects a country and depending on the selection you want a different prompt table for the states. For example, if the user selects US show states for US and user selects Canada show states of Canada.

This can be achieved by following the steps below

Step 1: Create two views/dynamic views to show the states of the US and Canada.
Step 2: Go to record field, in our case it is state field (Right click and select View Definition on the state field on the page).
Step 3: Right Click and select Record Field Properties
Step 4: Select Edits tab and then select Table Edit. Select Prompt table with edit( or No Edit) as per your requirement. Write %EDITTABLE against Prompt table.*EDITTABLE is a field in table named DERIVED, provided by PeopleSoft.

Step 5: Place the EDITTABLE field from DERIVED Table on the page.
Step 6: On field change of Country Field, write the below code

if YOURRECORDNAME.COUNTRY = "US";
  DERIVED.EDITTABLE = "STATE_US_DVW"; // dynamic view of list of states of US
Else
  DERIVED.EDITTABLE = "STATE_CAN_DVW"; // dynamic view of list of states of CAN
End-if;

Using SQL Text

Instead of creating two views/dynamic views, you can also create one dynamic view and pass the SQL dynamically to change the prompt table.

Step 1: Create and save A Dynamic view
Step 2: Give the dynamic view as a prompt table to the state field.
Step 3: On field change of your country field you can write.

if YOURRECORDNAME.COUNTRY = "US";
  YOURRECORDNAME.FIELD.SqlText = "Select State from MY_STATE_TBL where country = 'US'";
ELSE
  YOURRECORDNAME.FIELD.SqlText = "Select State from MY_STATE_TBL where country = 'CAN'";
END-IF;

HAPPY LEARNING :)