Pages with Secondary Pages That Use a Field

No votes yet

Here is a question that was asked on the forum and thought it will be of a value to add it to the PeopleSoft handbook to keep for future references. The question was:

Is there a way to determine all the pages with a secondary page with a specific field ?
For e.g. if I am looking for emplid field and determine that the emplid field is used on say 5 secondary pages. I now need to tie those secondary pages to the pages .
Is there a sql that can be run to identify such pages ?

Here is how you accomplish the above:

The 1st select will get all pages that have secondary pages on them (thus the fieldtype <> '11' - fieldtyp 11 means it is a subpage and you want secondary pages) and the 2nd select will look for a specific field on secondary pages found.

Please test it and let us know if it works!

WITH my_data AS
     (SELECT   pnlname, fieldtype, subpnlname
          FROM pspnlfield
         WHERE (subpnlname <> '' OR subpnlname <> ' ') AND fieldtype <> '11'
      GROUP BY pnlname, fieldtype, subpnlname)
SELECT   my_data.pnlname, my_data.subpnlname
    FROM my_data, pspnlfield FIELD
   WHERE my_data.subpnlname = FIELD.pnlname AND FIELD.fieldname = 'Your-field'
GROUP BY my_data.pnlname, my_data.subpnlname
ORDER BY my_data.pnlname, my_data.subpnlname;

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!