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

Comments

ChiDONEt said…
Good tip... Wich editor are you using? looks very nice (like sublime or atom).
Graham said…
@ChiDONEt Thanks! The editor I used was UltraEdit which has a nice syntax highlight feature. I don't edit code in there. I just use it to generate HTML for posting to the blog.
Unknown said…
Hi Graham,

I had a requirement like this and used this tip to show the query results on my Component page.
My query is simple select from record with input as prompts.

I gave a hyperlink on page and in the Field Change event the code shown above is used.

When I clicked on the link query prompts are shown up but I did not get the Run Tab of the Page.

Questions:
I have hidden the HTML area on my page. Is this causing the issue from showing up the Run Tab?
Did I miss Something? Could you please help?