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;