PeopleSoft Grid PeopleCode

Posted by in PeopleCode | 22 Comments

PeopleSoft Grid PeopleCodeIn this article we would see how to populate a Grid dynamically using PeopleCode. Here we use a Dynamic View SGK_VCHR_DVW as the main record of the Grid.

The grid is placed on level 1 of a secondary page and is populated using Peoplecode written in the Activate event of the secondary page. We use the SQL object &VCHRS_GRD_SQL to fetch some Voucher IDs and Vendor IDs from the database and populate the grid with these values.

Local SQL &VCHRS_GRD_SQL;
/* SQL object for fetching the vouchers and vendors*/
Local Rowset &VCHRS_GRD_RS;
/*Rowset for accessing the Grid*/
 
&VCHRS_GRD_SQL = CreateSQL("SELECT V.VOUCHER_ID, V.VENDOR_ID FROM PS_VOUCHER V WHERE V.BUSINESS_UNIT =:1 AND V.ACCOUNTING_DT > :2", &SGK_BU, &SGK_ACCTG_DT);
/*creating the SQL statement*/
 
&VCHRS_GRD_RS = GetLevel0()(1).GetRowset(Scroll.SGK_VCHR_DVW);
/*creating a rowset corresponding to the grid */
 
While &VCHRS_GRD_SQL.Fetch(&GRD_VOUCHER_ID, &GRD_VENDOR_ID)
&VCHRS_GRD_RS(1).SGK_VCHR_DVW.VOUCHER_ID.Value = &GRD_VOUCHER_ID;
/*assigning the voucher ID to the filed in the grid */
&VCHRS_GRD_RS(1).SGK_VCHR_DVW.VENDOR_ID.Value = &GRD_VENDOR_ID;
/*assigning the Vendor ID to the filed in the grid */
&VCHRS_GRD_RS.InsertRow(0);
/* inserting a new row at the beginning of the grid*/
End-While;
 
&VCHRS_GRD_RS.DeleteRow(1);
/* deleting the empty row left after all the insertions */
&VCHRS_GRD_SQL.Close();
/* closing the SQL object */
Tags: 
  1. Dropdown Values from Peoplecode
    Adding Dropdown Values from PeopleCode
  2. Optional Prompting in PS Query
  3. PeopleSoft Standalone Rowsets
    PeopleSoft Standalone Rowset
Subscribe to Comments RSS Feed in this post
22 Responses
  1. Surya #

    I tried the above code I get the “SQL error has occured” . When I place grid at level 1 and give this code in Level 0 Record Field – Field change i get this error. My requirement is I have 2 fields at Level 0 . When I key in one field in level 0 , the grid should populate dynamically based on the keyed in value. Can you please suggest.

    Reply
    • Rakesh Subhagan #

      Can you try running the SQL in an SQL execution tool like Toad and check if it fetches the required rows?

      Reply
      • Rakesh # 
        &userid = SHP_EMPL_TBL.SHP_EMPLID_SS.Value;
 
Local Rowset &VCHRS_GRD_RS;
Local SQL &sql = CreateSQL(“SELECT a.shp_deptid_ss , a.shp_description_ss , a.shp_mgrid_ss , b.shp_jobtitle_ss FROM ps_shp_deptinf_tbl a ,ps_shp_deptemp_ss b WHERE a.shp_deptid_ss =b.shp_deptid_ss AND a.eff_status=’A’ AND b.shp_emplid_ss= ‘” | &userid | “‘”);
 
&VCHRS_GRD_RS = GetLevel0()(1).GetRowset(Scroll.SHP_NEWR_PS);
While &sql.Fetch(&deptid, &dpt, &mgr, &jobtitle)
 
&VCHRS_GRD_RS(1).SHP_NEWR_PS.SHP_DEPTID_SS.Value = &deptid;
&VCHRS_GRD_RS(1).SHP_NEWR_PS.SHP_DESCRIPTION_SS.Value = &dpt;
&VCHRS_GRD_RS(1).SHP_NEWR_PS.SHP_MGRID_SS.Value = &mgr;
&VCHRS_GRD_RS(1).SHP_NEWR_PS.SHP_JOBTITLE_SS.Value = &jobtitle;
&VCHRS_GRD_RS.InsertRow(0);
End-While;
&VCHRS_GRD_RS.DeleteRow(1);
 
&sql.Close();
        Reply
        • Sowmya #

          I have tried this logic in my code but it it inserting same date in all records. Please help me with this.
          Here is my code
          &sql = CreateSQL(“SELECT A.TAX_AUTHORITY_CD, A.TAX_PCT FROM PS_TAX_AUTHORITY A, PS_TAX_CD B WHERE A.TAX_AUTHORITY_CD = B.TAX_AUTHORITY_CD AND B.TAX_CD = :1”, HCC_BI_HDR.TAX_CD, &TAX_AUTHORITY_CD, &TAX_PCT);
          &RS = GetLevel0()(1).GetRowset(Scroll.HCC_BHDR_TAX);

          While &sql.Fetch(&TAX_AUTHORITY_CD, &TAX_PCT)
          &RS(1).HCC_BHDR_TAX.TAX_AUTHORITY_CD.Value = &TAX_AUTHORITY_CD;
          &RS(1).HCC_BHDR_TAX.TAX_PCT.Value = &TAX_PCT;
          &RS.InsertRow(0);
          End-While;

          &RS.DeleteRow(1);

          where i need to reterive multiple data in a grid.

          Reply
          • Rakesh Subhagan #

            Hi Sowmya,

            Have you tried running the query outside of PeopleSoft? Is it giving the desired output?

            SELECT A.TAX_AUTHORITY_CD, A.TAX_PCT FROM PS_TAX_AUTHORITY A, PS_TAX_CD B WHERE A.TAX_AUTHORITY_CD = B.TAX_AUTHORITY_CD AND B.TAX_CD =

  2. Surya #

    If I give theSQL text for the Dynamic View as blank it throws me the error “SQLL text is invlaid for the Dynamic view”

    Reply
  3. Mustafa #

    thank you,its very helpfull…

    Reply
  4. santhosh #

    Hi,

    Thanks for your post. It helped me a lot in understanding the grid peoplecode.

    I am new to peoplesoft. I have implemented the same code on a sample page and was able to populate the grid.

    Can you help me in the below problem which i am facing.
    In my database value of a field is ”AT&T” for example. It is displaying as AT&T in the PIA page.

    Thanks for your help in advance.
    Santhosh

    Reply
    • Rakesh Subhagan #

      Hi Santhosh,

      Nice the know that the post was helpful.

      Regarding your issue, I couldn’t understand what the problem is. Can you please elaborate?

      Reply
  5. Sadeesh #

    Hi,

    I have a requirement like there is page with a grid / scroll and with a button. The user would be able to enter the rows in the grid and when the user clicks the button, the requirement is to insert into the same number of rows as the user enters in the grid. To be in detail, consider the following scenario –

    Rows – SETID DESCR LONG DESCR EFFDT
    Row1 GLOBL TEST TEST1234 SYSDATE
    Row2 310P1 TEST2 TEST12345 SYSDATE
    Row3 310P3 TEST3 TEST123456 SYSDATE

    consider these are the rows the user has entered and he clicks on the button. After clicking we need to insert 3 more rows into this grid like this –

    Rows – SETID DESCR LONG DESCR EFFDT
    Row1 GLOBL TEST TEST1234 SYSDATE
    Row2 GLOBL TEST TEST1234 SYSDATE
    Row3 310P1 TEST2 TEST12345 SYSDATE
    Row4 310P1 TEST2 TEST12345 SYSDATE
    Row5 310P3 TEST3 TEST123456 SYSDATE
    Row6 310P3 TEST3 TEST123456 SYSDATE

    So, its like inserting a new row for each existing row in the grid and carry forward the values from the previous row to the newly inserted row (this is never a issue since this is a effdt record). My code goes like this –

    Local Rowset &rset0, &rset1;
    Local Row &ROW;

    &rset0 = GetLevel0();
    &rset1 = &rset0(1).GetRowset(Scroll.HRB_ITM_MMT_ADD);
    &rows = &rset1.ActiveRowCount;
    Local Record &REC, &REC2;
    &REC = CreateRecord(Record.MASTER_ITEM_TBL);
    &REC2 = CreateRecord(Record.HRB_ITM_MMT_ADD);

    &BUCOUNT = 1;
    For &j = 1 To &rset1.ActiveRowCount

    &PREVBU = &rset1(&j).GetRecord(Record.HRB_ITM_MMT_ADD).GetField(Field.SETID).Value;
    &r = CurrentRowNumber();

    &rset1.InsertRow(&r);
    End-For;

    For some reasons it never works. Maybe the activerowcount in the For loop takes the rowcount as 3 (which is the user enters 3 rows into the grid before clicking the button) and this loop iterates only 3 times and inserts only at the current row number at the last row.

    Am still researching for the logic for this.

    Can someone help me in this?

    Thanks!!

    Reply
    • Sree #

      I think you should make use of the TOTALROWCOUNT instead of activerowcount. try the below code:
      let &p = 2;
      let &k = 1;
      For &j = 1 To &rset1.Totalrowcount
      &rset1.InsertRow(&p);
      &rset1(&p).GetRecord(Record.HRB_ITM_MMT_ADD).GetField(Field.SETID).Value = &rset1(&k).GetRecord(Record.HRB_ITM_MMT_ADD).GetField(Field.SETID).Value;
      ” Same as above line for all fields”
      &p = &p + 2;
      &k = &p – 1;
      End-For;

      Reply
  6. wira #

    Hi Rakes,

    Can we do some fill method / find some value method when data in the scroll? i mean it has not saved yet in dbase but i want to search the value..is it possible?

    Thanks
    Wira

    Reply
    • Rakesh Subhagan #

      @Wira,

      To search for a value within the grid, you may instantiate a rowset (using GetRowset) corresponding to the grid and loop through all active rows, comparing the data in the row to the value to be searched, during each iteration.

      Reply
  7. San #

    I tried this but still a blank row is getting inserted at the backend. Pls help me fix this.

    Reply
    • Rakesh Subhagan #

      @San,

      This example shows populating a grid and it uses a Dynamic view that’s not present in the DB. So I am confused as to what you refer by “blank row at the backend”.

      Can you pl elaborate?

      Reply
  8. isha #

    I tried using the above code to dynamically populate the grid, but i am getting a SQL error while using the same. The Insertrow function is creating the issue because when I comment it the code works fine and the data gets saved into databse. Please let me know the workaround to populate the rows in grid.

    Reply
  9. anshul #

    Hi,
    Thanks a lot for sharing this info. But while using this technique to populate the grid,I am getting an error “Invalid text for record.”Please help me through this!

    Reply
  10. Rao #

    Hi, I tried the above code at the at Button field at Fieldchange event. The Grid is populated with data But when I Save the page I m getting a Data Conflict Error.

    Please advise.

    Thanks in Advance

    Reply
  11. Rahul #

    Hii…can anyone please help..by using From Date and To Date functionality… I want to populate only required data in a grid..

    Reply
  12. Daniel Luiz #

    Thank you very much, your publication has helped me a lot in solving a problem.

    Reply
Leave a Reply

Your email address will not be published. Required fields are marked *

*
*