Pocket eSchool User Manual - Building Queries

Home牋牋牋牋牋Table of Contents牋牋牋牋牋Previous Topic牋牋牋牋牋Next Topic牋牋牋牋牋Where Am I
For most search operations, Pocket eSchool's search methods, described in topic Selecting Records can be utilized to find single students, teachers, or staff persons. However, when a group of students need to be found based on one or more conditions, then the simple methods are not satisfactory. A better approach is to use Pocket eSchool's query system. It takes a bit of effort to set up a query and test it. However, once it has been set up, it easy and fast to use. In addition, queries have persistence; that is, the query is not deleted whenever new data is downloaded to the handheld. It stays on your handheld until you choose to delete it.

If you have not already done so, please read the example of a query first, in topic Selecting Records.. There are four aspects to a Pocket eSchool query which will now be described and, then, illustrated. They are 1) the query's name, 2) the filter condition, 3) the sort order, and 4) the query result record set. The query's name is some descriptive text which summarizes in a few words, what the query does. The filter condition describes the data values that all selected records must satisfy in order to be selected; the filter condition controls which records are to be selected. The sort order indicates the order that the selected records are to be listed in the query's result record set. The record set is the final result of the query; it is a list records which meet the filter condition that have been sorted as indicated by the sort order. The examples below will illustrate each of these aspects. We will look at the detail for the Grade 9 males query. Then, we'll build a new query that finds all 9th grade students who also have a birthday in May.

Table 2-12: Examining an existing Pocket eSchool Query

Figure 2-92
Tapping the Query button on Pocket eSchool's main menu causes the queries directory to be displayed. Figure 2-92 shows the current Student query directory. The directories for teacher and staff files are accessed from the dropdown list at the top of the form. The directory lists each query's name and a result record count. A new query is added by tapping on the New button. Existing queries are edited by first tapping on the query's row in the directory and then tapping on the Edit button.
Figure 2-93
Selecting the Grade 9 males query in Figure 2-92 and then tapping on the Edit button shows the Grade 9 males query. The purpose of this form is to display the query's name, filter condition, and sort order in one place. The only part of the query that can be edited on this form is the query's title, as illustrated in Figure 2-96 below. The filter condition and sort order have their own edit forms, which are accessed by tapping the Edit Filter and Edit Sort buttons, respectively.
Figure 2-94
Tapping the Edit Filter button shows the Filter edit form for this query. Notice that there are two filter conditions, Grade equals 09 and and Gender equals M, each on a separate line. These filter conditions are constructed using four components that are listed below the conditions grid. In the example in Figure 2-94, the first component is empty, the second contains Grade, the third contains equals, and the fourth contains 09. More about this later.
Figure 2-95
Tapping the Edit Sort button, in Figure 2-93, shows the Sort edit form for this query. Here, the sort key, Last Name, is used. This will sort the selected records by last name. The name of the sort key is selected by the dropdown list, below the Sort Key grid, which presently contains the key data item, Last Name. The present sort key can be edited by simply selecting a different data item from the dropdown list box. A second or third order sort key can be added by tapping the Add button and selecting the sort key. Any number of sort keys may be used simultaneously. The default sort order for any sort key is always in ascending order (from low to high). This can be overridden by checking the Descending checkbox for the desired key. Unwanted keys can be deleted by first tapping the key's row and then tapping the Delete button. The keys can be moved relative to each other in the sort grid by using the two buttons .

Table 2-13: Building a New Pocket eSchool Query

Figure 2-96
New queries can be constructed in two ways. The first is to create one from scratch by tapping the New button in Figure 2-92. The second way is to copy an existing query, by tapping the Copy button in Figure 2-92 and then modifying the copy to turn it into a new query. In the example here, we'll build the query from scratch to illustrate most of the aspects of query construction. Tapping the New button in Figure 2-92 causes an empty query, as shown in Figure 2-96, to be displayed. All that can be done here is to give the new query a title.
Figure 2-97
After tapping on the title line and using the Palm's built-in software keyboard, the text for the title can be entered. In the example illustrated here, the query will search for students whose grade is 9 and have a birthday in May. The title needs to be short. Its primary purpose is to identify the query in the query directory, shown in Figure 2-92. So, while Grade 9 with May birthday is not very good English, it does convey the correct contents of the query.
Figure 2-98
Tapping on the Done button on the software keyboard display, the entered text appears on the query's title data line. The new query still needs a filter condition and its sort order must be specified. The buttons, labeled Edit Filter and Edit Sort, are used for this.
Figure 2-99
Tapping on the Edit Filter button in Figure 2-98 launches the filter edit form, which is initially empty. The grid at the top of the form is for holding the text for the filter conditions. Up to five conditions can be seen at once. Scroll bars appear in the grid if there are more than 5 filter conditions. Notice that below the empty filter grid are three drop-down list boxes and a text box. They are currently empty and will get filled in during the construction of the filter condition. Figure 2-94 show what this looks like after the condition has been completed.
Figure 2-100
The first dropdown list box is always empty for the first filter condition. For other filter conditions, other than the first, it is used to select and or or, or some variation, using parentheses, as shown in Figure 2-109. The second dropdown list box contains the names of all the data items in the student file. One of the data items, Grade, needs to be selected for the query we are building.
Figure 2-101
The data list may be quite long. Its length depends on the number of student data items that have been downloaded for your Student Information System and the number of new data items that have been added in Pocket eSchool.
Figure 2-102
As you can see, the list can be quite long. Usually, the most commonly selected data items, such as Grade, are at the top of the list. Tapping the Grade entry in Figure 2-100 is all that is needed to select the data item. This data item name, Grade, is then displayed in the form.
Figure 2-103
The third dropdown list box contains all of Pocket eSchools relationals. In the query condition Grade equals 09, the relational is equals because it relates the data item Grade to the constant value 09. In algebra, the relationals (usually called relational operators, =, <=, <> etc.) are usually used in logical expressions. However, in Pocket eSchool, words, rather than math symbols, are used to describe the same idea.
Figure 2-104
More relationals. Pocket eSchool has quite an extensive set of relationals. In fact, there are 19 different ones that can be used to construct very concise queries.
Figure 2-105
Even more relationals. The relationals can be used with all kinds of data: text, numbers, dates, and lists.
Figure 2-106
After the data item and relational has been selected, the filter condition is taking shape. All that is needed now is the grade 9 value. In the data file, grade 9, has been downloaded as 09 rather than just a 9. This is because grade is really a text data item (to handle non-numeric grades, such as K or EC). The 09 makes grade 9 sort before 10; otherwise, 9 would get sorted after grade 12, which is not appropriate.
Figure 2-107
The grade value 09 can be added by using the software keyboard, using the numeric part of the keyboard.
Figure 2-108
After tapping the Done button in the software keyboard in Figure 2-107, the final result of the first filter condition is now completed. By tapping on the first grid row of the filter conditions (which was empty), the filter text Grade equals 09, is posted to the first filter condition grid row.
Figure 2-109
The next step is to add the additional filter that finds students whose birthday is in May. Pocket eSchool has a birthday data item which has the format mm/dd, where mm is the birthday month as a number and dd is the birthday month day as a number. Someone who as a birthday on May 26 would have a value of 05/26 as the birthday data item. Now, we can find students with a May birthday with the filter condition Birthday begins with 05. Since the students that we want to find have to be both 9th graders and have a birthday in May, then the two filter conditions must be connected with an and. Figure 2-109 lists several connectors involving and and or. The parentheses versions of and and or are used for constructing queries with more than two filter conditions. The parentheses are useful for constructing compound filter conditions. For example, the filter condition Grade equals 09 and (First Name equals Bill or First Name equals Bob) finds all the Bill's and Bob's who are also 9th graders. Without the parentheses, the filter condition would find all 9th grade Bill's and all Bob's of any grade.
Figure 2-110
Here, the second filter condition and Birthday begins with 05 has been completely entered, using the same methods used in the Grade equals 09 filter condition above.
Figure 2-111
Tapping the Back icon, in Figure 2-110, saves the filter condition and redisplays the query. The query has its filter condition completed, but no sort order has yet been specified. The sort order is set by tapping the Edit Sort button; the tap causes the Sort Order form to be displayed. Any data item or items can be used to sort on. For the query here, I'll choose the Last Name data item. This is done by tapping the Add button and then tapping the dropdown list box and selecting the Last Name data item.
Figure 2-112
Selecting the sort data item from the dropdown list box needs only a tap on its entry.
Figure 2-113
The tap on the Last Name entry in Figure 2-112, causes the selected value, Last Name, to be posted to the first row of the Sort Key grid. This is enough for this query. However, in general, any number of sort keys can be picked. Each of the sort keys selected can be designated to be sorted in descending (high to low) order.
Figure 2-114
Tapping the back arrow icon, in Figure 2-113, causes the query to be redisplayed with the newly added sort order showing. Now that the query is complete, it is saved by tapping the back arrow icon. The tap also causes the query directory to be redisplayed, as shown in Figure 2-115.
Figure 2-115
Now, the newly added query is visible in the query directory and it can be used. Selecting the query in the directory grid and then tapping the Use button initiates the search and sort.
Figure 2-116
Here is the end of the progress display form and some sort performance statistics. The query processor accessed 222 student records, of which only 4 records met the filter condition. This took 7 seconds to process. The rate per 1000 records for the same query would be 31 seconds.
Figure 2-117
The selected 4 records are made visible by tapping the Continue button, in Figure 2-116, The query data display form in Figure 2-117 is a specialized student directory that displays the value of the sort key in its first column. The students last and first names are shown in the second column. All the usual tap link options and further searches, using the search icon, can be used on the students selected by the query.
Figure 2-118
Since the tap link is set to Data, tapping the first row in Figure 2-117 causes Dustin Berry's Data form to be displayed. Note that his birthday is May 26!

Home牋牋牋牋牋Table of Contents牋牋牋牋牋Previous Topic牋牋牋牋牋Next Topic牋牋牋牋牋Top

Pocket eSchool User Manual Topics Map

牋牋Overview
牋牋Pocket eSchool
牋牋牋A Tour of Student Forms
牋牋牋A Tour of Teacher Forms
牋牋牋A Tour of Course Forms
牋牋牋A Tour of Bus Forms
牋牋牋A Tour of Statistics
牋牋牋Selecting Records
牋牋牋Navigation Basics
牋牋牋Picture Sizes
牋牋牋Building Queries <----- You are here
牋牋牋Adding contacts and contact data
牋牋牋Management
牋牋Installing Pocket eSchool
牋牋Installing eSchool PDA Edition
牋牋Downloading your School's Data
牋牋Update Notes

	Figure 2-92 Tapping the Query button on Pocket eSchool's main menu causes the queries directory to be displayed. Figure 2-92 shows the current Student query directory. The directories for teacher and staff files are accessed from the dropdown list at the top of the form. The directory lists each query's name and a result record count. A new query is added by tapping on the New button. Existing queries are edited by first tapping on the query's row in the directory and then tapping on the Edit button.
	Figure 2-93 Selecting the Grade 9 males query in Figure 2-92 and then tapping on the Edit button shows the Grade 9 males query. The purpose of this form is to display the query's name, filter condition, and sort order in one place. The only part of the query that can be edited on this form is the query's title, as illustrated in Figure 2-96 below. The filter condition and sort order have their own edit forms, which are accessed by tapping the Edit Filter and Edit Sort buttons, respectively.
	Figure 2-94 Tapping the Edit Filter button shows the Filter edit form for this query. Notice that there are two filter conditions, Grade equals 09 and and Gender equals M, each on a separate line. These filter conditions are constructed using four components that are listed below the conditions grid. In the example in Figure 2-94, the first component is empty, the second contains Grade, the third contains equals, and the fourth contains 09. More about this later.
	Figure 2-95 Tapping the Edit Sort button, in Figure 2-93, shows the Sort edit form for this query. Here, the sort key, Last Name, is used. This will sort the selected records by last name. The name of the sort key is selected by the dropdown list, below the Sort Key grid, which presently contains the key data item, Last Name. The present sort key can be edited by simply selecting a different data item from the dropdown list box. A second or third order sort key can be added by tapping the Add button and selecting the sort key. Any number of sort keys may be used simultaneously. The default sort order for any sort key is always in ascending order (from low to high). This can be overridden by checking the Descending checkbox for the desired key. Unwanted keys can be deleted by first tapping the key's row and then tapping the Delete button. The keys can be moved relative to each other in the sort grid by using the two buttons .

	Figure 2-96 New queries can be constructed in two ways. The first is to create one from scratch by tapping the New button in Figure 2-92. The second way is to copy an existing query, by tapping the Copy button in Figure 2-92 and then modifying the copy to turn it into a new query. In the example here, we'll build the query from scratch to illustrate most of the aspects of query construction. Tapping the New button in Figure 2-92 causes an empty query, as shown in Figure 2-96, to be displayed. All that can be done here is to give the new query a title.
	Figure 2-97 After tapping on the title line and using the Palm's built-in software keyboard, the text for the title can be entered. In the example illustrated here, the query will search for students whose grade is 9 and have a birthday in May. The title needs to be short. Its primary purpose is to identify the query in the query directory, shown in Figure 2-92. So, while Grade 9 with May birthday is not very good English, it does convey the correct contents of the query.
	Figure 2-98 Tapping on the Done button on the software keyboard display, the entered text appears on the query's title data line. The new query still needs a filter condition and its sort order must be specified. The buttons, labeled Edit Filter and Edit Sort, are used for this.
	Figure 2-99 Tapping on the Edit Filter button in Figure 2-98 launches the filter edit form, which is initially empty. The grid at the top of the form is for holding the text for the filter conditions. Up to five conditions can be seen at once. Scroll bars appear in the grid if there are more than 5 filter conditions. Notice that below the empty filter grid are three drop-down list boxes and a text box. They are currently empty and will get filled in during the construction of the filter condition. Figure 2-94 show what this looks like after the condition has been completed.
	Figure 2-100 The first dropdown list box is always empty for the first filter condition. For other filter conditions, other than the first, it is used to select and or or, or some variation, using parentheses, as shown in Figure 2-109. The second dropdown list box contains the names of all the data items in the student file. One of the data items, Grade, needs to be selected for the query we are building.
	Figure 2-101 The data list may be quite long. Its length depends on the number of student data items that have been downloaded for your Student Information System and the number of new data items that have been added in Pocket eSchool.
	Figure 2-102 As you can see, the list can be quite long. Usually, the most commonly selected data items, such as Grade, are at the top of the list. Tapping the Grade entry in Figure 2-100 is all that is needed to select the data item. This data item name, Grade, is then displayed in the form.
	Figure 2-103 The third dropdown list box contains all of Pocket eSchools relationals. In the query condition Grade equals 09, the relational is equals because it relates the data item Grade to the constant value 09. In algebra, the relationals (usually called relational operators, =, <=, <> etc.) are usually used in logical expressions. However, in Pocket eSchool, words, rather than math symbols, are used to describe the same idea.
	Figure 2-104 More relationals. Pocket eSchool has quite an extensive set of relationals. In fact, there are 19 different ones that can be used to construct very concise queries.
	Figure 2-105 Even more relationals. The relationals can be used with all kinds of data: text, numbers, dates, and lists.
	Figure 2-106 After the data item and relational has been selected, the filter condition is taking shape. All that is needed now is the grade 9 value. In the data file, grade 9, has been downloaded as 09 rather than just a 9. This is because grade is really a text data item (to handle non-numeric grades, such as K or EC). The 09 makes grade 9 sort before 10; otherwise, 9 would get sorted after grade 12, which is not appropriate.
	Figure 2-107 The grade value 09 can be added by using the software keyboard, using the numeric part of the keyboard.
	Figure 2-108 After tapping the Done button in the software keyboard in Figure 2-107, the final result of the first filter condition is now completed. By tapping on the first grid row of the filter conditions (which was empty), the filter text Grade equals 09, is posted to the first filter condition grid row.
	Figure 2-109 The next step is to add the additional filter that finds students whose birthday is in May. Pocket eSchool has a birthday data item which has the format mm/dd, where mm is the birthday month as a number and dd is the birthday month day as a number. Someone who as a birthday on May 26 would have a value of 05/26 as the birthday data item. Now, we can find students with a May birthday with the filter condition Birthday begins with 05. Since the students that we want to find have to be both 9th graders and have a birthday in May, then the two filter conditions must be connected with an and. Figure 2-109 lists several connectors involving and and or. The parentheses versions of and and or are used for constructing queries with more than two filter conditions. The parentheses are useful for constructing compound filter conditions. For example, the filter condition Grade equals 09 and (First Name equals Bill or First Name equals Bob) finds all the Bill's and Bob's who are also 9th graders. Without the parentheses, the filter condition would find all 9th grade Bill's and all Bob's of any grade.
	Figure 2-110 Here, the second filter condition and Birthday begins with 05 has been completely entered, using the same methods used in the Grade equals 09 filter condition above.
	Figure 2-111 Tapping the Back icon, in Figure 2-110, saves the filter condition and redisplays the query. The query has its filter condition completed, but no sort order has yet been specified. The sort order is set by tapping the Edit Sort button; the tap causes the Sort Order form to be displayed. Any data item or items can be used to sort on. For the query here, I'll choose the Last Name data item. This is done by tapping the Add button and then tapping the dropdown list box and selecting the Last Name data item.
	Figure 2-112 Selecting the sort data item from the dropdown list box needs only a tap on its entry.
	Figure 2-113 The tap on the Last Name entry in Figure 2-112, causes the selected value, Last Name, to be posted to the first row of the Sort Key grid. This is enough for this query. However, in general, any number of sort keys can be picked. Each of the sort keys selected can be designated to be sorted in descending (high to low) order.
	Figure 2-114 Tapping the back arrow icon, in Figure 2-113, causes the query to be redisplayed with the newly added sort order showing. Now that the query is complete, it is saved by tapping the back arrow icon. The tap also causes the query directory to be redisplayed, as shown in Figure 2-115.
	Figure 2-115 Now, the newly added query is visible in the query directory and it can be used. Selecting the query in the directory grid and then tapping the Use button initiates the search and sort.
	Figure 2-116 Here is the end of the progress display form and some sort performance statistics. The query processor accessed 222 student records, of which only 4 records met the filter condition. This took 7 seconds to process. The rate per 1000 records for the same query would be 31 seconds.
	Figure 2-117 The selected 4 records are made visible by tapping the Continue button, in Figure 2-116, The query data display form in Figure 2-117 is a specialized student directory that displays the value of the sort key in its first column. The students last and first names are shown in the second column. All the usual tap link options and further searches, using the search icon, can be used on the students selected by the query.
	Figure 2-118 Since the tap link is set to Data, tapping the first row in Figure 2-117 causes Dustin Berry's Data form to be displayed. Note that his birthday is May 26!