Create and Format a Query

In this topic you will learn how to Create and Format a Query.

You start by selecting a record. Since you are interested in generating a list of vendor information, it makes sense to first determine what vendor tables (vendor records) are available.

You must enter all or part of the Record Name into the Search By field. The wildcard (%) or the Advanced Search link may be utilized to help focus your search.

For this example, the VENDOR_ADDR - Supplier Address record will be used.

NOTE: Once you select the Add Record option, the system will automatically take you to the Query tab where you will view the available field options for the Vendor_ADDR record.

In this example, an Effective Date message will display. This occurs anytime an effective dated record is selected. This allows you to modify the date criteria of the record information. In this case, all data will be retrieved with effective dates prior to the current date.

Field names do no default in alphabetical order. The system provides a Sort fields alphabetically option to make searching for fields easier.

The fields display in alphabetical order. By clicking the Sort fields alphabetically button again, the system returns the fields to the original order.

The Check All button is used if you want to use all the fields associated with the record in your query.

The Uncheck All button deselects all of the fields associated with the record.

When a field is selected for use in the query, and the query is run, the system returns all the data for the field. You can narrow your results by specifying criteria for the field. The Use as Criteria (funnel) option allows you to add criteria for a particular field.

For example if you selected the Vendor ID field for your query, all vendors will be retrieved from the database. If you only wanted to retrieve vendors who have an Active status, you could specify this by using criteria.

From the Edit Criteria Properties page, you can Choose Expression, Choose Record or Field, or Define Constant.

NOTE: See Exercise 4 for information on the Using as Criteria feature.

Click the Sort fields alphabetically button to make field selection easier.

After all fields have been selected, click the Fields tab to view the selected fields collectively.

The Reorder/Sort button allows you to change the order of the fields and, therefore, the order of your query result columns.

Reorder columns by entering the column number in the New Column field. If a field is left blank, that column will remain as originally indicated.

In this example, NAME1 is the fifth (5th) column on your query results. Change NAME1 to column two (2).

You must save your selections prior to running the query.

Query names must be entered in ALL CAPS. Names can be up to thirty (30) characters long. No spaces or special characters (e.g., &, $, #, etc.) may be used in the name. An underscore can be used to separate words used in the query name.

A Description may be added. The Description is thirty (30) characters long and is not case sensitive.

The Query Definition box is used to enter a longer description of the query. The use of Query Definition is optional but recommended. You can also enter the date you created your query in the Query Definition box.

The Run tab allows you to preview your query.

Click the Translate Value Long option.

Just as you can change the order in which fields (columns) display in your results, you can also Sort the data results for multiple fields.

Click the Reorder/Sort button.

All changes are complete.

Now that all changes have been made to your query, you will run your query from the Query Manager page.

Note that when searching, any private queries associated with your user id will display first in your search results.

Click the VEND_LIST HTML link to run your query.

You can download your results into an Excel spreadsheet if you wish to manipulate your query results.

Click the Excel Spreadsheet link.

Once in Excel, you may manipulate the data as desired by clicking the Enable Editing button.

Click the Close Report button to close your Excel spreadsheet.

Click the Close Report button to close the HTML spreadsheet.

If desired, you may add the VENDOR_LIST query to My Favorite Queries for easy access in the future.

Click the VEND_LIST Checkbox option.

The system takes you to the Query tab.

From the Query tab you can select individual fields within a record. The fields you select will display as the columns on your report.

Key symbols identify fields found in multiple records. Key fields provide a link when joining multiple records.

The fields display on the Fields tab in the order in which they were selected. Each field represents a column on your spreadsheet, so this also the order they will display in your report. Often fields will need to be reordered to make query results easier to read.

When you save the first time, you will name your query. Any queries you create must be saved as Private queries. If you modify a Public query, and wish to save the changes, it must be saved a Private query.

The Folder field is not used.

The Query Type is always User.

The Owner option defaults as Private and will remain as defaulted so that your query cannot accidentally be saved as a Public query.

The Fields are reordered based on the changes you made on the Reorder/Sort panel.

The Heading Text displays the text used for each field (column) on your report. Field titles can be changed using the Edit feature.

In this example you will change the VENDOR_ID Heading Text from Supplier to Supplier ID.

Click the VENDOR_ID - Supplier ID Edit button to change the Heading Text (column title).

NOTE: The Unique Name Field will always remain as defaulted.

The results have been retrieved for your query run. The first 100 options display. You can use the arrows to move between pages, or use the Last and First links to view the Last 100 options and return to the First 100 options.

NOTE: The number of result rows downloaded into Excel is limited.

The query results display in HTML format. Data cannot be manipulated in HTML format. You can download the results to an Excel spreadsheet if you wish to make changes to the query results in a spreadsheet. Examples would be filtering data, grouping, or performing subtotals.

The system defaults to RFT Short (Record Field Text Short). The RFT Short column title will be up to 10-characters long.

NOTE: The Heading Text field displays the selected column title. In this example, the column title would display as Vendor since the RFT Short options is selected.

The RFT Long displays up to a 30-character column title.

The Text option displays the column title as it is entered in the Heading Text field.

No Heading will result in the column title being blank.

A value in the XLAT column indicates a Translate Value may be used. The Translate Value is an abbreviated code for a field's description.

In this example, The EFF_STATUS has a XLAT value of "N". The XLAT column can contain one of the following values:

N (None) - Displays in the query results as a single character value and assumes a current date logic (i.e. Effective Date is equal to current date);

S (Short) - Displays the field description in the query results as a 10-character value and uses a specified effective date logic (i.e. Effective Date is equal to or less than current date; or

L (Long) - Displays the field description in the query results as a 30-character value and uses a specified effective date (i.e. Effective Date is equal to or less than current date).

The EFF_STATUS query field now displays an "L" in the XLAT column. By changing the Translate Value from "N" to "L", the query results will display "Active" instead of an "A", or "Inactive" instead of an "I" in the query results Status column.

In this example, the XLAT value for the Effective Status field is "N", so the value displayed in the query results for this column will display as a single letter in your query results:

A = Active or

I = Inactive.

Click the button to the right of the Action field.

Click the Add to Favorites option.

NOTE: Your security permissions will determine the options available in the Action list.

Your query is added to My Favorite Queries and displays in the My Query Favorites section at the bottom of the page. When you next navigate to Query, the My Favorite Queries section will display automatically on the Query Manager or Query Viewer Search page. You will not need to use the Search By option to run this query in the future.

Currently your results display by Vendor ID (Supplier ID) in ascending order. You will use the New Order By fields on the right side of the table to sort your query results.

In this example, you will sort results by EFF_STATUS.

If you wish to sort your results in descending order, check the box in the Descending checkbox for the desired field. If you select "Descending", the D1 will display in the Ord column on the Field tab to denote results will display in descending order.

The Ord column now displays a "1" for the EFF_STATUS field.

You query results will now sort by whether the vendor is Active or Inactive.

NOTE: It is recommended the query be saved when any changes are made. The Save As option will be used when first saving your query so that a name can be assigned.

For training purposes only, you will forgo saving the query at this time.

PeopleSoft stores the data entered into the system in tables. The tables are comprised of rows (records) and columns (fields). You will select the various rows (records) and columns (fields) you want the system to retrieve to create your query. When you run your query, the data associated with the selected records and fields will display in your report.

In this example you will create a query that generates a list of vendor names, addresses and current status (e.g. Active or Inactive)

The Find an Existing Record page displays.

Across the top of the page are nine tabs that can be used in creating or amending a query. The system defaults you into the Records tab.

A list of 20 record options display.

If you are unsure of which record to use, you can click a record's Show Fields link to view the fields that comprise the record.

A list of fields displays for your review.

If the fields you are interested in are not listed, simply return to your Search Results and click on the Show Fields link for another record.

Joins are used when creating queries using more than one record. Joins allow you to retrieve data from multiple tables, but present it as if it came from one table. Three types of joins are used by LSUHSC in PeopleSoft: Hierarchy Join, Standard (Any) Join and Related Record Join.

NOTE: Joins are not used when creating a single record query.

NOTE: See Exercise 5 - Hierarchy Joins for additional information.

Selecting Fields

Select a field by clicking the checkbox to the left of the Field Name.

For this example the following fields will be selected: Vendor ID, Effective Status, Name1, Address1, City, State, and Postal.

You will use the New Column fields, on the left side of the table, to change the order of your query result columns. The New Order By column, on the right side of the table, will be used to sort your data.

VENDOR_ID will remain as column 1, so you will not need to make an entry for this field.

The Heading Text for the VENDOR_ID field has changed from Supplier to Supplier ID.

The Effective Date for Short/Long defaults as Current Date and will remain as defaulted.

The Status for each Vendor has changed from an "A" or "I" to "Active" or "Inactive".

NOTE: A message displays in red on Line 1 stating the query results are too large and not all results were downloaded.

Create and Format a Query

This completes Create and Format a Query.

You start by selecting a record. Since you are interested in generating a list of vendor information, it makes sense to first determine what vendor tables (vendor records) are available.

You must enter all or part of the Record Name into the Search By field. The wildcard (%) or the Advanced Search link may be utilized to help focus your search.

Reorder columns by entering the column number in the New Column field. If a field is left blank, that column will remain as originally indicated.

In this example, NAME1 is the fifth (5th) column on your query results. Change NAME1 to column two (2).

A Description may be added. The Description is thirty (30) characters long and is not case sensitive.

NOTE: The Unique Name Field will always remain as defaulted.

Click the Add to Favorites option.

Currently your results display by Vendor ID (Supplier ID) in ascending order. You will use the New Order By fields on the right side of the table to sort your query results.

In this example, you will sort results by EFF_STATUS.

In this example you will create a query that generates a list of vendor names, addresses and current status (e.g. Active or Inactive)

VENDOR_ID will remain as column 1, so you will not need to make an entry for this field.

Click the Main Menu button.

Click the Reporting Tools menu.

Click the Query menu.

Click the Query Manager menu.

Click the Create New Query link.

Enter the desired information into the Description field. Enter

a valid value

e.g.

"VEN"

Click the Search button.

Click the VENDOR_ADDR - Supplier Address Add Record link.

Click the OK button.

Click the Sort fields alphabetically button.

Click the Check All button.

Click the Uncheck All button.

Click the VENDOR_ID - Supplier ID Use as Criteria button.

Click the Cancel button.

Click the VENDOR_ID - Supplier ID Select option.

Click the Sort fields alphabetically button.

Click the ADDRESS1 - Address Line 1 Select option.

Click the CITY - City Select option.

Click the EFF_STATUS - Status as of Effective Date Select option.

Click the NAME1 - Supplier Name Select option.

Click the POSTAL - Postal Code Select option.

Click the STATE - State Select option.

Click the Fields tab.

Click the Reorder / Sort button.

Enter the desired information into the NAME1 - Supplier Name New Column field. Enter

a valid value

e.g.

"2"

Enter the desired information into the ADDRESS1 - Address Line 1 New Column field. Enter

a valid value

e.g.

"3"

Enter the desired information into the CITY - City New Column field. Enter

a valid value

e.g.

"4"

Enter the desired information into the STATE - State New Column field. Enter

a valid value

e.g.

"5"

Enter the desired information into the POSTAL - Postal Code New Column field. Enter

a valid value

e.g.

"6"

Enter the desired information into the EFF_STATUS - Status as of Effective Date New Column field. Enter

a valid value

e.g.

"7"

Click the OK button.

Click the Save As link.

Enter the desired information into the Query field. Enter

a valid value

e.g.

"VEND_LIST"

Enter the desired information into the Description field. Enter

a valid value

e.g.

"VENDOR ADDRESS LIST"

Enter the desired information into the Query Definition field. Enter

a valid value

e.g.

"THIS IS A LIST OF VENDOR ADDRESSES"

Click the OK button.

Click the Fields tab.

Click the EFF_STATUS - Status as of effective Date Edit button.

Click the Long option.

Click the OK button.

Click the Fields tab.

Click the Reorder / Sort button.

Enter the desired information into the EFF_STATUS - Status as of Effective Date New Order By field. Enter

a valid value

e.g.

"1"

Click the OK button.

Click the Save button.

Click the Return To Search button.

Enter the desired information into the Find an Existing Query field. Enter

a valid value

e.g.

"VEND"

Click the Search button.

Click the VEND_LIST HTML link.

Click the Excel SpreadSheet link.

Click the Close Report button.

Click the VEND_LIST Select option.

Click the VENDOR_ID - Supplier ID Edit button.

Click the RFT Long option.

Click the OK button.

Click the NAME1 - Supplier Name Edit button.

Click the Text option.

Enter the desired information into the Heading Text field. Enter

a valid value

e.g.

"Supplier Name"

Click the OK button.

Click the Address1 - Address Line 1 Edit button.

Click the OK button.

Click the STATE - State Edit button.

Click the Text option.

Enter the desired information into the Heading Text field. Enter

a valid value

e.g.

"State"

Click the OK button.

Click the Text option.

Enter the desired information into the Heading Text field. Enter

a valid value

e.g.

"Address"

Click the Action list.

Click the Add to Favorites list item.

Click the Go button.

Click the Open button.

Click the VENDOR_ADDR - Supplier Address Show Fields link.

Click the Return button.

Click the Down scrollbar.

Click the UP scrollbar.