I'm speaking at OpenWorld 2017

Friday, 19 February 2016

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

3 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.

John Nash said...

Thanks, a lot for sharing such a useful information and tutorials on PeopleSoft

PeopleSoft HRMS Technical Online Training

PeopleSoft HRMS Functional Online Training

PeopleSoft Financial Management Online Training

PeopleSoft Admin Online Training

PeopleSoft DBA Online Training

PeopleSoft CRM Online Training