User defined groups	Top  Previous  Next

The user defined group module allows users to specify conditions that make up the WHERE clause of a query. The WHERE clause is used to specify that only certain rows of a table are displayed, based on the criteria described in that WHERE clause. To review a WHERE clause at anytime click on Review group/query as statement. This will display the WHERE clause you have generated in both plain English and SQL (Structured Query Language). When generating user defined groups it is possible to specify up to 10 conditions. A condition can be applied against a value or another field. Example: WHERE ICT_Y8_NCL equals Value: 5a Finds all the pupils whose Y8_NCL score equaled 5a WHERE ICT_Y8_NCL equals Field: ICT_Y7_NCL Finds all the pupils whose Y8_NCL score was the same as their Y7_NCL score. Selecting a Value or Another Field When selecting a field the Value or another Field column will refresh. The contents of the refreshed field will differ depending on whether the user is performing a departmental or administrative task. The refreshed column will also differ depending on the condition selected. Departmental Example If the field ICT_Y7_NCL is selected along with the equals condition then the Value or another Field column will be refreshed with the distinct contents of that field along with the other fields available for use by that department. Value: (represents an empty string "" (Text), 0 (Whole number) or 0.0 (Decimal) Value: NULL (see Working with NULL Values) Value: 4a Value: 4b Value: 5b Value: 5c Field: Surname Field: Forename Field: Year Field: Form Field: ICT_Y7_Effort Field: ICT_Y7_Attainment Field: ICT_Y7_Homework Field: ICT_Y7_Behaviour Field: ICT_Y7_NCL Field: ICT_Y7_RANK etc... Other: The distinct contents of the field ICT_Y7_NCL represents the only values currently available in this field. To select any other value would result in an empty results set. However, to manually type a value users can select Other: This will change the select box to a text box where a value can be entered. The following conditions also result in a text box being displayed because they are all string related conditions. Begins with Does not begin with Ends with Does not end with Contains Does not contain Administrator Example If the field Shared: Y7_NCL is selected along with the equals condition then the Value or another Field column 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. Important Differences Between Academic Values and Values in SQL In order to get the most from the user defined group module it is important to understand some fundamental differences between the way academic values are used and the way SQL (or any spreadsheet/database application) understands them. In SQL A is worth less than F. This is different from the world of academia where a grade A is worth more than a grade F. This is easily dealt with providing that you bear this in mind when specifying conditions for your user defined group. Example: WHERE ICT_Y11_Predicted_GCSE is less than Value: C Finds all the pupils whose ICT_Y11_Predicted_GCSE grade is either A,A* or B WHERE ICT_Y11_Predicted_GCSE is greater than Value: C Finds all the pupils whose ICT_Y11_Predicted_GCSE grade is either D,E,F or G Numeric values can obviously be dealt with as expected. Constructing Complex Queries Sometimes it maybe necessary to use multiple conditions to achieve the desired results. Example You want to find all the Males on the SEN register who have a CAT Average score of less than 112 and whose ICT_Y9_NCL_Rank is less than their KS3_Target. Use the following query: WHERE Gender equals Value: M  AND SEN equals Value: Y  AND CATavg is less than Value: 112 AND ICT_Y9_NCL is less than Field: KS3_Target When to use OR Use OR when you want to use the same field more than once in the same query. Example: To find all the pupils in year 7 or in year 8: WHERE ( Year = Value:7  OR Year = Value: 8 ) To find all the males in year 7 or in year 8: WHERE Gender = Value: M AND(  Year = Value: 7 OR Year = Value: 8 ) Using AND in the above situations would result in an empty results set because there are no pupils in year 7 AND year 8. IMPORTANT: To avoid slow queries and unexpected results always surround OR statements with brackets. Working with NULL Values The concept of the NULL value is a common source of confusion for newcomers to SQL (Structured Query Language), who often think that NULL is the same as an empty string "" (blank). This is not the case! A NULL value is neither an empty string "" (Text), 0 (Whole number) or 0.0 (Decimal). A NULL value is best thought of as contents of a record before any value (even an empty string "") was saved to the database. As of version 3.1 Pupil Tracking stores empty strings "" as NULL values. This allows empty strings "" entered into whole number or decimal fields to appear blank rather than 0 or 0.0. Consider the following queries: To find all the pupils who scored 0 in a recent test. WHERE TEST_Result equals Value: To find all the pupils who have not had any data entered for them in a recent test WHERE TEST_Result equals Value: NULL To find all the pupils who scored 0 in a recent test or who have not yet had data entered for them. WHERE ( TEST_Result equals Value: OR TEST_Result equals Value: NULL) NULL values do not show up in Pupil Tracking, so if constructing a query to find blanks i.e empty strings "" be sure to always include a clause to look for NULL values.