Edit custom queries
Top  Previous  Next
From the Administration Menu, click on Query Manager > Edit custom queries

Custom queries allow administrators to update fields on Pupil Tracking based on criteria defined using the user defined groups module. 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 on Add a new custom query. Options are displayed prompting the user to add a new whole school query or a department query. Whole school queries are executed across all department tables where Shared: fields are selected. Department queries are executed across a single department table. See also Custom query example

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 using the user defined groups module.
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 department tables.

UPDATE Field: Shared: Y7_NCL to Value: 4a WHERE Year equals Value: 7 AND Gender equals Value: M
Does have criteria defined and would only update Y7_NCL to 4a for all the males in year 7.

Defining a Group
See user defined groups module 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 on the Execute & Save Query button will execute and save the query.

Managing Custom Queries

An unlimited number of custom queries can be created and managed through the Query Manager.

Global Search
The global search will display all custom queries whose name/departments/ID matches the search criteria. Click on Clear search results 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.

Department
This is the department the query has been created for. Whole School indicates that this is a query has been executed across all department tables.

Query Name
This is the name allocated to the query when it was created. Hovering the mouse over the query name will display the query description. The query name and description can be edited at anytime by clicking on Edit under the Action column.

Last Executed
This displays the date and time the query was last executed in dd-mm-yyyy hh:mm:ss format. Hovering the mouse over the date and time will display the query statement in both plain English and SQL (Structured Query Language) format.

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 on 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 on Execute will execute that query. Clicking on 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. If changes are made click on the Save button at the base of the page. To delete the query click on Delete at the top of the page.

With selected
By checking queries in the far left hand column and using the With selected: 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.

Execute all scheduled queries and query groups
Clicking on Execute all scheduled queries and query groups will execute all the scheduled queries and query groups, simulating exactly what will happen to these queries over night. Note: Scheduled queries which are also part of a scheduled custom query group will only be executed once.
Example:
If custom query IDs 5,7,9,11,14 have been scheduled and query groups containing the IDs 4,5,6,7,8,9,10,11 have also been scheduled then the following queries will be executed once in the following order 4,5,6,7,8,9,10,11.