Student Faculty Information System

BROWSERS AND PEOPLESOFT – UPDATED (IE8) – as of November 2010

If you have upgraded your browser to MS Internet Explorer 8 (or plan to), note that there may be an effect when you attempt to access SFIS/HRMS from your Portfolio.  If you are unable to get into PeopleSoft, try this:  Right click on SFIS/HRMS v8.9 and when the menu appears, select Open Link in New Tab, and you’ll see a new tab open in your browser.

Please note that Internet Explorer 8 is not a supported web browser for PeopleSoft and consequently there may be other unexpected results when using that version of the browser.

IE 6 and 7 are on the list of supported browsers for PCs, as is Firefox through version 3.0.  For further information on browsers and PeopleSoft, click here.

Tags: Browsers, Microsoft Excel

This article highlights the use of runtime prompts when creating criteria in Query.   One key benefit of prompts is that when you run the query, you can change the criteria as necessary, in various combinations as required, to achieve the desired results.

An excerpt from PeopleBook:  PeopleSoft Query explains the use of prompts as follows:

Adding a prompt lets you further refine a query when you run it…  When you run a query with a prompt, a prompt page requests the required value.  All date, time and datetime prompt fields are required fields when running Query.  Enter the value into the field. The query uses the value that you enter as the comparison value for the criterion that included the prompt.  If the field for which you are prompting has an associated prompt table (even if it is the Translate table), the Edit Table drop-down list box shows its name.

If you have never worked with prompts, you can start by reviewing Lesson 9:  Adding Runtime Prompts to a Query in Intro to PS Query – I and II.

One method of working with prompts that increases your flexibility even more is to incorporate a wild card into your prompt.  A wildcard enables the user to return some or all data for a specified criterion.  A demonstration of using wildcard can be found in Using a Wildcard for Prompt Criteria.

Other places to read about prompts can be found on the Prompts in Query page.

Tags: Criteria, Demonstration, Expression, Prompts, Wes Function

Here is some updated documentation as well as new pages for the month of August. 

• Browsers and PeopleSoft
• Query Expressions
• Query Tip Sheet
• Query Troubleshooting
• Query – Wes Functions Chart
• Today’s Date in Query

Tags: Browsers, Dates, Demonstration, Effective Dates, Expression, Microsoft Excel, Wes Function

If you enter the word “date” or the phrases “effective date” or “effective dating” in the Search box over to the right, you will receive a number of hits.

This is because dates are an important element when entering information into the pages in SFIS and when extracting information through Query.

• The concept of the date in SFIS encompasses birth dates, admit terms, academic years, class year, today’s date, months, calendar years, and so on, and also the Effective Date.
• Dates can be entered through a process or manually entered.
• Dates can be used in Query to run computations over spans of time; they can serve to exhibit history; using expressions and Wes Functions, dates can be displayed in numerous formats.
• Note that the proper use of dates has an impact throughout the SFIS system.

When working with PeopleSoft, it is important to understand the use of Effective Dates.  As described in PeopleBooks,

“PeopleSoft’s effective-dating logic enables you to maintain an accurate history of information in the database.  Effective dating allows you to store historical data, see changes in your data over time, and enter future data… When you enter new information that is related to existing data…, you do not want to lose or overwrite the data already stored in the database. To retain history, add a data row identified by the date when the information goes into effect:  the effective date. You can use the information to look at what has happened up to now and plan for the future.”

PeopleBooks goes on to explain the use of Action Options with Effective Dating – Update/Display, Correct History and Include History.

With Query, you can use dates in a variety of ways:

• Derive information about specified fields between two dates – and include a prompt.
• Display any date in a field, including Effective Dates, in a desired format through the use of to_char in an expression; examples would be the year, month-day, mm/dd/yyyy, etc., as number or words
• Similarly, time can be formatted as desired, for example, as AM or PM or military time
• Display today’s date in many formats with the use of expressions and Wes Functions (SYSDATE).
  • Note that, unless you change the format, the SYSDATE will display as follows:  30-JUL-10
• Extract parts of a date in an expression through the use of substr which has this form:  SUBSTR(DATEFIELD,X,Y).   (Note that substr can be upper or lower case).
  • Example:  The date appears as 2010-05-30.  SUBSTR counts from X position and returns Y characters.  So for example –
    • SUBSTR(DEGREE_DT,1,7) returns 2010-05 
    • substr(DEGREE_DT,3,8) returns 10-05-30
• Return the Class Year with a Wes Function – this is based on the EMPLID
• In addition to Class Year, there are a number of variations on utilizing or returning dates through the use of Wes Functions, most notably SYSDATE (current date), but also application start date, current term, undergraduate term, first year inquiry term, admit term, program action date, and registration term being a few examples.

• Unique to Effective Dates:
  • When you first join a record that has effective dating, you will get this message:  An effective date criteria has been automatically added for this effective dated record (139,60).
    • What is important to know when you see this message is that the database will automatically give you the very latest dated row for the criteria.
  • Through Criteria Condition Types, a number of Effective Date situations can be shown
  • If necessary, for certain reasons, Effective Dates can be deleted from a query.
  • Using Effective Dates in a subquery requires care.
    • See Advanced Concepts Lesson 18 for more information.
  • When doing table joins on effective dated information, you will rarely modify the criteria that PeopleSoft automatically creates.
    • If you do need to change the criteria, be certain you’re changing the correct rows of criteria.
  • Translate tables are effective-dated, so you must select which effective date to use for them.
    • For most tables, PeopleSoft Query defaults to the current date, meaning that it uses the currently active list of Translate table values.
    • However, if the table you’re querying is also effective-dated, PS Query uses the value in the EFFDT field for a row.

Tags: Dates, Demonstration, Effective Dates, Expression, Query - Advanced, Subquery, Translate Values, Wes Function