Blogs

TRAG-PS Query Drilling URLs

By Dede Young posted 07-12-2023 08:59 AM

  

What is a drilling URL?  It is a PeopleSoft Query expression type that creates code to drill down to another object.  Drilling URLs are defined by selecting a drill-down query, a menu-component-page combination, URL of choice, etc.  There are currently seven types of Drilling URLS.

1. Query URL - Drills down to another existing query
2. External URL - Drills down to an external website; entering the external URL will build drilling URLs in an external URL format, opening the drilling URL within the PeopleSoft system

3. Free Form URL - Drills down to an external website, opens a new window outside of the PeopleSoft System

4. Component URL - Drills down to another component (page) in PeopleSoft
5. Attachment URL - Drills down to an attachment in PeopleSoft
6. Image URL - Drills down to an image in PeopleSoft, generated by a query that contains the image data
7. REST URL - Allows the user to drill down from the query result set, or a report based on the query, into a PeopleSoft defined REST Web Service URL

Each Drilling URL type has different setup requirements.  Some are optional, others necessary.  Each also has an optional ‘Use psc’ checkbox.  Use psc is a content servlet used to define the content type that is used to build drilling URLs.  By default, it is unchecked and will use the portal service (psp) type, unless checked.

Last week, the TRAG published a short article on a specific type of drilling URL expression, the query drilling URL.  Let’s now discuss another type of drilling URL expression, the Component URL. 
 

The Portal Name may differ by institution.  A common value is ‘EMPLOYEE’.  Node Name may also be institution-specific.  Both of these are optional, as well as searching the Content Reference.  However, if the Add Content Reference Link is utilized, the user will not need to find the menu name, market, and component, as it will be populated.

Click the Add Content Reference Link and navigate to the page to which the drilling URL should be pointed, and select that page. This example will drill down to the student ‘program/plan stack’.

Component and Query drilling URLs require the entry of URL Keys.  These are search keys that match the query results to the component or query to which the drilling URL is being pointed. 

Look up the Page.  This is the tab within the Program/Plan component to which the URL will bring the user.  Select STDNT_PROG to be brought to the Student Program tab of the Program/Plan component.  Likewise, if STDNT_PLAN is selected, the user will be brought to the Student Plan tab of the Program/Plan component.



The Menu Action should be selected, the options being different and dependent on the component to which the drilling URL is pointing.  It is also important to note that the action selected will obey the user’s access to that component.

·         Add – opens the target component at the Add a New Value page

o   For the Student Program/Plan component, this will bring the user to the student’s program page, the most recent date-effective row, with no history, and is dependent on the user’s access to add a new value or ‘row’

·         Update – opens the target component at the Find an Existing Value page, or mode

o   For the Student Program/Plan component, this will bring the user to the student’s program page, the most recent date-effective row, with no history, and requires only the lowest level of security to the Student Program/Plan component

·         Update All – opens the target component at the Find an Existing Value page, with the Include History option selected, and requires the user to have access to the include history option

o   This will bring the user to the student’s program page, the most recent date-effective row, with all history available

·         Correction – opens the target component at the Fid an Existing Value page, with the Correct History option selected

o   This will bring the user to the student’s program page, the most recent date-effective row, with all history available and editable; this requires the highest level of access to the component

o   Users with Correction access will have correction ability, regardless of the Menu Action selected 


By clicking the Search Keys button, the list of search fields, or ‘URL Keys’, will be displayed.

As many fields as are necessary to successfully drill down to the student program tab of the program/plan component need to be included as fields in the query.  At minimum, to drill down to the Student Program/Plan component, the EMPLID, ACAD_CAREER, and STDNT_CAR_NBR need to be included as fields in the query and identified in the URL Keys table. 

To identify from which record the fields were added, click the Selection Flag checkbox corresponding to the field, then click the Field Lookup icon.

Select the field to populate the Key Value box, and repeat with all necessary fields.  Discovering which and how many of the URL Keys need to be populated may be accomplished through trial and error.

Once the drilling URL is saved, the extra fields will no longer displayed, and only those with search keys identified will remain.

The Select Field button will display the fields in the query and allows the user to select query result columns to form fieldname and value pairs, and defines the order of the value pair in the URL.  Select Field not be used in this example.

Click the Map Column button to display the query fields and map the drilling URL to one of the fields in the query.

Drilling URL query fields are displayed using the Unique Field Name.  This is where changing that name becomes important.  If the query contains several expressions and the Unique Field Name for those expressions has not been changed, it may be difficult to determine which field is which.  In this example, the component drilling URL will be mapped to the ACAD_PROG field, easily identified in the list.

After mapping to the field, click OK to complete the setup.  The Expression Properties box is displayed, showing the Drilling URL code.  This is not editable within the Expression Text box.

Click OK to complete the drilling URL expression and be sure to save the query.  A similar Component drilling URL is created, drilling down to the Student Plan page of the Program/Plan component, and mapped to the field G.TRNSCR_DESCR, which is the academic plan transcript description, from ACAD_PLAN_TBL.

After the drilling URL expressions are in place, the query can be run.  The Acad Prog and Acad Plan Descr fields appear as hyperlinks.

Clicking on the Acad Prog hyperlink brings the user to the Student Program page within the Student Program/Plan component.

Clicking on the Acad Plan Descr hyperlink brings the user to the Student Plan page of the Student Program/Plan component.

Component drilling URLs can be utilized for a variety of purposes, such as and not limited to the example just discussed, drilling down to the Grade Roster page to generate grade rosters based on query results, or drilling down to Maintain Schedule of Classes to update class information based on query results.

More information regarding Drilling URLs can be found within the HEUG archives, take a look!

0 comments
27 views

Permalink