PeopleTools | Setting up a field prompt based on SQL text created dynamically
A. Basic vs Dynamic Prompts
If you’ve done a PeopleTools training course (or have equivalent experience), you will know that any field included on the page can be created with its own prompt table. Such a field will appear on the page with a magnifying glass symbol on the right-hand side:
By clicking the symbol, users can select their desired value from a predefined list. The prompt edit must be linked to a record, which does the job of filtering the data (if required). The prompt record is usually an SQL Table or SQL View. In the above example, the prompt record has been defined as COUNTRY_TBL, an SQL Table.
However, you may be faced with a requirement where the prompt record cannot be known in advance. For instance you may have a report run control page that works for both employees and customers. A single ‘ID’ field exists on the page, along with a radio button to specify ‘Employee’ or ‘Customer’. In this case, you would want a different prompt record depending on which radio button was selected. The master list of employees is stored in a different table to the master list of customers.
Fortunately, this requirement can be completed via the use of a dynamic SQL View.
B. Creating the Dynamic View
Firstly, create your record in App Designer as normal, setting the record type to ‘Dynamic View’. Because this record type does not require SQL text, it can be saved immediately once the fields have been configured. No record ‘build’ is required.
C. PeopleCode for Dynamic Views
Next, add code to the page to set the ‘real’ SQL text at run-time. This is usually added to a ‘RowInit’ or ‘FieldChange’ event (or often both). Using our country example, here is an example of setting the SQL text dynamically:
RECORD_NAME.COUNTRY.SqlText = "select country, descr from ps_country_tbl " |
"where country like 'T%'";
Then, when the user clicks the prompt on the page, the dynamic SQL automatically takes effect:
When the SQL text is set dynamically, the statement must contain the same number of fields as that of the record definition. For our Country dynamic view, we have specified two fields (COUNTRY and DESCR), so the SQL text must include those two fields. You will receive a run-time error if the field list is incomplete.
D. Optional SQL Text
Finally, one other feature of dynamic views is worth mentioning. You can still set up SQL text against the record definition as you normally would for an SQL view:
Again, there is no need to build the view, even with the SQL text entered. In fact the ‘Build -> Current Definition’ option is greyed out completely for dynamic views.
At run-time, if you don’t set the ‘SqlText’ property at all, the system will go back to the default text entered against the record definition. Therefore, this could be a useful option if you have a prompt that works in a certain way the majority of the time, but just needs a small modification now and again. You could set up the primary SQL Text in the dynamic view definition, and then override in PeopleCode should the situation arise.