Tyson Moore

Consultant, Developer, Volunteer

Posts Tagged ‘xlat’

Sort using XLATLONGNAME instead of Translate

June 15th, 2010

I have a question. Hope you can help

Need help to sort using XLATLONGNAME. Currently we have a search record that returns results in a grid (on the search page) Results are displayed in 4 columns. One of the columns has a translate value. So the sort is based on the translate value. We want it to be by the XLATLONGNAME. Is that possible.

Example (say we have the following)

Translate Value – Long Name
A – Backlog
B – Available
C – Contract
D – Entitled
E – Dispatch

We do NOT want to change the translate value order. But want to use the XLATLONGNAME for the search sort.

Currently when the results are displayed, the sort is by Translate value. Say if the field 2 is the one with the above translate value, then the results show up as

Field 1 :: Field 2
Backlog :: XXXXX
Available:: XXXXX
Contract:: XXXXX
Entitled :: XXXXX
Dispatch:: XXXXX
…this is cause its sorted by Translate value.

Need this to be as follows ..
Field1 :: Field 2
Available:: XXXXX
Backlog :: XXXXX
Contract:: XXXXX
Dispatch:: XXXXX
Entitled :: XXXXX

The above set is what is required – This is by XLATLONGNAME.

Thank you,
Jessica

Jessica,

You’ll likely have to create a new search record. It should be a view of the current search record joined to the XLAT item. You can then select the long name and sort on it that way.

-Tyson

Share and Enjoy:

No comments »

Posted in PeopleSoft

Tags: application designer PeopleSoft translate xlat

Selecting the translate values from PSXLATITEM table by joining to another table

January 14th, 2010

Our current Version: PS8.9; People Tools: 8.47How can we select the translate values from PSXLATITEM table by joining to another table?I want the description(XLATLONGNAME) in the field value rather th
How can I get the translate values description selected in the query?
Record used :
PSXLATITEM.
HRS_JO_RQMT_I
fiels selected:
SCHEDULED_TYPE
SHIFT
Translate values of Schduled_type:
P
F
O
Long names:
par time
Full time
Optional
I want the descriptions (long names) of the tanslate values should be displayed in the output rather than
F, P, O
Any suggestions ?/ Thanks.

If you are doing this via the online Query tool, you can change the Field settings (on the Field tab) to display the Long description rather than the ID for the fields you are selecting.

If you are just writing SQL, I would suggest using XLATTABLE_VW instead of PSXLATITEM to obtain the long description of the field(s) you are selecting. In my queries, I typically join to that view with the following criteria:

SELECT D.XLATLONGNAME AS SCHEDULE_TYPE
FROM PS_HRS_JO_RQMT_I A, XLATTABLE_VW B
WHERE B.FIELDNAME = 'SCHEDULED_TYPE'
AND A.SCHEDULED_TYPE = B.FIELDVALUE AND B.EFFDT
(SELECT MAX(B_ED.EFFDT) FROM XLATTABLE_VW B_ED
WHERE B.FIELDNAME = B_ED.FIELDNAME
AND B.FIELDVALUE = B_ED.FIELDVALUE
AND B_ED.EFFDT <= SYSDATE);