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.
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.
This comment has been removed by the author.
ReplyDeleteShyam this worked great... Thanks!
ReplyDeleteAnyone can help me where to write this code and in which event??
ReplyDeleteHi
ReplyDeleteI 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.
HI I have tried the below code.
ReplyDeleteLocal 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
I wrote the above piece of code in Page activate.
ReplyDeleteI 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