PeopleSoft Access Scrolls, Grids, Records, Fields using Object Oriented Programming.
By Milind Patil
Access Scrolls, Grids, Records, Fields using Object Oriented Programming.
Populating Scroll, Grid, Record, Field using Object Oriented Programming.
Sample code:
WinMessage(GetLevel0().GetRow(1).HC_FAC_ROLE_MAS.GetField(Field.FISCAL_YEAR).Value); /* this works for level 0*/
WinMessage(GetLevel0().GetRow(1).GetRowset(Scroll.HC_FAC_ROLE_MAS).GetRow(1).GetRecord(Record.HC_FAC_ROLE_MAS).GetField(Field.ROLENAME).Value); /* this works for level 1*/
WinMessage(GetLevel0().GetRow(1).GetRowset(Scroll.HC_FAC_ROLE_MAS).GetRow(1).GetRowset(Scroll.HC_FAC_ROLE_STG).GetRow(1).GetRecord(Record.HC_FAC_ROLE_STG).GetField(Field.OPRID).Value); /* works for row 1 hardcoded*/
For &i = 1 To GetLevel0().GetRow(1).GetRowset(Scroll.HC_FAC_ROLE_MAS).GetRow(1).GetRowset(Scroll.HC_FAC_ROLE_STG).ActiveRowCount
WinMessage(&i);
WinMessage(GetLevel0().GetRow(1).GetRowset(Scroll.HC_FAC_ROLE_MAS).GetRow(1).GetRowset(Scroll.HC_FAC_ROLE_STG).GetRow(&i).GetRecord(Record.HC_FAC_ROLE_STG).GetField(Field.OPRID).Value);
End-For;
1) Accessing the Scroll elements.
2) Populating Scroll or Grid using RowSet.
3) Current Row - CurrentRowNumber()
4) ActiveRowCount
5) Populate table with its own underlying table.
/*************************************************************************/
1) Accessing the Scroll elements. /*************************************************************************/
Access the scroll level 0 fields
&rs0 = GetLevel0(); /*Get level 0 rowset*/
&row0 = &rs0.getrow(1); /*Get row 1 of level 0 rowset*/
&rec0 = &row0.GetRecord(Record.RECORD123);/*Get (any) record from row of 0 rowset */
&field0 = &rec0.Field123; /* Get Field */
&value0 = &field0.value; /* Get value of the field */
Example:
Local Rowset &rs0;
Local Row &row0;
&rs0 = GetLevel0(); /*Get level 0 rowset*/
&row0 = &rs0.GetRow(1); /*Get row 1 of level 0 rowset*/
&rec0 = &row0.GetRecord(Record.RUN_CNTL_HC); /*Get (any) record from row of 0 rowset */
&field0 = &rec0.HRS_JOB_OPENING_ID; /* Get Field */
&value0 = &field0.value; /* Get value of the field */
WinMessage(" &value0 = " | &value0);
Access the scroll level 1 fields <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
&rs0 = GetLevel0(); /*Get level 0 rowset*/
&row0 = &rs0.getrow(1); /*Get row 0 of level 0 rowset*/
&rs1 = &row0.getrowset(Scroll.RECORD123); /*Get rowset from row 0 using the scroll name(can also be the name of the primary record in the scroll)*/
&row1 = &rs1.getrow(1); /*get row1 of level 1 rowset*/
&rec1 = &row1.getrecord(Record.RECORD345); /*get record in the row1*/
&field1 = &rec1.FIELD123; /*finally access the field in the record*/
&value1 = &field1.value;
Example:
Local Rowset &rs0;
Local Row &row0;
&rs0 = GetLevel0(); /*Get level 0 rowset*/
&row0 = &rs0.GetRow(1); /*Get row 0 of level 0 rowset*/
&rs1 = &row0.GetRowset(Scroll.HC_EEO_GROUPS); /*Get rowset from row 0 using the scroll name(can also be the name of the primary record in the scroll)*/
&row1 = &rs1.getrow(1); /*get row1 of level 1 rowset*/
&rec1 = &row1.getrecord(Record.HC_EEO_GROUPS); /*get record in the row1*/
&field1 = &rec1.RUN_CNTL_ID; /*finally access the field in the record*/
&value1 = &field1.value;
WinMessage("&value1 = " | &value1);
/*************************************************************************/
2) Populating Scroll or Grid on a Page using RowSet.
/*************************************************************************/
/*Calling button peoplecode - set the button destination as 'PeopleCode Command'. */
/*You many use destination as 'Secondary Page' and skip this PeopleCode if the variable is already assigned elsewhere.*/
Global string &var1;
&var1 = GetLevel0().GetRow(1).PERSONAL_DATA.EMPLID.Value;
/*For using the fields on level 0, 1 or 2 using the appropriate command shown below - replace with actual values
&var1 = GetLevel0().GetRow(1).LEVEL0_REC.FIELD_LEVEL1.Value;
&var1 = GetLevel0().GetRow(1).GetRowset(SCROLL.LEVEL1_REC)(1).LEVEL1_REC.FIELD_LEVEL1.VALUE;
&var1 = GetLevel0().GetRow(1).GetRowset(SCROLL.LEVEL1_REC)(ROWNUM).GetRowset(SCROLL.LEVEL2_REC)(ROWNUM_HERE).LEVEL2_REC.FIELD_LEVEL2.VALUE
*/
DoModal(Page.HC_MP_TEMP1, "My Page", 20, 20);
**************** Sample 1 ****************************************************************
/* Code on Page Activate */
/*http://pawan-mundhra.blogspot.com/2011/03/changing-grid-value-based-on-condition.html*/
Local Rowset &rsGrid, &rsDataSource;
&var1 = "00001896"; /* Component or global variables initialized on/passed from main page button - comment this line - to avoid been overwritten*/
&rsGrid = GetLevel0().GetRow(1).GetRowset(Scroll.HC_TL_EMPRCD_VW); /* name of the Grid goes here */
&rsGrid.Flush();
&rsDataSource = CreateRowset(Record.HC_TL_EMPRCD_VW); /* name of the Source View goes here */
&rsDataSource.Flush();
&rsDataSource.Fill(" WHERE EMPLID =:1", &var1);
&rsDataSource.CopyTo(&rsGrid);
************** End of Sample 1 ****************************************************************
*************** Sample 2 - but too many commands - use if few fields needed to be populated not entire grid****************************
http://hariprasadpeoplesoft.blogspot.com/2012/04/populating-data-into-grid.html
Local SQL &SQL1;
Local Rowset &RS1;
&var1 = "PS";
&SQL1 = CreateSQL("SELECT OPRID, RUN_CNTL_ID FROM PS_PRCSRUNCNTL WHERE OPRID =:1", &var1);
/*creating the SQL statement*/
&RS1 = GetLevel0()(1).GetRowset(Scroll.PRCSRUNCNTL);
/*creating a rowset corresponding to the grid */
While &SQL1.Fetch(&OPRID, &RUN_CNTL_ID)
&RS1(1).PRCSRUNCNTL.OPRID.Value = &OPRID;
/*assigning the voucher ID to the filed in the grid */
&RS1(1).PRCSRUNCNTL.RUN_CNTL_ID.Value = &RUN_CNTL_ID;
&RS1.InsertRow(0);
/* inserting a new row at the beginning of the grid*/
End-While;
&RS1.DeleteRow(1);
/* deleting the empty row left after all the insertions */
&SQL1.Close();
/* closing the SQL object */
*************** End of Sample 2 ****************************
To access a field on scroll Level 0, 1 or 2
&var1 = GetLevel0().GetRow(1).LEVEL0_REC.FIELD_LEVEL1.Value;
&var1 = GetLevel0().GetRow(1).GetRowset(SCROLL.LEVEL1_REC)(1).LEVEL1_REC.FIELD_LEVEL1.VALUE;
&var1 = GetLevel0().GetRow(1).GetRowset(SCROLL.LEVEL1_REC)(ROWNUM).GetRowset(SCROLL.LEVEL2_REC)(ROWNUM_HERE).LEVEL2_REC.FIELD_LEVEL2.VALUE
/* - corrections required below - see above
For Level 0:
Value_LEVEL0 = GetLevel0().GetRow(1).LEVEL0_REC.FIELD_LEVEL1.VALUE;
For level 1:
Value_LEVEL1 = GetLevel0().GetRow(1).GetRowset(SCROLL.LEVEL1_REC)(1).LEVEL1_REC.FIELD_LEVEL1.VALUE;
FOR LEVEL2 :
VALUE_LEVEL2 = GetLevel0().GetRow(1).GetRowset(SCROLL.LEVEL1_REC)(ROWNUM).GetRowset(SCROLL.LEVEL2_REC). LEVEL2_REC.FIELD_LEVEL2.VALUE
*/
------------------------------------------------
3) GetLevel0().GetRow(1).GetRowset(Scroll.HRS_RCMNT)(CurrentRowNumber()).HC_APP_JOB_WRK.HC_PADMIN_REQ.Value = "test222";
This gets the current row number - can be used in rowinit - instead of looping in the post build.
4) For &i = 1 To GetLevel0()(1).GetRowset(Scroll.TL_INSTALL_VW).ActiveRowCount
¤t_trc = GetLevel0()(1).GetRowset(Scroll.TL_INSTALL_VW)(&i).TL_TR_WEEK_WRK.TRC.Value;
If ¤t_trc = "CME" Then
&QTY1 = GetLevel0()(1).GetRowset(Scroll.TL_INSTALL_VW)(&i).TL_TR_WEEK_WRK.QTY_DAY1.Value;
&QTY2 = GetLevel0()(1).GetRowset(Scroll.TL_INSTALL_VW)(&i).TL_TR_WEEK_WRK.QTY_DAY2.Value;
&QTY3 = GetLevel0()(1).GetRowset(Scroll.TL_INSTALL_VW)(&i).TL_TR_WEEK_WRK.QTY_DAY3.Value;
&QTY4 = GetLevel0()(1).GetRowset(Scroll.TL_INSTALL_VW)(&i).TL_TR_WEEK_WRK.QTY_DAY4.Value;
&QTY5 = GetLevel0()(1).GetRowset(Scroll.TL_INSTALL_VW)(&i).TL_TR_WEEK_WRK.QTY_DAY5.Value;
&QTY6 = GetLevel0()(1).GetRowset(Scroll.TL_INSTALL_VW)(&i).TL_TR_WEEK_WRK.QTY_DAY6.Value;
&QTY7 = GetLevel0()(1).GetRowset(Scroll.TL_INSTALL_VW)(&i).TL_TR_WEEK_WRK.QTY_DAY7.Value;
&QTY_CME = &QTY_CME + &QTY1 + &QTY2 + &QTY3 + &QTY4 + &QTY5 + &QTY6 + &QTY7;
End-If;
End-For;
5) Populate table with its own underlying table.
&rs = GetLevel0()(1).GetRowset(Scroll.TABLE_ABC);
&rs.Flush();
&rs.Select(Record.TABLE_ABC, "WHERE EMPLID =:1", &emplid1);
5) Example code
Note two ways to populate a record set. 1) using SQL and adding line by line (commented), or giving SQL and populating the record set. The advantage of 2 is that it adds and updates the record easily. The first one tries to always add, even the queried records.
MY_TBL_SRV_GBL_SavePreChange_ComponentPC
---------------------------------------------------------------------------
/* first get the max(MY_TBL_KEY1) from the database */
SQLExec("select max(MY_TBL_KEY1) + 1 from PS_MY_TBL_DIR", &max_database_id);
/* then scroll through the rowset and get max id - in case it assigned these before but failed to save because of error */
&max_buffer_id = 0;
For &i = 1 To GetLevel0()(1).GetRowset(Scroll.MY_TBL_DIR).ActiveRowCount
If GetLevel0()(1).GetRowset(Scroll.MY_TBL_DIR)(&i).MY_TBL_DIR.MY_TBL_KEY1.Value > &max_buffer_id Then
&max_buffer_id = GetLevel0()(1).GetRowset(Scroll.MY_TBL_DIR)(&i).MY_TBL_DIR.MY_TBL_KEY1.Value;
End-If;
End-For;
&max_buffer_id = &max_buffer_id + 1;
If &max_buffer_id < &max_database_id Then
&max_buffer_id = &max_database_id;
End-If;
For &i = 1 To GetLevel0()(1).GetRowset(Scroll.MY_TBL_DIR).ActiveRowCount
If GetLevel0()(1).GetRowset(Scroll.MY_TBL_DIR)(&i).MY_TBL_DIR.MY_TBL_KEY1.Value = 0 Or
None(GetLevel0()(1).GetRowset(Scroll.MY_TBL_DIR)(&i).MY_TBL_DIR.MY_TBL_KEY1.Value) Or
GetLevel0()(1).GetRowset(Scroll.MY_TBL_DIR)(&i).MY_TBL_DIR.MY_TBL_KEY1.Value = 99999 Then
GetLevel0()(1).GetRowset(Scroll.MY_TBL_DIR)(&i).MY_TBL_DIR.MY_TBL_KEY1.Value = &max_buffer_id;
&max_buffer_id = &max_buffer_id + 1;
GetLevel0()(1).GetRowset(Scroll.MY_TBL_DIR)(&i).MY_TBL_DIR.MY_TBL_SERVER_ID.Value = GetLevel0().GetRow(1).MY_TBL_PSSERVER.GetField(Field.MY_TBL_SERVER_ID).Value;
End-If;
End-For;
<* Moved it to component save
If MY_TBL_DIR.MY_TBL_KEY1 = 0 Then
SQLExec("select max(MY_TBL_KEY1) from PS_MY_TBL_DIR", &id);
MY_TBL_DIR.MY_TBL_KEY1 = &id + 1;
If MY_TBL_DIR.MY_TBL_KEY1 = 1 Then
MY_TBL_DIR.MY_TBL_KEY1 = 1001 /* this is the series starting with 1001 for directory ids */
End-If;
End-If;
MY_TBL_DIR.MY_TBL_SERVER_ID = GetLevel0().GetRow(1).MY_TBL_PSSERVER.GetField(Field.MY_TBL_SERVER_ID).Value;
If MY_TBL_DIR.MY_TBL_CREATEDBY = "" Then
MY_TBL_DIR.MY_TBL_CREATEDBY = %OperatorId;
MY_TBL_DIR.MY_TBL_CREATEDTIME = %Datetime;
Else
MY_TBL_DIR.MY_TBL_LASTUPDTEBY = %OperatorId;
MY_TBL_DIR.MY_TBL_LASTUPDTIME = %Datetime;
End-If;
*>
---------------------------------------------------------------------------
MY_TBL_SRV_GBL_PostBuild_ComponentPC
---------------------------------------------------------------------------
SQLExec("select MY_TBL_HASHCOMPARE from PS_MY_TBL_HASHVAL", &hask_key_encrypted);
MY_TBL_WRK.MY_TBL_HASHCOMPARE = &hask_key_encrypted;
/* Query the details */
Local SQL &SQL1;
Local Rowset &RS1;
&server = GetLevel0().GetRow(1).MY_TBL_PSSERVER.GetField(Field.MY_TBL_SERVER_ID).Value;
/*creating the SQL statement*/
/*&SQL1 = CreateSQL("SELECT MY_TBL_KEY1, MY_TBL_SERVER_ID, MY_TBL_STATUS, MY_TBL_DIRECTORY, MY_TBL_DIRSRC_DEST, MY_TBL_DESCR254_MX, MY_TBL_ARCHIVE_LOC FROM PS_MY_TBL_DIR WHERE MY_TBL_SERVER_ID = :1", &server, &MY_TBL_KEY1, &MY_TBL_SERVER_ID, &MY_TBL_STATUS, &MY_TBL_DIRECTORY, &MY_TBL_DIRSRC_DEST, &MY_TBL_DESCR254_MX, &MY_TBL_ARCHIVE_LOC);*/
&SQL1 = CreateSQL("SELECT MY_TBL_KEY1, MY_TBL_SERVER_ID, MY_TBL_STATUS, MY_TBL_DIRECTORY, MY_TBL_DIRSRC_DEST, MY_TBL_DESCR254_MX, MY_TBL_ARCHIVE_LOC FROM PS_MY_TBL_DIR WHERE MY_TBL_SERVER_ID = :1 order by MY_TBL_KEY1 DESC", &server);
&RS1 = GetLevel0()(1).GetRowset(Scroll.MY_TBL_DIR);
/*creating a rowset corresponding to the grid */
&RS1.Flush();
&RS1.Select(Record.MY_TBL_DIR, "WHERE MY_TBL_SERVER_ID = :1 order by MY_TBL_KEY1 DESC", &server);
<* This for manually adding line by line
While &SQL1.Fetch(&MY_TBL_KEY1, &MY_TBL_SERVER_ID, &MY_TBL_STATUS, &MY_TBL_DIRECTORY, &MY_TBL_DIRSRC_DEST, &MY_TBL_DESCR254_MX, &MY_TBL_ARCHIVE_LOC)
&RS1(1).MY_TBL_DIR.MY_TBL_KEY1.Value = &MY_TBL_KEY1;
&RS1(1).MY_TBL_DIR.MY_TBL_SERVER_ID.Value = &MY_TBL_SERVER_ID;
&RS1(1).MY_TBL_DIR.MY_TBL_STATUS.Value = &MY_TBL_STATUS;
&RS1(1).MY_TBL_DIR.MY_TBL_DIRECTORY.Value = &MY_TBL_DIRECTORY;
&RS1(1).MY_TBL_DIR.MY_TBL_DIRSRC_DEST.Value = &MY_TBL_DIRSRC_DEST;
&RS1(1).MY_TBL_DIR.MY_TBL_DESCR254_MX.Value = &MY_TBL_DESCR254_MX;
&RS1(1).MY_TBL_DIR.MY_TBL_ARCHIVE_LOC.Value = &MY_TBL_ARCHIVE_LOC;
&RS1(1).MY_TBL_DIR.MY_TBL_USER_FLD1.Value = " ";
&RS1(1).MY_TBL_DIR.MY_TBL_USER_FLD2.Value = " ";
&RS1(1).MY_TBL_DIR.MY_TBL_USER_FLD3.Value = " ";
&RS1(1).MY_TBL_DIR.MY_TBL_KEY1.Enabled = False;
&RS1(1).MY_TBL_DIR.MY_TBL_SERVER_ID.Enabled = False;
&RS1(1).MY_TBL_DIR.MY_TBL_STATUS.Enabled = False;
&RS1(1).MY_TBL_DIR.MY_TBL_DIRECTORY.Enabled = False;
&RS1(1).MY_TBL_DIR.MY_TBL_DIRSRC_DEST.Enabled = False;
&RS1(1).MY_TBL_DIR.MY_TBL_DESCR254_MX.Enabled = False;
&RS1(1).MY_TBL_DIR.MY_TBL_ARCHIVE_LOC.Enabled = False;
&RS1(1).MY_TBL_DIR.MY_TBL_USER_FLD1.Enabled = False;
&RS1(1).MY_TBL_DIR.MY_TBL_USER_FLD2.Enabled = False;
&RS1(1).MY_TBL_DIR.MY_TBL_USER_FLD3.Enabled = False;
&RS1.InsertRow(0);
/* inserting a new row at the beginning of the grid*/
End-While;
/* deleting the empty row left after all the insertions */
&RS1.DeleteRow(1);
*>
&SQL1.Close();
/* closing the SQL object */
---------------------------------------------------------------------------
MY_TBL_SRV_GBL_MY_TBL_DIR_RowInsert_ComponentPC
---------------------------------------------------------------------------
If MY_TBL_DIR.MY_TBL_SERVER_ID.Value = 0 Or
None(MY_TBL_DIR.MY_TBL_SERVER_ID.Value) Then
MY_TBL_DIR.MY_TBL_SERVER_ID.Value = 99999;
End-If;
<* Moved it to component save
If MY_TBL_DIR.MY_TBL_KEY1 = 0 Then
SQLExec("select max(MY_TBL_KEY1) from PS_MY_TBL_DIR", &id);
MY_TBL_DIR.MY_TBL_KEY1 = &id + 1;
If MY_TBL_DIR.MY_TBL_KEY1 = 1 Then
MY_TBL_DIR.MY_TBL_KEY1 = 1001 /* this is the series starting with 1001 for directory ids */
End-If;
End-If;
MY_TBL_DIR.MY_TBL_SERVER_ID = GetLevel0().GetRow(1).MY_TBL_PSSERVER.GetField(Field.MY_TBL_SERVER_ID).Value;
If MY_TBL_DIR.MY_TBL_CREATEDBY = "" Then
MY_TBL_DIR.MY_TBL_CREATEDBY = %OperatorId;
MY_TBL_DIR.MY_TBL_CREATEDTIME = %Datetime;
Else
MY_TBL_DIR.MY_TBL_LASTUPDTEBY = %OperatorId;
MY_TBL_DIR.MY_TBL_LASTUPDTIME = %Datetime;
End-If;
*>
---------------------------------------------------------------------------
MY_TBL_SRV_GBL_MY_TBL_PSSERVER_SavePreChange_ComponentPC
---------------------------------------------------------------------------
SQLExec("select count(*) from PS_MY_TBL_PSSERVER where MY_TBL_SERVER = :1", MY_TBL_PSSERVER.MY_TBL_SERVER, &count1);
If %Mode = "A" And
&count1 > 0 Then
Error ("Server name:" | MY_TBL_PSSERVER.MY_TBL_SERVER | " already exists, you may not create duplicate records with same server name.");
End-If;
If MY_TBL_PSSERVER.MY_TBL_SERVER_ID = 0 Then
SQLExec("select max(MY_TBL_SERVER_ID) from PS_MY_TBL_PSSERVER", &id);
MY_TBL_PSSERVER.MY_TBL_SERVER_ID = &id + 1;
End-If;
If MY_TBL_PSSERVER.MY_TBL_CREATEDBY = "" Then
MY_TBL_PSSERVER.MY_TBL_CREATEDBY = %OperatorId;
MY_TBL_PSSERVER.MY_TBL_CREATEDTIME = %Datetime;
Else
MY_TBL_PSSERVER.MY_TBL_LASTUPDTEBY = %OperatorId;
MY_TBL_PSSERVER.MY_TBL_LASTUPDTIME = %Datetime;
End-If;
--------------------------------------------------------------------------------------------
To order scroll
--------------------------------------------------------------------------------------------
&rs = GetLevel0()(1).GetRowset(Scroll.MY_TBL_PVAL_HDR);
&rs.Sort(MY_TBL_PVAL_HDR.CREATEDTIME, "D");
--------------------------------------------------------------------------------------------
To access a rowset (grid) at level 2 inside a scroll at level 1
--------------------------------------------------------------------------------------------
If %Page = "MY_TBL_AUDIT" Then
&MY_TBL_runkey = GetLevel0().GetRow(1).GetRowset(Scroll.MY_TBL_PVAL_HDR)(CurrentRowNumber(1)).GetRowset(Scroll.MY_TBL_PVAL)(CurrentRowNumber(2)).MY_TBL_PVAL.MY_TBL_RUNKEY.Value;
&MY_TBL_task = GetLevel0().GetRow(1).GetRowset(Scroll.MY_TBL_PVAL_HDR)(CurrentRowNumber(1)).GetRowset(Scroll.MY_TBL_PVAL)(CurrentRowNumber(2)).MY_TBL_PVAL.MY_TBL_TASK.Value;
&prcsinstance = GetLevel0().GetRow(1).GetRowset(Scroll.MY_TBL_PVAL_HDR)(CurrentRowNumber(1)).GetRowset(Scroll.MY_TBL_PVAL)(CurrentRowNumber(2)).MY_TBL_PVAL.PRCSINSTANCE.Value;
/* WinMessage("&MY_TBL_runkey = " | &MY_TBL_runkey);
WinMessage("&MY_TBL_task = " | &MY_TBL_task);
WinMessage("&prcsinstance = " | &prcsinstance);*/
SQLExec("select L.MY_TBL_LOG from PS_MY_TBL_PVAL L where L.MY_TBL_RUNKEY = :1 and L.MY_TBL_TASK = :2 and L.PRCSINSTANCE = :3", &MY_TBL_runkey, &MY_TBL_task, &prcsinstance, &log1);
MY_TBL_WRK2.MY_TBL_LOG = &log1;
TransferPage(Page.MY_TBL_AUDIT_LOG);
End-If;
If %Page = "MY_TBL_AUDIT_LOG" Then
TransferPage(Page.MY_TBL_AUDIT);
End-If;
--------------------------------------------------------------------------------------------
