From the Administration Menu, click Query Manager > Edit custom queries
Custom queries allow administrators to update fields on Pupil Tracking based on criteria defined using the user defined groups interface. Fields may be updated to a specific value or to the contents of another field. Always backup data using the Export Manager prior to executing queries which update non-empty fields.
Adding a New Custom Query
To add a new custom query click Add a new custom query. Options are displayed prompting the user to add a new whole school query or a subject query. Whole school queries are executed across all subject tables where Shared: fields are selected. Subject queries are executed across a single subject table.
Review query as statement
Click this link at any time to review the query as a statement in both plain English and SQL (Structured Query Language). Sometimes viewing the query as a statement makes it easier to read and understand the query to be executed.
Selecting the field to be updated
It is possible to select a shared or a general field to be updated. Upon selection the update to select box will refresh. The contents of the refreshed select box will differ depending on the type of field chosen. If a general field is selected for update then the select box will refresh displaying the distinct contents of that field as values along with other shared and general field names. If a shared field is selected for update then the select box will be refreshed with the contents of the select box that has been created for this field. If no select box has been created then no values will be displayed and Other: must be selected to allow the manual entry of a value.
Apply conditions
Unchecking Apply conditions assumes that no criteria are to be applied to the query.
Example:
UPDATE Field: Shared: Y7_NCL to Value: 4a
Has no criteria defined and would simply update the field Y7_NCL to 4a across all year groups and subject tables.
UPDATE Field: Shared: S2_BGE_Level_May to Value: 3.3 WHERE Year equals Value: 2 AND Gender equals Value: M
Does have criteria defined and would only update S2_BGE_Level_May to 3.3 for all the males in S2.
Defining a Group
See user defined groups interface for help with defining a group.
Query Name
Enter a suitable name for the query (this may be edited later).
Query Description
Enter a suitable description for the query (this may be edited later).
Schedule this query to run every night
If this box is checked the query will be executed once every night (this may be edited later).
Execute & Save Query
Clicking Execute & Save Query will execute and save the query.
Managing Custom Queries
An unlimited number of custom queries can be created and managed through the Query Manager.
Search
The search will display all custom queries whose name/subjects/ID matches the search criteria. Click Clear search to clear the search criteria.
The column headings for Custom Queries are explained as follows:
ID
Each time a new custom query is added a new ID will be generated. When executing multiple queries, queries are executed in order of their ID.
Subject
This is the subject the query has been created for. Whole School indicates that this query has been executed across all subject tables.
Query Name
This is the name allocated to the query when it was created. The query name and description can be edited at anytime by clicking Manage under the Action column.
Last Executed
This displays the date and time the query was last executed.
Status
The status indicates either Successful for a successfully executed query or Failed for a query which has failed. A query may fail if fields used in the query have subsequently been edited or deleted. The exact reason for a query failing is documented in the Query Log for each query. This can be accessed by clicking Manage under the Action column.
Scheduled
Indicates whether the query has been scheduled to run every night or not. A tick will be displayed if the query is to be executed every night, otherwise this column will appear blank. Scheduled queries are executed in order of their ID number. Note: Scheduled queries which are also part of a scheduled custom query group will only be executed once.
Action
The 2 options Execute and Manage are displayed under the Action column for each custom query. Clicking Execute will execute that query. Clicking Manage will display the Query Log, Query Statement, Query Name and Query Description along with the option to schedule the query. The Query Log contains details of when the query was last executed, the status of the query and a list of the tables affected by the query. Under each table affected by the query the number of rows affected is displayed. If the status of the query is Failed then failure details will be displayed. Note: Pupil Tracking will not update columns where the new value is the same as the old value. This means that affected rows does not actually equal the number of rows matched, only the number of rows that were literally affected by the query. Only the query name, description and schedule details can be edited.
With checked
By checking queries in the far left hand column and using the With checked: options it is possible to Execute, Schedule, Unscheduled, Group and Delete multiple queries. Note: Multiple queries are executed in order of their ID number. For example, if the results of one query will have an affect on another, then create the queries in the order in which they should be executed.