How to get Field Value from its Description?

How to get Field Value from its Description?

How to get Translate Field Value from its Description?

A brief overview 1st.

What are XLAT Tables in PeopleSoft?

XLATTABLE: Stores translate values (PeopleSoft version prior to 8.4).
PSXLATDEFN: Stores all fields that have Xlat values. This table does not store any Xlat values.
PSXLATITEM: Stores fields with their actual translate values (PeopleSoft version 8.4 and above).

Some Functions:

LongTranslateValue: This property returns a string that contains the Long translate (XLAT) value of the field if the field is based on a translate table.
ShortTranslateValue: This property returns a string that contains the Short translate (XLAT) value of the field if the field is based on a translate table.

How to get Translate Field Value from its Description?

What are the important Fields in the PSXLATITEM table?

PeopleSoft Field Name	Description
FIELDNAME	Field Name
FIELDVALUE	Field Value
EFFDT	Effective DateDefault: %date
EFF_STATUS	Effective Status
XLATLONGNAME	Translate Long Name
XLATSHORTNAME	Translate Value Short Name

SQL For getting Translate Field From Description?

SELECT FIELDVALUE FROM PSXLATITEM WHERE FIELDNAME = :1 AND
(FIELDVALUE= :2 OR XLATLONGNAME = :2 OR XLATSHORTNAME= :2) AND
EFFDT=(SELECT MAX(B.EFFDT) FROM PSXLATITEM B
WHERE B.FIELDNAME = FIELDNAME AND B.FIELDVALUE = FIELDVALUE AND B.EFFDT <= SYSDATE)
AND EFF_STATUS = 'A';

Method to get the Translate Field from Description:

method GetValueFromXlatDescr    /+ &FieldSrc as Field, +/    /+ &FieldTrgt as Field, +/    /+ &effDt as Date, +/    /+ &refFieldName as String +/	Variables: Source Field Target Field Reference Date Xlat Field Name
Local Record &rec = CreateRecord(Record.PSXLATITEM);    Local string &SQL, &value, &sError;    If All(&FieldSrc.Value) Then	If Field has some value then
&SQL = "SELECT A.FIELDVALUE FROM PSXLATITEM A  WHERE  A.FIELDNAME = :1 AND %EffdtCheck(PSXLATITEM B, A, %Datein(:2))  AND A.EFF_STATUS = 'A' AND (upper(A.XLATLONGNAME) = upper(:3) or A.FIELDVALUE = :3)";	SQL
SQLExec(&SQL, &refFieldName, &effDt, &FieldSrc.Value, &FieldTrgt.Value);       %This.ErrorIfNoFieldValue(&FieldSrc, &FieldTrgt);    End-If; end-method;	Execute the SQL  & Check for Errors

Explanation:

I have written method in App Pkg style. Since this is the new style of coding and is object oriented, we should adopt this as much as possible.
We could have passed many variables, but to adopt a Object Oriented approach (pass by reference) I am using the Fields.
The Field object can carry all these and we need not worry about the limitation of returning more than one value.
After verification that the field has some value, we formulate the SQL. In the WHERE clause I have used the FIELDVALUE check along with the intended XLATLONGNAME just in case we get the actual FIELDVALUE and want to validate it. Also I have used upper to not limit the case of recieved strings.
Post execution of the SQL, we are passing it to Check and Error so that we dont retain the null values, and use them for loading in the final targets.

How to get Translate YesNo Field Value from its Description?

A brief overview 1st. 

When we want to save just a Yew/No we dont need a translate or a prompt. We can instead just enable the YesNo property.

In PSRECFIELDDB (Record Field Without Subrecs*) (or PSRECFIELD for that matter), we have field USEEDIT. This captures extra properties of a Record Field. If it is Yes/No field we can understand by the following.

Bit Number	Bit Value	Set (Not Set) Value	Comment
13	8192	Edits - Yes/No	Field will only accept a Y/N response that is mapped to specified database values.

How to get Translate YesNo Field Value from its Description?

Now if we get a Yes/No in description we cant store it in the 1 Char Field. So we can use a simple Evaluate to convert it to Y/N and Error our if its neither.

method GetValueFromYesNo    /+ &FieldSrc as Field, +/    /+ &FieldTrgt as Field +/    Local string &value;    If All(&FieldSrc.Value) Then       Evaluate Upper(&FieldSrc.Value)       When = "YES"          &FieldTrgt.Value = "Y";          Break;       When = "NO"          &FieldTrgt.Value = "N";          Break;       When-Other          %This.ErrorIfNoFieldValue(&FieldSrc, &FieldTrgt);       End-Evaluate;    End-If; end-method;

Explanation:
Like method GetValueFromXlatDescr this method takes the Field, Evaluates it and Returns a Y/N.
If it has a value and its not Yes/No then a Error is thrown.

How to get Prompt Field Value from its Description?

A brief overview 1st.
When Translates (max 4 char fields with values stored in PSXLATITEM) or YesNo field is not sufficient, we can store values in other tables. These tables can be used as prompt tables, and the field that need to refer to these tables must have their property set at Record Field level.
USEEDIT looks like the following in this case.

Bit Number	Bit Value	Set (Not Set) Value
14	16384	Prompt table edit enabled.

How to get Prompt Field Value from its Description?

method GetValueFromPromptDescr    /+ &FieldSrc as Field, +/    /+ &FieldTrgt as Field, +/    /+ &effDt as Date, +/    /+ &refRecName as String, +/    /+ &refFieldName as String, +/    /+ &refDescrFieldName as String +/

Local string &value, &sqlCond;    If All(&FieldSrc.Value) Then       Local Record &rec = CreateRecord(@("Record." | &refRecName));       Local Field &fld = &rec.GetField(@("FIELD." | &refFieldName));

If All(&refFieldName) Then          &sqlCond = " AND (upper(" | &refFieldName | ") = upper(:3) ";       End-If;       If All(&refDescrFieldName) Then          If All(&sqlCond) Then             &sqlCond = &sqlCond | " OR upper(" | &refDescrFieldName | ") = upper(:3) )";          Else             &sqlCond = " AND upper(" | &refDescrFieldName | ") = upper(:3) ";          End-If;       Else          If All(&sqlCond) Then             &sqlCond = &sqlCond | "  )";          End-If;       End-If;           &sqlCond = "SELECT " | &refFieldName | " FROM %Table(:1) A WHERE  %EffdtCheck(:1 B, A, %Datein(:2)) AND A.EFF_STATUS = 'A' " | &sqlCond;

SQLExec(&sqlCond, &rec, &effDt, &FieldSrc.Value, &FieldTrgt.Value);       %This.ErrorIfNoFieldValue(&FieldSrc, &FieldTrgt);    End-If;   end-method;

Error Handler

method ErrorIfNoFieldValue    /+ &FieldSrc as Field, +/    /+ &FieldTrgt as Field +/    Local string &fieldDescr;    Local string &sError;    If None(&FieldTrgt.Value) Then       &isError = True;          &sError = &FieldSrc.Name | ": " | MsgGetText(30k, 1, "Field Value Not Valid");    End-If; end-method;