Embed Query Results in Page - PeopleCode Friday #5

I recently had a requirement to include Query results in a component alongside other regular page objects.   I know that you can run an existing Query using PeopleCode I just needed a way to handle prompts and download to Excel etc.

I didn't have to look far for the answer.  PeopleSoft Query Manager  has a Run tab in which you can preview the results of your unsaved Query.    Behind the Run tab is a call to PeopleCode Functions ExecQuery and FormatResults.

Query Manager Run Tab.

Throw a Query object at these and hey presto.

Step 1 On the component page add the following 2 objects from QRY_VIEWER_WRK record.

• HTML Area . QRY_VIEWER_WRK.HTMLAREA - Used to display Query results,
• Text Box.  QRY_VIEWER_WRK.HTMLCTLEVENT - Used to drive operations like next/previous in list, download to Excel, etc.  This field should be invisible, modifiable by JavaScript and have a page fieldname of HTMLCTLEVENT

Step 2 Add the following PeopleCode (below) to some event. A button or page activate depending on how you want to control Query execution.  This code fragment is really just a starting point but should run any Public Query the user has permissions to run.  If the Query has prompts then a Prompt Dialogue will be generated when the ExecQuery function is called.

As always, feel free to leave comments, observations, alternative ideas or questions.

Page Definition for Displaying Query Results

Declare Function ExecQuery PeopleCode QRY_VIEWER_WRK.FUNCLIB FieldFormula;
Declare Function FormatResults PeopleCode QRY_VIEWER_WRK.FUNCLIB FieldFormula;

/* Create a new Query object*/
Local ApiObject &qryObject;

&qryObject = %Session.GetQuery();

/* Open and existing Query */
&qryObject.open("XRFWIN", True, True);

/* Run the Query */
Local Record &cPromptRec = Null;
&bResult = ExecQuery(&qryObject, &cPromptRec);

/* Display the HTML results */
QRY_VIEWER_WRK.HTMLAREA = FormatResults();

This is what the final page looks like