PS Query & SQL

Good Morning -

There are two sections to this response.  First, I have a question that is a bit of a mystery to me, followed by a discussion of how I might find the values for this (or any) field.  

Question

This field, LINE_ACTION, has 18 translate values.  I can see them in all the ways outlined below, BUT they do not show up in Query Manager when I try to apply the field as criteria.  I don't understand why that is.   I thought if a field has translate values associated with it, they would always show up in query manager if  were using them them as criteria.  Does anyone know why they are not showing up as expected?  

Context 

First, it makes sense to define an XLAT (or Translate) value. XLATs link a short, often single-character code to a longer, more descriptive text. This mechanism is primarily used to populate the options you see in drop-down lists and radio buttons throughout the PeopleSoft application. For example, a database table might store the code 'A' to indicate an employee's status. The XLAT table "translates" this code so that a user sees the word "Active" on the screen. 

This information and more can be found in this presentation. How to Speak PeopleTools

Answers

When I am researching this sort of question (what are the values held in a field), here are the steps I follow.  

Finding Field information - If I do NOT have access to AppDesigner

• Query the PSRECFIELD table, searching on the field name.  (In this case LINE_ACTION.)
• Make a note if the field appears in multiple tables. This is for information only.  It might be valuable to know where else a field appears. 
• In this case, LINE_ACTION appears in 18 tables (at Duke, anyway) 
• Review Translates- 3 ways 
  • Query the XLATTABLE record.  Query for the field LINE_ACTION field. 
    • At Duke, there are 20 values for the field. 
  • Alternatively, in the PeopleSoft PIA, navigate to the Translate Values page.  This lets you review the the the translate for a field via a page.
    • Navigation = PeopleTools> Utilities> Administration> Maintain Translate Values 
  • In Query Manager, add the record with your field.  (In this case, ITEM_LINE_SF.) Add criteria using the LINE_ACTION field. Use the lookup function (magnifying glass) to review the translates, if they are there.  

Finding Field information - If I have access to AppDesigner

• Open AppDesigner
• Click Open object icon.  Select "Field". Type in field name (LINE_ACTION). 
• Once the field is pulled back, click Properties (aka, "The man with the hand" )
  • Once Translate values are on the second tab in Properties.
• You can also search for Definition References under the Edit menu.  This lets you see what other records the field might show up in. 

------------------------------
Tom Johnson
Sr Business Systems Analyst
Duke University
tom.johnson@duke.edu
"None of us is as smart as all of us"
------------------------------

Good Morning -

There are two sections to this response.  First, I have a question that is a bit of a mystery to me, followed by a discussion of how I might find the values for this (or any) field.  

Question

This field, LINE_ACTION, has 18 translate values.  I can see them in all the ways outlined below, BUT they do not show up in Query Manager when I try to apply the field as criteria.  I don't understand why that is.   I thought if a field has translate values associated with it, they would always show up in query manager if  were using them them as criteria.  Does anyone know why they are not showing up as expected?  

Context 

First, it makes sense to define an XLAT (or Translate) value. XLATs link a short, often single-character code to a longer, more descriptive text. This mechanism is primarily used to populate the options you see in drop-down lists and radio buttons throughout the PeopleSoft application. For example, a database table might store the code 'A' to indicate an employee's status. The XLAT table "translates" this code so that a user sees the word "Active" on the screen. 

This information and more can be found in this presentation. How to Speak PeopleTools

Answers

When I am researching this sort of question (what are the values held in a field), here are the steps I follow.  

Finding Field information - If I do NOT have access to AppDesigner

• Query the PSRECFIELD table, searching on the field name.  (In this case LINE_ACTION.)
• Make a note if the field appears in multiple tables. This is for information only.  It might be valuable to know where else a field appears. 
• In this case, LINE_ACTION appears in 18 tables (at Duke, anyway) 
• Review Translates- 3 ways 
  • Query the XLATTABLE record.  Query for the field LINE_ACTION field. 
    • At Duke, there are 20 values for the field. 
  • Alternatively, in the PeopleSoft PIA, navigate to the Translate Values page.  This lets you review the the the translate for a field via a page.
    • Navigation = PeopleTools> Utilities> Administration> Maintain Translate Values 
  • In Query Manager, add the record with your field.  (In this case, ITEM_LINE_SF.) Add criteria using the LINE_ACTION field. Use the lookup function (magnifying glass) to review the translates, if they are there.  

Finding Field information - If I have access to AppDesigner

• Open AppDesigner
• Click Open object icon.  Select "Field". Type in field name (LINE_ACTION). 
• Once the field is pulled back, click Properties (aka, "The man with the hand" )
  • Once Translate values are on the second tab in Properties.
• You can also search for Definition References under the Edit menu.  This lets you see what other records the field might show up in. 

------------------------------
Tom Johnson
Sr Business Systems Analyst
Duke University
tom.johnson@duke.edu
"None of us is as smart as all of us"
------------------------------

Thanks Mark.  That is super good to know. 

Good Morning -

There are two sections to this response.  First, I have a question that is a bit of a mystery to me, followed by a discussion of how I might find the values for this (or any) field.  

Question

This field, LINE_ACTION, has 18 translate values.  I can see them in all the ways outlined below, BUT they do not show up in Query Manager when I try to apply the field as criteria.  I don't understand why that is.   I thought if a field has translate values associated with it, they would always show up in query manager if  were using them them as criteria.  Does anyone know why they are not showing up as expected?  

Context 

First, it makes sense to define an XLAT (or Translate) value. XLATs link a short, often single-character code to a longer, more descriptive text. This mechanism is primarily used to populate the options you see in drop-down lists and radio buttons throughout the PeopleSoft application. For example, a database table might store the code 'A' to indicate an employee's status. The XLAT table "translates" this code so that a user sees the word "Active" on the screen. 

This information and more can be found in this presentation. How to Speak PeopleTools

Answers

When I am researching this sort of question (what are the values held in a field), here are the steps I follow.  

Finding Field information - If I do NOT have access to AppDesigner

• Query the PSRECFIELD table, searching on the field name.  (In this case LINE_ACTION.)
• Make a note if the field appears in multiple tables. This is for information only.  It might be valuable to know where else a field appears. 
• In this case, LINE_ACTION appears in 18 tables (at Duke, anyway) 
• Review Translates- 3 ways 
  • Query the XLATTABLE record.  Query for the field LINE_ACTION field. 
    • At Duke, there are 20 values for the field. 
  • Alternatively, in the PeopleSoft PIA, navigate to the Translate Values page.  This lets you review the the the translate for a field via a page.
    • Navigation = PeopleTools> Utilities> Administration> Maintain Translate Values 
  • In Query Manager, add the record with your field.  (In this case, ITEM_LINE_SF.) Add criteria using the LINE_ACTION field. Use the lookup function (magnifying glass) to review the translates, if they are there.  

Finding Field information - If I have access to AppDesigner

• Open AppDesigner
• Click Open object icon.  Select "Field". Type in field name (LINE_ACTION). 
• Once the field is pulled back, click Properties (aka, "The man with the hand" )
  • Once Translate values are on the second tab in Properties.
• You can also search for Definition References under the Edit menu.  This lets you see what other records the field might show up in. 

------------------------------
Tom Johnson
Sr Business Systems Analyst
Duke University
tom.johnson@duke.edu
"None of us is as smart as all of us"
------------------------------

Hi Tom,

I checked the record definition of ITEM_LINE_SF in our environment through app designer, and it shows column LINE_ACTION is not set up to edit against the XLAT table.  Maybe this is why the translates are not showing up in Query Manager?  Not absolutely sure though if this is how it was delivered.  Is this how it is defined in your system too?

Good Morning -

There are two sections to this response.  First, I have a question that is a bit of a mystery to me, followed by a discussion of how I might find the values for this (or any) field.  

Question

This field, LINE_ACTION, has 18 translate values.  I can see them in all the ways outlined below, BUT they do not show up in Query Manager when I try to apply the field as criteria.  I don't understand why that is.   I thought if a field has translate values associated with it, they would always show up in query manager if  were using them them as criteria.  Does anyone know why they are not showing up as expected?  

Context 

First, it makes sense to define an XLAT (or Translate) value. XLATs link a short, often single-character code to a longer, more descriptive text. This mechanism is primarily used to populate the options you see in drop-down lists and radio buttons throughout the PeopleSoft application. For example, a database table might store the code 'A' to indicate an employee's status. The XLAT table "translates" this code so that a user sees the word "Active" on the screen. 

This information and more can be found in this presentation. How to Speak PeopleTools

Answers

When I am researching this sort of question (what are the values held in a field), here are the steps I follow.  

Finding Field information - If I do NOT have access to AppDesigner

• Query the PSRECFIELD table, searching on the field name.  (In this case LINE_ACTION.)
• Make a note if the field appears in multiple tables. This is for information only.  It might be valuable to know where else a field appears. 
• In this case, LINE_ACTION appears in 18 tables (at Duke, anyway) 
• Review Translates- 3 ways 
  • Query the XLATTABLE record.  Query for the field LINE_ACTION field. 
    • At Duke, there are 20 values for the field. 
  • Alternatively, in the PeopleSoft PIA, navigate to the Translate Values page.  This lets you review the the the translate for a field via a page.
    • Navigation = PeopleTools> Utilities> Administration> Maintain Translate Values 
  • In Query Manager, add the record with your field.  (In this case, ITEM_LINE_SF.) Add criteria using the LINE_ACTION field. Use the lookup function (magnifying glass) to review the translates, if they are there.  

Finding Field information - If I have access to AppDesigner

• Open AppDesigner
• Click Open object icon.  Select "Field". Type in field name (LINE_ACTION). 
• Once the field is pulled back, click Properties (aka, "The man with the hand" )
  • Once Translate values are on the second tab in Properties.
• You can also search for Definition References under the Edit menu.  This lets you see what other records the field might show up in. 

------------------------------
Tom Johnson
Sr Business Systems Analyst
Duke University
tom.johnson@duke.edu
"None of us is as smart as all of us"