Find Translates and Prompt Tables Via SQL

Created: September 13, 2016

Here are two tricks to get the translate values and the prompt table, from App Designer, without logging into App Designer.

Trick #1

A lot of times, the values you are searching for are in a table called Translates (PSXLATITEM). Most of these are delivered by PeopleSoft. Note that the table is effective dated, but you usually can get by without the effective dated logic. Also, note that the table does not start with PS_, but PS (no underscore). That means it is a tools table, not an application table. Also, use the FIELDVALUE field to link to the actual field. Also, you don’t usually need to outer join this, but for the LEDGER_KK and this field (KK_BUDG_TRANS_TYPE), you should, since only the budget rows use the table.

SELECT * FROM PSXLATITEM
WHERE FIELDNAME = 'KK_BUDG_TRANS_TYPE'

Trick #2

If you can’t find the fieldname in translate table, here is another trick.

Sometimes a field is tied to a prompt table and not the translate table. The developer (on the record definition) will sometimes store the prompt table. The below SQL lets you see what is on the App Designer (without having to log into App Designer). Note: If the prompt value starts with %EditTable, then that means the prompt table is dynamic and a developer will have to find this value for you, in the code.

SELECT RD.RECNAME, RD.RECTYPE, RD.RECDESCR, RI.FIELDNUM, RI.FIELDNAME, RI.RECNAME_PARENT, RI.EDITTABLE
FROM PSRECDEFN RD, PSRECFIELDDB RI
WHERE RD.RECNAME = RI.RECNAME
AND RD.RECNAME = 'LEDGER_KK'
ORDER BY RI.FIELDNUM

Enjoy.

↑ Back to top Share on Twitter