Peoplecode to check if field contains spaces or punctuation

If All(PTSF_SRCH_ENGN.PTSF_SRCH_ENG_INS.Value) Then
   &Name = PTSF_SRCH_ENGN.PTSF_SRCH_ENG_INS.Value;
   &I = 1;
   If IsAlphaNumeric(&Name) = False Then
      While &I <= Len(&Name)
         /* Test to see if failure is because of _ or - or not*/
         &testchar = Substring(&Name, &I, 1);
         If (IsAlphaNumeric(&testchar) = True) Or
               (&testchar = "_") Or
               (&testchar = "-") Then;
            &I = &I + 1;
         Else
            Error (MsgGetText(78, 33, "no spaces or punctuation are allowed"));
            &I = Len(&Name);
            Break;
         End-If;
      End-While;
   Else
      /* test for number in first position*/
      &testnumberpos1 = Substring(&Name, 1, 1);
      If IsDigits(&testnumberpos1) = True Then
         Error (MsgGetText(78, 33, "no spaces or punctuation are allowed"));
         &I = Len(&Name);
      End-If;
   End-If;
End-If;

Flush data from Component Buffer in a Grid

In my case , I am flushing the grid data on field change of Qualification field

Local Rowset &Xlat;

/* --- Flush Elective Grid on Qualification Field Change --- */

&Xlat = GetRowset(Scroll.X_ELECTIVE_INFO);


&Xlat.Flush();

Read CSV file in Peoplecode

Local File &MYFILE;
Local Record &REC;
Local array of string &ARRAY;
Local string &FilePath, &FileName;

Local Rowset &GRD_RS;

&GRD_RS = GetLevel0()(1).GetRowset(Scroll.IOBM_PHN_NO_WRK);
&GRD_RS.Flush();
SQLExec("Delete  from PS_IOBM_PHN_NO_WRK ");
SQLExec("commit");

&FILE = IOBM_SMSATT_WRK.ATTACHUSERFILE.Value;

&URL = GetURL(URL.IOBM_FINANCE_UPLOAD);

&MYFILE = GetFile(&URL | &FILE, "r", "a", %FilePath_Absolute);


&REC = CreateRecord(Record.IOBM_PHN_NO_WRK);
&ARRAY = CreateArrayRept("", 0);


If &MYFILE.IsOpen Then
 
   While &MYFILE.ReadLine(&STRING);
     
      &ARRAY = Split(&STRING, ",");
      &REC.EMPLID.Value = &ARRAY [1];
      SQLExec("select Distinct phone  from PS_PERSON_PHONE where Emplid = :1 ", &ARRAY [1], &PHONE);
      &REC.PHONE.Value = &PHONE;
      &REC.Insert();
      rem  MessageBox(0, "", 0, 0, "" | &REC.EMPLID.Value | "" | &REC.PHONE.Value);
     
   End-While;
End-If;
GetLevel0().Refresh()

Get Day name in peoplesoft

Local string &Get_DT  =    (IOBM_ATTEND_WRK.DT_ARRIVE.Value);


rem &GRD_SQL2 = SQLExec("select to_char(sysdate, 'DY') FROM DUAL ", &getday);

&GRD_SQL2 = SQLExec("select to_char(to_date(:1,'yyyy/mm/dd'), 'DY') FROM DUAL ", &Get_DT, &getday);

Evaluate &getday

When "THU"
   IOBM_ATTEND_WRK.THURS.Value = "Y";

   When "FRI"
   IOBM_ATTEND_WRK.FRI.Value = "Y";
    
When "SAT"
   IOBM_ATTEND_WRK.SAT.Value = "Y";

  When "SUN"
   IOBM_ATTEND_WRK.SUN.Value = "Y";

  When "MON"
   IOBM_ATTEND_WRK.MON.Value = "Y";

  When "TUE"
   IOBM_ATTEND_WRK.TUES.Value = "Y";

  When "WED"
   IOBM_ATTEND_WRK.WED.Value = "Y";

End-Evaluate;

SQLExec

Sqlexec  Update

  SQLExec("Update PS_SF_ACCTG_LN set GL_DISTRIB_STATUS = 'Y', PROCESS_INSTANCE = :4 where RUN_DT = :1 and SEQNUM = :2 and SF_LINE_NBR = :3", &REC.RUN_DT.Value, &REC.SEQNUM.Value, &REC.SF_LINE_NBR.Value, &Process_instance);


----------------------------------------------------------------------------------------------------------

Comma Seprated String in Poeplecode

&UserRoles = CreateArray("");
Local array of string &roles = %Roles;
Local string &rolesStr = "";
If &roles.Len >= 1 Then
   &rolesStr = "'" | &UserRoles [1] | "'";
End-If;

For &i = 2 To &roles.Len
   &rolesStr = &rolesStr | ", ";
   &UserRoles [&i] = &roles [&i];
   &rolesStr = &rolesStr | "'" | &UserRoles [&i] | "'";
End-For;





If %Language = %Language_Base Then
  
  
   FORM_ADD_VW.FORM_TYPE.SqlText = "SELECT A.FORM_TYPE, A.PORTAL_LABEL FROM PS_KS_FORM_TYPE_VW A  WHERE A.ROLEUSER IN (" | &rolesStr | ") AND A.FORM_TYPE_STATUS ='A' AND A.PORTAL_NAME='" | %Portal | "' AND A.EFFDT_FROM <= %CurrentDateIn AND (A.EFFDT_TO is null or A.EFFDT_TO >= %CurrentDateIn)";
  
  
Else
  
   FORM_ADD_VW.FORM_TYPE.SqlText = "SELECT A.FORM_TYPE, B.PORTAL_LABEL FROM PS_KS_FORM_TYPE_VW A , PS_FORM_TYPE_LG B WHERE A.ROLEUSER IN (" | &rolesStr | ") AND  A.FORM_TYPE_STATUS ='A' AND A.PORTAL_NAME='" | %Portal | "' AND A.EFFDT_FROM <= %CurrentDateIn AND (A.EFFDT_TO is null or A.EFFDT_TO >= %CurrentDateIn) AND A.FORM_TYPE=B.FORM_TYPE AND B.LANGUAGE_CD='" | %Language | "'";
  
  
End-If;

Rowset Fill Method

 Simple Fill Method 

The Rowset method Fill is used to populate a stand-alone rowset from the database.
 It take a where clause as a parameter. The underlying record is used as a select record.

Local Rowset &GetNbr = CreateRowset(Record.IOBM_PHN_NO_WRK);
&GetNbr.Fill();

   For &i = 1 To &GetNbr.ActiveRowCount
     
     
      &getPhone = &GetNbr.GetRow(&i).GetRecord(Record.IOBM_PHN_NO_WRK).GetField(Field.PHONE).Value;
      &getemplid = &GetNbr.GetRow(&i).GetRecord(Record.IOBM_PHN_NO_WRK).GetField(Field.EMPLID).Value;

End-for ;

---------------------------------------------------------------------------------------------------------------------------
 1.  Using JOIN Keyword.

Some times the criteria for selection comes from a record other than the one your selecting from.

For example, let’s say you want to select records from the JOB table, but only those with a Job Code in a certain Grade.
Basically, you need JOB Record for the data and JOBCODE_TBL record  for the criteria.


Local Record &job = CreateRecord(Record.JOB);

&job.Fill("JOIN PS_JOBCODE_TBL JC ON FILL.SETID = JC.SETID
AND FILL.JOBCODE = JC.JOBCODE WHERE JC.GRADE = :1", &paramGrade);


----------------------------------------------------------------------------------------------------------------------


2. Using Exist Keyword 



Local Rowset &rs = CreateRowset(Record.RF_INST_PROD);

&rs.Fill("WHERE RBTACCTID = :1 AND EXISTS (SELECT 1 FROM PS_RF_INST_PROD_ST WHERE SETID = FILL.SETID AND INST_PROD_ID = FILL.INST_PROD_ID AND INST_PROD_STATUS = 'INS')", &acctid);



------------------------------------------------------------------------------------------------------------------------- 

3 . Using UNION Keyword.
Local Rowset &rs = Createrowset(Record.PERSON);

&rs.Fill("WHERE 1=0 UNION SELECT EMPLID, %DateOut(BIRTHDATE),BIRTHPLACE,BIRTHCOUNTRY,BIRTHSTATE,%DateOut(DT_OF_DEATH),%DateTimeOut(LAST_CHILD_UPDDTM) FROM PS_PERSON WHERE EMPLID = :1", &emplid);