IMPORTANT: This chapter is under construction and subject to change.
onCourse has powerful tools for searching across every piece of data you have collected. You can search in lists, perform advanced searches on any attribute of the data and use these searches to write scripts and reports. In this chapter we'll start from the simplest tools and then show you more advanced possibilities.
Every list view has a search panel across the bottom.
Typing into the Search bar will retrieve all records that match your input across certain fields. For example, in the Contact list, the text you enter will be found in first name, last name and email address fields.
Find related records is one of the most powerful tools inside onCourse, let you quickly jump from window to window, finding records related to groups of other records. This function is located next to the advanced search in each list view in onCourse.
For example, to locate all the employers of all the students aged 18 to 25 in the last three months of VET programs to send the Employer Satisfaction Survey to you can start by searching for all classes with a particular tag or use the advanced search on VET course flag. Once you have those classes, you can highlight those delivered in the last three months, and select from the find related icon 'Enrolled students'.
Once the student list has opened, you can search that list of results for students aged 18 to 25. The list view that has opened is in a special mode called 'custom selection' which you can see in the window header bar. This means other searches or filters you run on this window are only inside the special set of results you have created as a list.
With the reduced list of students, you can use the find related icon to select contacts related as Employer. From this new window, you can use the cog wheel to send a message to the student's employers.
Find related can only be run on a max of 1000 records
You can construct more powerful Advanced Search queries using the onCourse query language. You can toggle between Basic and Advanced Search by clicking the magnifying glass icon on the left of the search panel. When you are in advanced mode, a plus symbol appears next to the icon. It might seem intimidating at first, but understanding the logic of this feature will help hone in on finding exactly the right data you need.
A simple query consists of a field, followed by an operator, followed by a value:
title starts with "Apply"
In the above example, the field is 'title', the operator is 'starts with', and the value is 'Apply'. Searches are context sensitive to the screen you are viewing e.g. in the Unit of Competency list, this query will show you records beginning with 'Apply'.
Two or more queries can be linked together with conditional operators.
title starts with 'Apply' and nationalCode starts with "B"
In this example, 'and' is the conditional operator that combines the two separate queries. In this case, using 'and' means only records that satisfy both queries are returned.
When you click into the Advanced Search box, a drop-down list of available fields will appear. The fields that are available to use in queries will change depending on which record type you are searching on.
In your query, you will want to compare the field with some value. The type of comparison is defined with an operator. Each operator can be written as a word in full or as the short symbol.
Finds records where the field matches the value exactly. This operator is not case sensitive.
createdOn = today
nationalCode EQUALS "ABC"
NOT EQUAL finds records that do not match the input value.
deliveryMode != ONLINE
LESS THAN returns records where the value is less than the input value.
LESS THAN can also be used with EQUALS to return records where the value is less than or equal to the input value.
enrolmentCount < 10
enrolmentCount <= 9
GREATER THAN returns records where the value is greater than the input value.
GREATER THAN can be used with EQUALS to return records where the value is greater than or equal to the input value.
age > 17
age >= 18
BETWEEN ( .. ) is used to specify a date range. BETWEEN ( .. ) will return records where the specified attribute occurred or was created between the set dates.
createdOn 01/01/2018 .. 01/02/2018
BETWEEN can be used with a star closure ( * ), placed either directly before or after the date. See below for the correct syntax; placing the star and BETWEEN before the date will find all relevant data from before that date, while placing them after will find all relevant data after that date.
The above query will find any data that was create before the 1st January 2018, while the below query finds anything created after 1st January 2018.
IN will display any data that fits into the input data set.
A set is denoted as a list of items, where each element of the set is separated by a comma. In the below example, this query will return and display any data that has a confirmationStatus as either NOT_SENT or DO_NOT_SEND.
confirmationStatus in NOT_SENT, DO_NOT_SEND
BEFORE can be used in conjunction with dates (or date-specific keywords, like 'today' or 'tomorrow') to return any requested data created or set before the input date.
createdOn before today
AFTER can be used in conjunction with dates (or date-specific keywords, like 'today' or 'tomorrow') to return any requested data created or set after the input date.
createdOn after today
CONTAINS returns any data where the specified field contains/matches the input.
name contains "Gardening"
STARTS WITH returns any data where the specified field contains data that starts with the input.
name starts with "Cooking"
ENDS WITH returns any data where the specified field contains data that ends with the input.
name ends with "Expert"
NOT reverses the returned value of all of the previous operators.
For example, 'code not contains "Gardening"' with return all courses that have a code that does not contain the term 'Gardening'
name not like John
name not contains "Gardening"
name not starts with "Cooking"
name not ends with "Beginners"
The HAS TAG ( # ) operator will return records that are tagged with the specified tag in onCourse E.G. if you have a tag named Health and Care, and another named Training, and you wanted to find records that use either tag, you'd use:
#Health_and_Care or #Training
The FILTER TAG ( @ ) operator is used to as shorthand to call a custom query or other filter that has been saved.
For example, imagine the following query is saved with the name kids:
Age <= 12
@kids can then be called in the construction of other queries
@kids and isMale is true
is equivalent to Age <= 12 and isMale is true
The LIKE (~) operator is used to search a field for a specific pattern.
There are two special wild card characters used with the LIKE operator:
%: the percent sign is used to represent any amount of characters (including zero)
_: the underscore is used to represent exactly one character
Examples of the LIKE operator with wild card characters:
name ~ "a%" name like "a%"
finds any record that has a name starting with "a"
name ~ "%a"
finds any record that has a name ending with "a"
name ~ "%ab%"
finds any record that has a name containing "ab"
name ~ "_a%"
finds any record that has a name with "a" as the second letter
name ~ "_%_%_%"
finds any record that has a name with at least a length of 3 characters
name ~ "a%b"
finds any record that has a name starting with "a" and ending with "b".
name contains "Gardening" or code starts with "GAR"
startDateTime = tomorrow and successAndQueuedEnrolments >= minimumPlaces
Use brackets to specify the order in which query fragments are executed.
name contains "Gardening" or (code starts with "GAR" and startDateTime = tomorrow)
In onCourse, a keyword is a reserved word that has a predefined meaning.
the current day from 00:00 to 23:59
yesterday from 00:00 to 23:59
tomorrow from 00:00 to 23:59
from January 1 00:00 to December 31 23:59 of the previous year
from the 1st of the previous month 00:00, to the last day of the previous month 23:59
from Monday 00:00 to Sunday 23:59 of the previous week.
If 'today' is Thursday 13 September 2018, then 'last week' will be from Monday 3 September 2018 to 9 September 2018.
from January 1 00:00 to December 31 23:59 of the next year
from the 1st of the next month 00:00 , to the last day of the next month 23:59
from Monday 00:00 to Sunday 23:59 of the next week.
If 'today' is Thursday 13 September 2018, then 'next week' will be from Monday 17 September 2018 to 24 September 2018.
createdOn today createdOn yesterday .. tomorrow startDateTime last week endDateTime next year
Dates can be combined with basic arithmetic and a specified time unit to query over a period of time.
createdOn today + 1 day createdOn yesterday..tomorrow + 2 week createdOn * .. today + 6 month createdOn 9:00 .. 19:00 today - 1 year
ME allows for search to be constructed using the currently logged in user as a query value.
Invoice.createdByUser = me
Scripts often need retrieve records from your database to perform some function. For example, if you wanted to contact all students who are enrolled in a class starting tomorrow, you would need to retrieve all classes that start tomorrow from the database.
Add a query panel to your script like this.
The results of this query are then available to you in your script in the variable 'records'. You can use this to perform additional actions in the script.
For more information on custom scripts, please visit our Scripting chapter