Using Hyperlinks in Queries to Drill Down to Individual Values

Your rating: None Average: 2 (1 vote)

I came across a PeopleSoft posting written by Rob's PeopleSoft Blog and could not help but to post it on my blog. It is a neat trick and I'm sure will come handy in many occasions!

How often do you see your users running queries and then writing down values that are returned by the query so that they can then navigate to the appropriate page and view the underlying details of those vouchers, journals, vendors, etc.? Wouldn't it be nice if they could just click on a hyperlink embedded within the query results and have the page pop up in a separate browser window instead?

I attended a session at Oracle OpenWorld at which a consultant demonstrated how hyperlinks can be used in PS Query to drill down to individual values that are returned by a query. There wasn't a lot of detail given on how to do this, but once I got home I decided to give it a shot, and within an hour I had it working. This is one of the coolest features I've ever seen in PS Query, and I'm sure your users will love it.

Here's how you do it:

Copy the URL - Open a browser window and sign into the instance in which the query will be built (or modified) and navigate to the search page for the data to be retrieved (e.g., vouchers, vendors, journals, etc.). Call up a value in update mode and then click on the "http" icon in the upper right-hand corner of the page to copy the entire URL to the clipboard.

Store the copied URL-Paste the contents of the clipboard into a text editor. Here is an example from a vendor record that I retrieved: http://ps3app01.usi.net/psp/B88INST/EMPLOYEE/ERP/c/MAINTAIN_VENDORS.
VNDR_ID.GBL?AR_NUM=&NAME1=ALISO%20VIEJO%20GOLF%20CLUB&SETID
=SHARE&VENDOR_ID=0000000002&VENDOR_NAME_SHORT=ALI-005&
VENDOR_PERSISTENCE=R&PAGE=VNDR_ID1_SUM

Add an Expression to your query-Create your query as you normally would and then add an expression of type "Long Character" in which you will store the modified URL.

Modify the URL- This is tricky, so be careful. The URL as-is would return only the value that you retrieved, so you'll need to make it dynamic by deleting the hard-coded values (e.g., &BUSINESS_UNIT=001) and concatenating together the various pieces of the URL and the appropriate field names from the query. Here's what the above URL looks like after making the changes: 'http://ps3app01.usi.net/psp/B88INST/EMPLOYEE/ERP/c/MAINTAIN_VENDORS.
VNDR_ID.GBL?&NAME1=' || A.NAME1 || '&SETID=' || A.SETID || '&VENDOR_ID=
' || A.VENDOR_ID || '&VENDOR_NAME_SHORT=' || A.VENDOR_NAME_SHORT
|| '&VENDOR_PERSISTENCE=' || A.VENDOR_PERSISTENCE || '&PAGE=VNDR_ID1_SUM'

Add the URL to the expression-Add the URL to the expression and then save and run the query from the Web to an ouput of "HTML". Once the results appear, click on the hyperlink and it should open a new browser window and call up the value for the row that you have clicked. If it doesn't, you'll have to trouble-shoot your query. My vendor query did not work the first time I tried it because I had left the hard-coded value for VENDOR_NAME_SHORT in the URL and that value did not match the value for VENDOR_ID in the row that I had clicked. Here's an example of one URL from the ouput from my query: http://ps3app01.usi.net/psp/B88INST/EMPLOYEE/ERP/c/MAINTAIN_VENDORS.
VNDR_ID.GBL?&NAME1=JOHNDOE&SETID=SHARE&VENDOR_ID=0000000001
&VENDOR_NAME_SHORT=JOHNDOE-001&VENDOR_PERSISTENCE
=R&PAGE=VNDR_ID1_SUM

The hyperlink will also work from within an Excel spreadsheet if that is the format to which you prefer to run your queries. You are not limited to using hyperlinks to PeopleSoft pages. If you've got a live Internet connection, you can link to any page via the same method. For example, here's an expression to perform a Google search on the value that appears in VENDOR.NAME1 from the query that I created: 'http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=
GGLD,GGLD:2004-21,GGLD:en&q=' || A.NAME1

Good luck with your query-building. This is a powerful tool that will come in handy in many occasions!

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!