Order Portal References and Folders Using Sequance Number

Your rating: None Average: 3.5 (2 votes)

To make things easier on users, you are most likely to order portal content references and portal folders in an alphabetical order. To do that, you need to navigate to your portal structure page (Portal Administrator role required), and edit each content reference and folder sequence numbers to be equal, for example, set them all to 100.

When all of your content references are set to be the same number PeopleSoft will automatically sort them alphabetically.

This way of approaching this could be rather time consuming if you are updating 100’s of links. I was wondering if there is a faster way by executing an update statement, after all, what I’m really doing is updating a number in the database.

I ran trace on one of manual attempts mentioned about and found out that PeopleSoft actually perform a series of deletes from “bunch” of tables as well as the actual content reference row from PSPRMSDEFN to then insert a fresh new row with the new sequence number. I however followed a different approach and executed a simple straight forward update statement to get the same result. I have tested this in our development environment and didn’t look like it is having any negative complications. So here is the SQL:

SELECT *
FROM psprsmdefn portal
WHERE portal.portal_reftype = 'C'
 AND portal.portal_name = 'EMPLOYEE'
 AND portal.portal_prntobjname = :folder_name
 AND NOT EXISTS
  (SELECT 'x'
   FROM psprsmsysattrvl spec
   WHERE portal.portal_objname = spec.portal_objname
   AND portal.portal_reftype = spec.portal_reftype
   AND portal.portal_name = spec.portal_name
   AND spec.portal_attr_nam = 'PORTAL_HIDE_FROM_NAV');

I thought limiting the sql to update only components (portal.portal_reftype = 'C') and one folder at time (AND portal.portal_prntobjname = :folder_name) is the best way of approaching this to eliminate any mass unwanted updates! To update folders you need to change portal.portal_reftype = 'C' to portal.portal_reftype = 'F'.

The sql also excludes any links that are marked as “hidden” as they really don’t need to get updated.

If you think there is a better way of doing this, please don’t hesitate to post your comments.

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!