How to dynamically populate data in a grid using SQL views

PeopleSoft Grids are powerful page controls in PeopleSoft. It can display information in a spreadsheet format and you can embed other page controls such as buttons, drop-down lists, html areas, etc.. to the grid. You use grids depending your table structure and how your page will display to the users based on user requirements. Grids are used in occur Level 1, 2 or 3 and it depends on whether you have more than one underlying record definition on a page.

Sometimes you want to pull information from multiple records and display in a grid on a page.

Since, a grid can associate with only one primary record, you have to create a view to get data from one or more records. Then you can associate the view record as the primary record in the grid.

However, imagine that you want to populate the grid dynamically passing parameters to the view.

Since, views cannot accept parameters dynamically (unlike SQL definitions or dynamic views) how do you handle this kind of situation. You can achieve that with the help of Select method in Rowset class. Select, reads data from the database tables or views into either a row or rowset object.

A good example from PeopleBooks
The following example selects into the child scroll of the level one rowset.  Each row fetched is placed under the appropriate row in &LEVEL1.
Note that instead of hard-coding the WHERE clause, the SQL repository is used to access a SQL definition named SELECT_WHERE.

&LEVEL1 = &LEVEL0()(1).GetRowset(SCROLL.EMPL_CHECKLIST);
&LEVEL1.Select(SCROLL.EMPL_CHKLST_ITM, RECORD.EMPL_CHKLST_ITM, SQL.SELECT_WHERE);

Another way you can achieve this, by creating a function to accept dynamic parameters (to WHERE clause) and then calling that function in appropriate event to populate the grid.
Note that here we use rowset Flush method to remove all rows from the rowset and free its associated buffer before selecting to the grid.
Flush method is often used to clear a work scroll before using the Select method.

rem Function to Refresh the Grid;
Function RefreshMyGrid(&Param1, &Param2,..,&ParamN)
   &rs = GetLevel0()(1).GetRowset(Scroll.MY_GRID_VW);
   &rs.Flush();
   &rs.Select(Record.MY_GRID_VW, "WHERE MYFIELD1 = :1 AND MYFIELD2=:2, MYFIELDN=:N", &Param1,&Param2,&ParamN);
End-Function;

Read More»

How to sort Grid Columns using PeopleCode

PeopleSoft Grids are most common form of data display control and they display fields in spreadsheet like format. Often, we need to provide custom sort for grid columns in various situations.
This can be achieved easily by using Sort Method in Rowset PeopleCode class.
However, there are few things to consider and we will look at it later in this post. First, how do we provide custom Sort in a grid.

Please place the following code in appropriate event to provide custom sorting of a grid;

/* Assuming you grid is in level 1*/
Local Rowset &rsLevel1 = GetLevel0()(1).GetRowset(Scroll.MY_GRID_REC);
&rsLevel1.Sort("MY_GRID_REC.COL1", "A", "MY_GRID_REC.COL2", "A", "MY_GRID_REC.COL3", "D");

Note that MY_GRID_REC.COL1, MY_GRID_REC.COL2, MY_GRID_REC.COL3 are your grid columns that you want to sort and MY_GRID_REC is the primary record of the grid.
"A" specifies ascending order; "D" specifies descending order.

If you are not auto selecting rows to populate in a grid you can achieve the same thing as below;

/*The first example repopulates a rowset in a page programmatically by first flushing its contents, 
selecting new contents using Select, then sorting the rows in ascending order by EXPORT_OBJECT_NAME*/
Function populate_rowset

   &RS1 = GetLevel0()(1).GetRowset(SCROLL.EXPORT_OBJECT);
   &RS1.Flush();
   &RS1.Select(RECORD.EXPORT_OBJECT, "where export_type =:EXPORT_TYPE_VW.EXPORT_TYPE");
   &RS1.Sort(EXPORT_OBJECT_NAME, "A");

End-Function;

Couple of Points to remember

• PeopleSoft Application Designer enables the user to personalize a grid at runtime. By default, grid personalization is enabled. If you provide custom sort using PeopleCode and if user personalize the grid at runtime (i.e online) then custom sort will not work afterwards;
• You can get back the custom sort you provided, by executing the same PeopleCode. To do that have to place a button on the page and allow user to click it to get back the custom sort on the grid.

Read More»

How to Enable and Disable Grid Rows using PeopleCode

Aprat from using EnableColumns(&Array)and SetProperties(&Array) 
Grid Class methods Here is another ways you can enable and disable PeopleSoft grid rows using PeopleCode.
Code snippt as follows;
/* assume Grid main Record is SOME_RECORD */

Local Rowset &RS = GetRowset(Scroll.SOME_RECORD);   For &i = 1 To &RS.ActiveRowCount      For &l = 1 To &RS(&i).GetRecord(Record.SOME_RECORD).FieldCount       &RS(&i).GetRecord(Record.SOME_RECORD).GetField(&l).Enabled = False;      End-For;   End-For;

More complete example would be

/* Declare a Rowset Object */ 

Local Rowset &level1; 

/* Traverse the Grid which is at Level 1 */ 

/* Level 0 always has a single row - so access it and Get the Scroll */ 

&level1 = GetLevel0().GetRow(1).GetRowset(Scroll.SOME_RECORD) ; 

/* To Remove "Add Row" or "Delete Row" Buttons at the end of all rows in the Grid */ 

&level1.DeleteEnabled = False; 

&level1.InsertEnabled = False; 

/* For a Single Row of Level 0 Data, There can be multiple Rows of Level 1 Data */ 

/* So, Looping through all rows in the Grid */ 

For &i = 1 To &level1.ActiveRowCount 

/* To Gray a particular field on all rows */ 

/* Depends on the requirement you can use Enabled property as well*/

/* This is for existing/unchanged/already-saved rows */

&level1.GetRow(&i).SOME_RECORD.SOME_FIELD.DisplayOnly = True; 

/ * For new rows in the grid */

If &level1.GetRow(&i).IsNew = False Then 

&level1.GetRow(&i).SOME_RECORD.SOME_NEW_FIELD.DisplayOnly = True; 

End-If; 

/* To Remove "Delete Row" Button at the end of particular rows in the Grid */ 

&level1.GetRow(&i).DeleteEnabled = False; 

End-For;

Read More»