How to write and save a query

Getting the data sets you need from onCourse

In every list view in onCourse you can use find to search for records. Creating a find, or search, is also known as creating a query – you are querying the records in your database to find those that match a certain set of criteria.

Saved queries are very useful if you commonly need to work with a particular data set, for example, if you are in charge of managing the VET courses, you may want to regularly work with the courses that have a VET flag. You can save a your query so you do not have to keep writing it every time you want to see the list of courses with a VET flag. Remember too that a query looks up the records again every time you run it – saving a query does not save the results of the query. This means if you run a query for VET courses on Monday, and save it, add five new VET courses on Tuesday, then run the query again on Wednesday, your query results on Wednesday will include the five new courses.

Most fields that are in onCourse are available for queries. If there is a query you need to write, but you can’t see the field that will allow you to do this, please let us know

Some examples

Finding all courses that contain units from the Certificate II in Business BSB20101
A student has completed two short courses that covered units from the Certificate II in Business. She would like to know what other courses are available that will enable her to keep working towards her goal of achieving sufficient units to be awarded the full qualification.

1. In the courses window, select find
2. From the first drop down box, select ‘Qualification National Code’, from the second, select ‘is’, and in the text field enter BSB20101
3. Select find. You will return to the window with all the courses that meet the criteria of your search. If your search returns no records, that is because no courses meet that criteria.

To return to the view of all records, hold down Control and click the find button. Select ‘Find all’.

Find all students who are over 25 and have not completed year 12
The government has made training vouchers available to people who are over 25 but have not completed year 12. You can query your own student list to find people who might be eligible and and then sms, email or snail mail them information about the programs.

1. Open the contacts window
2. Select find
3. In the first line, create the query ‘Age’, ‘is greater than’ and enter 25
4. Add a second line to the query by selecting ‘Add search line’.
5. In the second line, create the query ‘Student highest school level’, ‘Year 10’.
6. Select find

You can now select all these students use the cog wheel function to ‘send a message to x contacts’.

Finding contacts

Working with classes scheduled for Term 3
Your college runs four distinct terms per year and you wish to routinely work with the programs you have scheduled for term 3, to check all their details and get the ready to be published on your website. This query will find all classes with a start date within a particular date range.

1. Open the classes window and select find
2. In the first line of the query add ‘start date/time’, after, 21/7/08
3. Add a second line to the query ‘start date/time’, before, 13/10/08
4. As this is a query that you will use multiple times, before you select find, click on the cogwheel and select ‘Save find’.
5. When you save the find, you can make it visible only to you or if you uncheck this box, visible to all your colleges users of onCourse. Make sure you give your find a name that is clear and indicates what results are returned. In this example, Term 3 2008 would be an appropriate name.
6. Once you have saved your find, click the find button to run the query.

Save find

Using saved queries

You can access saved queries from the find button in the list view window. For example, to access saved queries for classes, open the class window and control click on the find button. A list of your saved queries are available to select from.

Alternatively, you can select the find option, and within the find pop up screen your saved queries are also available to select by clicking on the cogwheel function.

By selecting the option ‘manage find’ in either of these locations, you can choose to make public queries private (or vice versa) or delete saved queries.