Query Tool

Owned by Heather Smith
Last updated: Nov 05, 2024
10 min read

Introduction

In the Regent Query Tool, users define search criteria and the system identifies and returns student records from across the population that meet the entered criteria.

Within the Regent Fund product, clients can use the Query Tool to create a list of students who are eligible to be awarded from a specific fund. Saved queries are used within the Awarding Tool to identify a set of students to be awarded.

Saved queries are used within the Document Assignment Tool to identify a set of students to which selected documents will be assigned.

Overview

The Query Tool is available in the Regent Tools menu for users with the ‘View Query Tool’ permission enabled.

The Query Tool page is comprised of a top section containing a list of created queries in a grid format, as well as the function buttons for the page, and a bottom section containing three subpages where queries are created and executed: General, Editor, and Preview.

Data elements available to be queried are pulled from SBL data, ISIR data, and Regent setup data stored in cache. Configuration options for the data cache are presented in the Query tab of Institution Setup, for users with the correct permission.

Query Tool Page

Query List Grid

The grid at the top of the Query Tool contains the following information related to saved queries and utilizes sorting/filtering capabilities for these values:

  • Active

  • Institution

  • Query Name

  • Created By

  • Created Date

  • Modified By

  • Last Modified Date 

A user may select a query by clicking on the row for that query in the Saved Query list. When a row in the list is selected, metadata for the selected query will be displayed in the ‘General’ tab subpage.

 

Page Buttons

Function buttons at the top of the Query Tool page are Add Query, Refresh, and Copy Query.

Add Query

When a user selects ‘Add Query,’ the system will open the ‘General’ sub-tab in Edit mode for the user to create a new query. The button is displayed for users with ‘Edit Query' permission.

Refresh

The 'Refresh' button updates the list of queries in the grid at the top of the page, to display all queries. For example, if another user has just created and saved a new query, selecting the ‘Refresh’ button will add the new query to the list in the grid.

Copy Query

The ‘Copy Query’ button is displayed for users with ‘Edit Query’ permissions and is only enabled when the user selects an existing query by clicking on a row in the query list grid at the top of the page. Clicking ‘Copy Query’ will open the General subpage for editing with the metadata for the selected query populated except the ‘Query Name’ field will be populated as (“Copy of” <Name of Copied Query>). The user can make changes and save the new query, save the copied query as-is, or cancel without saving.

Query Tool Subpages

Data related to a specific query is created/viewed in four subpages that serve different functions within the tool: General, Editor, Preview, and Results.

General Subpage

If an existing query is selected in the query list grid, information for the query will be presented on the General subpage in View mode. A user with ‘Edit Query’ permission can edit the query by selecting the ‘Edit’ button or delete the query by selecting the ‘Delete’ button on the subpage.

If the ‘Add Query’ button at the top of the page is selected, fields on the General subpage will be enabled for entry of new query data. If a row is selected in the query list grid at the top of the page and the ‘Copy Query’ button is selected, fields on the General subpage will be enabled for editing of copied query data. The General subpage is the first step in defining a new query.

The General subpage contains five fields:

  • Active: This checkbox should be selected to mark a query as ‘Active.’ The field is selected by default.

  • Institution Name: In Edit mode, this field is a dropdown with options for all Institutions for which the user has ‘View Student’ permission at the Institution Permission Level. In View mode, the selected option is displayed.

  • Query Name: In Edit mode, this field is a text box for entry of a user-defined name for the query and is a required field. In View mode, the text entered by the user is displayed.

    • Query Name must be unique within an Institution.

  • Description: In Edit mode, this field is a text box for entry of a user-defined description of the query. This field is not required. In View mode, the text entered by the user is displayed.

  • Regent Query Language (RQL): This field displays the code for the parameters defined on the next subpage, the Editor page, after it has been entered and saved.

General Subpage Buttons

In View mode:

  • Refresh updates the query’s metadata to display the latest query information from the database.

  • Edit changes General page to Edit mode.

  • Delete removes the query from the database.

 

In Edit mode:

  • Save triggers validation tests (such a the check to ensure Query Name is unique) and, if no errors, the system stores the updated query information in the database.

  • Cancel discards the user’s changes.

Editor Subpage

The Editor page is where the parameters for the query are defined. (See Query Data Dictionary for more information.) Users with ‘Edit Query’ permission may create queries to identify groups of students meeting certain criteria by building sets of query criteria, called Selections Sets, in a menu driven user interface. Within the interface, users do the following:

  1. Select from available data elements. See Data Elements Available to Query section below.

  2. Apply rules to the selected data elements as needed with expressions and specified data parameters:

    1. "Equals (==)"

    2. "Not equals (!=)"

    3. "Less than (<)"

    4. "Less than or equal (<=)"

    5. "Greater than (>)"

    6. "Greater than or equal (>=)"

    7. "Between"

    8. "Not Between"

    9. "Starts with"

    10. "Ends with"

    11. "In"

    12. "Not In"

    13. "Contains"

    14. "Not Contains"

    15. "Is empty"

    16. "Is not empty"

  3. Associate rules using the operators AND, OR, and NOT.

  4. Group rules together and associate the different groups using the operators AND, OR. and NOT.

An option near the top of the page presented as a link titled ‘Switch to RQL’ allows the user to leave the editor interface and instead presents them with a text box for entry of RQL code to build the query. However, once selected, it is not possible to switch back to the editor interface within that query.

Once the query is defined in the Editor and the definitions have been saved, clicking on the Preview tab will cause the query to “run” and the list of students matching query criteria will be displayed on the Preview page.

Data Elements Available to Query

Data Elements for querying are available within the following groups: ISIR, UDF, Smart Form, Academic Plan, Student Data, Payment Period, Payment Period Award, Award, Academic Year, Resource, and Documents. Expand the lists below to see available elements within each group.

  • ISIR

    • Verification Status

    • Selected for Verification

    • Primary EFC

    • Dependency Status

    • FAY

    • Student Tax Filer Status

    • Parent Tax Filer Status

    • Housing Plan

    • Date Application Completed

    • Transaction Receipt Date

    • Application Receipt Date

    • Transaction Processed Date

    • Student State of Legal Residence

    • Student Residency Date

    • Parent State of Legal Residence

    • Parent Residency Date

    • Citizenship Status

    • Comment Codes

    • 1st Bachelor's Degree

    • First Bachelor's Degree By 07-01-YYYY?

    • Degree or Certificate Working On

    • Pell Eligibility

    • NSLDS Aggregate Subsidized OPB

    • NSLDS Aggregate Combined OPB

    • Working on Masters Or Doctorate Program

    • Graduate Level ISIR

    • Grade Level in College

    • FTI: FISAP Total Income

    • HS Diploma or Equivalent Received?

    • High School Name

    • High School City

    • High School State 

  • Student Address

    • Address Type

    • County

    • Source Type

    • State

  • Student Data

    • Active

    • Campus External ID

    • Campus Name

    • Citizenship Status

    • Class Rank

    • Disability Status

    • Ethnicity

    • Gender

    • Grad Cumulative GPA

    • Grade Level

    • High School

    • High School City

    • High School Diploma Type

    • High School GPA

    • High School Graduation Date

    • High School State

    • NCES High School Code

    • Program Credential

    • Program External ID

    • Program Name

    • Program Type

    • Race

    • Residency Date

    • Residency Status

    • School Of

    • Site External ID

    • Site Name

    • Specialization

    • State of Legal Residence

    • Undergrad Cumulative GPA

    • User Defined Field

  • Payment Period

    • Term Name

    • Term External ID

    • Program Name

    • Program External ID

    • Program Type

    • Program Credential

    • SAP Status

    • Start Date

    • End Date

    • Enrollment Status

    • Track

    • Enrolled Units

    • Grade Level

    • Anticipated Units

  • Payment Period Award

    • Term Name

    • Term External ID

    • Start Date

    • End Date

    • Fund Budget Name

    • Fund Name

    • Net Amount

    • Gross Amount

    • Paid Amount

    • Award Status

    • FAY

  • Award

    • Net Amount

    • Gross Amount

    • Paid Amount

    • Fund Name

    • FAY

    • Academic Year Start Date

    • Academic Year End Date

    • Loan Period Start

    • Loan Period End

    • Status

  • Academic Year

    • FAY

    • Start Date

    • End Date

    • Unmet Need

    • Unmet Cost

  • Resource

    • Resource Name for Payment Period

    • Resource Payment Period Amount

    • Term

    • Payment Period Start Date

    • Payment Period End Date

  • Documents

    • Name

    • Status

    • Scope

    • Expiration Date

    • Received Date

    • FAY

    • Academic Year Start Date

    • Academic Year End Date

    • Payment Period Start Date

    • Payment Period End Date

Editor Subpage Buttons

In View Mode:

  • Refresh updates the query’s metadata to display the latest query information from the database.

  • Edit changes General page to Edit mode.

In Edit Mode:

  • Save triggers validation tests and, if no errors, the system stores the updated query information in the database. If the user navigates away from the page without clicking ‘Save,' the defined parameters in the Editor interface/RQL will not be saved.

  • Cancel discards the user’s changes.

Preview Subpage

After query parameters have been defined and saved on the Editor page, the list of students meeting the query parameters will be presented on the Preview subpage in a grid format with a total count of unique students displayed at the top of the page. The RQL for the query is also displayed at the top of the page.

The student list contains the following information and the grid utilizes sorting/filtering capabilities for these values:

  • External Student ID

    • The column heading will display the label configured for 'Student ID 1 Name' in Institution Setup, Student ID field.

  • External Student ID 2

    • The column heading will display the label configured for 'Student ID 2 Name' in Institution Setup, Student ID field.

  • Internal Student ID

  • First Name

  • Last Name

  • Current Program

    • Name of the Student’s current program displayed at the top of the Academic Plan.

      • If the student does not have a Current Program, the Current Program is blank.

  • Site

  • Campus

  • ‘View Student’ button

    • Navigates to the Student Summary tab for the student.

Preview Subpage Buttons

  • Download Results provides a downloadable data file of query data on the Results subpage. Files will also be available from the system Process Log. The file may not be available for download immediately as the process to create the file may take time to run.

    • Button will be disabled if the button if the Count is 0 (no students returned by the query), or if the query had an error state.

Results Subpage

When the ‘Download Results’ button on the Preview page is selected, the process to create a downloadable data file is created and the results of that process are displayed on the Results tab as well as in the Process Log. The Results tab will display the following information for the most recent process run for the query:

  • Process Type: Query Results- Basic or Query Results- Detailed

  • Process Id: System-generated identification number for the process

  • Started On: Time/Date the process started

  • Finished On: Time/Date the process finished

  • Total records: Number of students returned by the query

  • Process Status: COMPLETE, ERROR, or PROCESSING

  • User: User name of user who initiated the process

  • Download File: Link to download the detailed results file

Results Output Files

Please note: The data cache used by the Query Tool may contain inactive and deleted students, but inactive and deleted students are excluded from the Results Output File. Therefore, the count of students on the Preview screen of the Query Tool may differ from the count of students in the output file.

Query Tool Configuration Options

Users with Admin permission can configure options related to Query Tool functionality on the Query tab of the Institution Setup screen.

The Query page includes two sections: Real-Time Update Settings and Advanced Settings.

Real-Time Update Settings Section

  • Enable Real-Time Data Updates checkbox: When this option is selected, the system will dynamically update the data available to the Query Tool whenever a system process makes any change to the data. When this option is not selected, the system will only update the data for the Query Tool when the ‘Refresh All Cached Data’ process is triggered. Unselected by default.

  • Check for Additional Updates button: Updates the copy of the data used by the Query Tool with any additional data changes from scripts or incomplete processes not already saved by the real-time data updates.

Advanced Settings Section

  • Refresh All Cached Data button: Starts the process that applies data changes to the copy of the data used by the Query Tool.

System Processes

In addition to the ‘Refresh All Cached Data’ button in Institution Setup, a System Process is available called ‘Refresh Cache Data.’ This process allows for update of the cache based on the following parameters: Enterprises, Institutions, Campuses, Sites, Students, Program Types, and Programs.