Get Edit Tables Behind a Record Fields

Your rating: None Average: 3.6 (5 votes)

A straight forward SQL to get edit tables behind field(s) for a specific record. The SQL execludes any edit tables that start with "%" as those are dynamic edit tables with values populated by PeopleCode at run time and thus will not be of a good use in this query.

SELECT R.FIELDNAME 
 , R.EDITTABLE 
  FROM PSRECFIELDDB R 
  , PSDBFIELD F 
 WHERE R.RECNAME = :RecordName
   AND SUBSTR(R.EDITTABLE,1,1) <> '%'  
   AND R.EDITTABLE <> ' '  
   AND R.FIELDNAME = F.FIELDNAME
   AND F.FLDNOTUSED = 0;

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
Jason G's picture
Jason G (not verified)
What is the purpose of PSDBFIELD.FLDNOTUSED ?

It's been bugging me for a while, do you know what is the purpose of the field FLDNOTUSED on PSDBFIELD? I know it makes the words "Not Used" show up in the Key column in App Designer, but what is the actual function, and how is the field set? it is disabled at the field object level in App Designer.

Thanks

Lepa's picture
User offline. Last seen 1 day 5 hours ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Get Edit Tables Behind a Record Fields

Per PeopleSoft:

Quote:
This field indicates whether a field is being actively used in a table or whether it is just there to avoid rebuilding the table and removing it. The default is 0, which means the field is used.

I can't find an example about such a field though.

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Lepa's picture
User offline. Last seen 1 day 5 hours ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Get Edit Tables Behind a Record Fields

Here is more info on the "Not Used" field property! Fields marked as "Not Used" will have the following characteristics:
• Skipped as part of the unique index.
• Ignored in Query.
• Ignored in nVision
• Unavailable to users in search pages or list boxes if it is a search key or list box item.

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Jason G's picture
Jason G (not verified)
Re: Get Edit Tables Behind a Record Fields

I wonder if it was a way for Psoft to deprecate a field yet avoid a massive ALTER during upgrades? It's on high-volume tables like VCHR_%. We have millions of rows in those tables and an alter would take forever, if it didn't completely bring the db to its knees.

Thanks for the info!