Dynamic Prompts in PeopleSoft

Dynamic Prompts in PeopleSoft

1.   Introduction

When one enters a value in a field and proceeds entering data on the page by using the prompts (magnifying glass button) of the fields, one expects the data shown by the prompts to be in some kind of relationship with the values that are already entered. In order to have such a feature, which we can conveniently call as dynamically changing prompts, we need to change the prompt table from where the data is selected or we need to change the criteria of data selection dynamically.

 Here we present two ways of doing this. Firstly using the Derived table, this is a well reported concept so it is briefly discussed in here. Secondly using the dynamic views, this is discussed to the level of implementation. Later part of the document has an example with some snaps to help the understanding.

2.   Dynamic prompt requirement

Let’s deal with the requirement part with an example here.

Let us take two fields that are present on an online PeopleSoft page, field1 and field2. Let field1 denote the party budget of a person and field2 denote the hotels that he can afford with that budget. Now imagine after entering field1 user enters a value in field2 by clicking on the prompt button of field2. Basing on the value entered in field1 he should be able to see the list of hotels in field2’s prompt.

     How to provide this functionality?

a.    Using Derived.Edittable

This is a well reported way of solving this problem. In the OurRecord.Field2 properties enter %xxx as the prompt table name (with edit or without edit). Where ‘xxx ‘ can be an already existing field name on the record ‘Derived’ or one inserted by you for this purpose into it. Place the Derived.xxx field on the page that we were dealing with, so far, at the same level where the field2 is present. Now the simplest part of the process, write some peoplecode assigning a table name to the Derived.xxx.value in RowInit or FieldChange or any other relevant event of field1. This assigned table/view is expected to have the required prompt values in it.

Continuing with our example let us assume that Table_20 holds the names of hotels that people with budget of up to 20000 can visit and Table_60 holds the names of the hotels that people with budget of up to 60000 can visit.

So a simple indicative algorithm might look like this:

If OurRecord.Field1.value <= 20000 then Derived.xxx.value = ‘TABLE_20’

If OurRecord.Field1.value <= 60000 then Derived.xxx.value = ‘TABLE_60’

Let’s further imagine that with time the system got introduced to the users with 30000, 40000, 50000, 70000 budgets and the users expect the system to work in a similar fashion as it did previously i.e. only hotels within their budget level need to be listed.

Continuing with our methodology, we need to create tables or views (here Table_xx) with different groupings and assign them to the Derived.xxx.value.  This is a case where there are too many possible combinations of values, which would require too many views. Furthermore, the values are customizable by the end-user or the application, which means even if you, the developer, wanted to, you couldn't provide all the combinations of views necessary. However you can generate the desired SQL text for the view in People Code based on what the user enters. The number of conditional statements required will increase in proportion with the number of combinations. In cases where, the given Record.Field is a delivered combination and the number of pages where this combination occurs may not be small. Then the number of objects that get modified with such a change will be high.

b.   Using Dynamic view

In this methodology a Dynamic view should be created and assigned as the prompt table to the OurRecord.Field2. Let’s create a dynamic view with required fields (Field2, Hotel_Descr) and preferably have a generic SQL, select statement. Now use the sqltext property of the field2 to assign a dynamic SQL to the dynamic view. This people code can be written in field change or/and in any other relevant event of field1.

An indicative algorithm will look like this:

OurRecord.Field2.sqltext = “Select Field2, Hotel_Descr from PS_Hotel_Tbl Where Field1 < = “|OurRecord.Field1.value|”

Effectively if budget is 30000 then the statement should look like this:

‘Select Field2, Hotel_Descr from PS_Hotel_Tbl Where Field1 < = 30000’

Here the table PS_Hotel_Tbl is assumed to have the data of all Hotels.

Using this functionality we can go ahead and incorporate a lot of conditions in the select statement. Not all functionalities that are supported for an SQL select statement are allowed here. E.g. Order By.

3.   Points that can increase our speed

a.    Check whether the tools version supports the sqltext property.

b.    Remove deferred processing for the fields where the code is written. Here Field1.

c.    Effective date and Effective status should be considered while writing the SQL of the view.

d.    Use the same fields in select statement, which are present in the dynamic view.

e.    Use a MessageBox to see the SQL statement which is getting assigned in the peoplecode.

f.     If the field is on a level other than level0, upon row insert the sqltext used for the new row will be the one specified during the dynamic view’s creation. This can be altered in RowInit or RowInsert events if felt required.

4 . Real-time Example

In a case where, on journal entry page, user enters Deptid and expects to have Products related to the Deptid entered, in the Product field’s prompt.

1. Create a dynamic view with required fields in it.

 2. Place a SQL select statement in the view.

  3. Change the prompt of the jrnl_ln.product to this dynamic view.

4. Create a SQL statement according to the requirement and assign it to the sqltext of the product field, in some peoplecode event of jrnl.deptid field