Cool Trick! Dynamic Prompt Table Using PeopleCode

Prompt table is used frequently in peoplesoft pages to eliminate human input error to the database. It is simple and easy to use for end users. Most of the prompt tables are displaying same results to all users from the underline table or view. However, there is time when you need to display different result based on a page/field condition, a previous field selection, or perhaps a different group of users. Can we do this?

YES! Have you heard of using dynamic view and sqltext in peoplecode? If you have not, then this maybe useful for you.

The cool thing about using a sqltext in peoplecode is to override the sql object in your view. First, you need to create a dynamic view that you will use as a prompt table. You may want to write a generic SQL in the view with all the fields you need to display in the search result.

Once you finished with creating dynamic view, then you can assign it as a prompt table edit. When the user click on the prompt table lookup from the page, it will display results from your generic SQL.

Now, in your peoplecode you can modify the generic SQL in the dynamic view by writing your own SQL with additional criteria using sqltext.

/* Update SQLText of the dynamic view */
RECORD_NAME.RECORD_FIELD.SqlText = "SELECT VAL1, VAL2 FROM PS_TABLE Where field_criteria = '" | &criteria_variable | "'";

Note:
1. Replace RECORD_NAME, RECORD_FIELD, VAL1, VAL2, PS_TABLE, field_criteria with your own
2. Assign criteria value to &criteria_variable

That's it, easy, huh!