1/18/10

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.

Workflow errors on New Hire and Add employment instance

User tries to hire a new person or tries to add a new organization relationship encounters a series of error messages related to workflow...

Trending in this Blog this month