Header Ads

  • Recent Posts

    Code to custom sort a PeopleSoft Drop-down List

    How to custom sort a drop down list populated from a PeopleSoft Translate table?
    The translate field values are by default sorted in ascending order. If we need to have a custom sorting how do we do it? For example, if the translate values of a field are: 'Jan', 'Feb', 'Jul' and 'Sep' the drop down will list the fields as 'Feb', 'Jan', 'Jul' and 'Sep'. If the requirement is to push 'Jan' to the bottom of the list and to display 'Feb', 'Jul', 'Sep' and 'Jan' then we can use the below coding logic. Thanks to my colleagues VJ and AK for sharing this with me. Here is the code to be written at appropriate level:

     Local Rowset &Xlat;
     &Xlat = CreateRowset(Record.PSXLATITEM);
     &Xlat.Flush();
     &Xlat.Fill("Where fieldname = 'Field Name' order by decode(Fieldvalue,'Jan',1,0),fieldvalue");
     RECORDNAME.FIELDNAME.ClearDropDownList();
     &j = &Xlat.ActiveRowCount + 1;
     For &i = 1 To &Xlat.ActiveRowCount
        &Value = &Xlat.GetRow(&i).PSXLATITEM.FIELDVALUE.Value;
        &Descr = &Xlat.GetRow(&i).PSXLATITEM.XLATLONGNAME.Value;
        RECORDNAME.FIELDNAME.AddDropDownItem(&Value, Rept(Char(9), &j - &i)
    &Descr);
    End-For;

    What the above code does is, it clears the drop down list for the specified field. &Xlat - Rowset is flushed and filled with required translate values(based upon Where condition). The DECODE statement in the ORDER BY Clause assigns a value of 1 for 'Jan' and all other values as 0 which pushes the list to get sorted as 'Feb', 'Jul', 'Sep' and 'Jan'. The values which are decoded as 0 get sorted in ascending order - F(eb) first followed by J(ul) and then S(ep).

    If the list of months(in the order to be dropped down) are 'Mar', 'Jul', 'Oct', 'Dec' then we can use decode statements to assign values of 0, 1, 2 and 3 respectively for 'Mar', 'Jul', 'Oct' and 'Dec' which would custom sort the fields' translate values.

    The example of months is taken into consideration as it is self explanatory; the example of translate values is chosen as the PeopleSoft default page control for Translate values is drop-down list. However, this customization can be reused for any PeopleSoft table which is used to populate dropdown values and not confined only for Translate values.

    7 comments:

    1. This comment has been removed by the author.

      ReplyDelete
    2. Shyam this worked great... Thanks!

      ReplyDelete
    3. Anyone can help me where to write this code and in which event??

      ReplyDelete
    4. Hi
      I have an issue with the translate field To version. I got a Bug where customer is having this issue:
      I have one translate field which is character type and is placed in one work record.I have added values to the translate field like
      Value : Longname
      1 Version1
      2 Version 2
      3 version 3....
      9 Version 9
      A Version 10
      B Version 11
      C Version 12...
      J Version 19..
      Z version 35

      When my customer select the version field which is dropdown he is able to see values like
      VERSION 10
      VERSION 11
      VERSION 12
      VERSION 13.....
      VERSION 19
      VERSION 2
      VERSION 20
      VERSION 21...
      VERSION 29
      VERSION 3
      VERSION 31
      VERSION 32...
      VERSION 35
      VERSION 5
      VERSION 6
      VERSION 7
      VERSION 8
      VERSION 9 which is incorrect order.
      Now customer is requesting for order to show like
      VERSION 1
      VERSION 2
      VERSION 3.....
      Please do the needfull. Request to share inputs to my mail id : ravi.velchuri38@gmail.com. Thank you.

      ReplyDelete
    5. HI I have tried the below code.
      Local Rowset &Xlat;

      &FLD = GetRecord(Record.BP_WORKLIST_WRK).GetField(Field.BP_TO_VERSION);
      &FLD.ClearDropDownList();

      Evaluate %Component
      When Component.BP_COPY_VERSION
      &Xlat = CreateRowset(Record.PSXLATITEM);
      REM &Xlat.Fill("WHERE FILL.FIELDNAME = 'BP_TO_VERSION' AND Fill.FIELDVALUE in ('1','2') and FILL.EFFDT = (select max(B.EFFDT) from PSXLATITEM B where B.FIELDNAME ='BP_TO_VERSION' and B.FIELDVALUE in ('1','2') and B.EFFDT <= FILL.EFFDT)");
      &Xlat.Fill("WHERE FILL.FIELDNAME = 'BP_TO_VERSION' and FILL.EFFDT = (select max(B.EFFDT) from PSXLATITEM B where B.FIELDNAME ='BP_TO_VERSION' AND B.FIELDVALUE=FILL.FIELDVALUE and B.EFFDT <= %CurrentDateIn)");
      rem &Xlat.Fill("WHERE FILL.FIELDNAME = 'BP_TO_VERSION' and FILL.EFFDT = (select max(B.EFFDT) from PSXLATITEM B where B.FIELDNAME ='BP_TO_VERSION' AND B.FIELDVALUE=FILL.FIELDVALUE and B.EFFDT <= %CurrentDateIn) AND FILL.EFF_STATUS='A'");
      &Xlat_cnt = &Xlat.ActiveRowCount;
      MessageBox(0, "", 0, 0, "ActiveRowCount = " | &Xlat_cnt);
      &Xlat.Sort(PSXLATITEM.FIELDVALUE, "A");
      For &I = 1 To &Xlat_cnt
      &CodeIn = &Xlat.GetRow(&I).GetRecord(1).FIELDVALUE.Value;
      &DescIn = &Xlat.GetRow(&I).GetRecord(1).XLATLONGNAME.Value;
      If &CodeIn <> BP_WORKLIST_WRK.BP_BUDGET_VERSION Then
      &FLD.AddDropDownItem(&CodeIn, &DescIn);
      End-If;
      End-For;

      Break;
      When-Other
      End-Evaluate;

      But still i dont see values sort in correct order. in PIA when i check i see dropdown showing values like

      Base,Master,Version 10,version 11,version 12,version 13,version 14,version 15.......version 19,version 2, version 21, version 22........version 29,version 3, version 31,version 32....version 35,version 5,version 6,version 7,version 8.......

      in App designer i have set my translate field in correct order like

      Value - Long name
      0 - Base
      1-version 1
      2-version 2......
      A - Version 10
      B - Version 11.....
      z - version 35....

      Request for any inputs

      ReplyDelete
    6. I wrote the above piece of code in Page activate.

      ReplyDelete
    7. I am running a Application Engine, each time i run it, already inserted values should be flushed out and only newly inserted values should be available in the record. please help.

      ReplyDelete

    Please refrain for marketing messages and unnecessary back links.

    Post Top Ad

    Post Bottom Ad