Using Extended Student Fields in Query
Overview
Extended Student Fields is a powerful feature of Aeries Query that seamlessly integrates data from Aeries Analytics (including the free LCAP Dashboards) into query results with no complex syntax. The Extended Student Fields behave like fields in the Student Data (STU) table, which makes them easy to incorporate into new or existing queries.
Security ↑
Users do not need any special permissions to use Extended Fields in their queries. Instead, each Extended Field is secured based on Read permission to the underlying table for that Analytics item. For example, users must have permission to Attendance Data (ATT) to use an Extended Field based on an ATT Percentage item. This security layer is very similar to how the Analytics Dashboard Student List data are secured.
Using Extended Student Fields in Query ↑
In Aeries Query, the Extended Fields become available once the Student Data (STU) table has been included in the query (e.g., LIST STU). The available Extended Fields display below the regular fields. As with regular fields, the Extended Fields can be added to the query by clicking the field or by typing the name manually.
Query Commands
The Extended Fields can be used with all Query commands.
LIST - Extended Fields can be included in the column list like any other field. Example: List all students' special education status
LIST STU ID NM SPECIALED
TOTAL - Extended Fields can be used for grouping. Example: Get an overall count of students in special education and students not in special education
TOTAL STU SPECIALED BY SPECIALED
SKIP - Extended Fields can be used to skip students. Example: Skip all students in special education
SKIP STU IF SPECIALED = "YES"
KEEP - Extended Fields can be used to keep students. Example: Keep only students in special education
KEEP STU IF SPECIALED = "YES"
CHANGE - Extended Fields can be used as the value to change TO. Example: CHANGE STU U9 TO SBAC_ELA.LEVEL
Extended Field values cannot be changed, however. The following example is not allowed: CHANGE STU SBAC_ELA.LEVEL TO 5
The Extended fields can be used to filter and sort records like any other field.
Ex: LIST STU ID NM GR SBAC_ELA IF SBAC_ELA < 4
Ex: LIST STU ID NM GR ENGLISH_GRD BY ENGLISH_GRD IF ENGLISH_GRD # " "
Qualifying Value and Level
Each Extended Field actually has two properties: the qualifying value and the level. These properties have different meanings depending on the type of Analytics item on which the Extended Field is based. Examples of qualifying values and levels can be found on the Student Indicator Details page for a student. By default, the qualifying value is returned when an Extended Field is used in a query. To use the level instead, append .LEVEL to the end of the name; e.g., SBAC_ELA.LEVEL
Configuration ↑
Extended Student Fields Config
The Extended Student Fields Config page is available to admins and to users with Update permission to the Item Definitions (DAI) security area. The page displays a list of all Analytics item definitions, including the LCAP Dashboard items and any custom items (for customers with a paid Analytics subscription). Use this configuration page to define the Extended Field names that will be used in Query.
The Query Column Title is the name that will be used in Query to refer to each Analytics item. Use the filters at the top to find specific items by Title, Short Title, System (i.e., LCAP or non-LCAP), or Item Type. Click Edit to add, change, or remove the Query Column Title for any item. Many of the LCAP items will already have a Query Column Title populated by default, and these can be changed, too. The Query Column Title can only contain uppercase letters, numbers, and the underscore character and must be between 4 and 20 characters in length.
Note: If an existing Query Column Title is changed or removed, any saved query using the old name will no longer work. Please be mindful of this when choosing to update an existing Query Column Title.
Note: Currently, when a Query Column Title is added or changed, users must log out and back in to Aeries to use that Extended Field. This is due to the use of session variables. This behavior may be improved in the future for a more seamless experience.