Help centre

Advanced Queries

You can use Impact Query Language (IQL) to create more precise queries and return precisely the activities that you are interested in.

How does it Work?

Using IQL is more powerful than the more basic search queries, but is also more complex and often slower. So, where possible, it's usually worth starting by trying out one of the more basic built-in queries available.

Syntax

IQL operates on the database tables within KOLOLA. The basic syntax is as follows:

"table_name"(filters);

Where table_name is the name of the table that should be searched for records, and filters is a list of filters to apply. Queries must end with a semicolon (;). In practice, queries need to be prefixed with iql: when used in the search box or analytics tool.

So for example, you could input the following queries in the search box:

iql:"event"("EventID"="1000"); // Returns the activity with ID 1000

igl:"person"("Name"*"d" & "Name"*"m"); // Returns people with names that contain a 'd' AND an 'm'

Tables

A list of common table names which you can use in queries is listed below:

• event - activities themselves
• person - people in the system
• attachment - uploaded attachments
• link - external web links
• framework - assessment indicators
• feature - assessment statements
• evidence - types of evidence
• eventevidence - evidence data
• collection - event collections

Filters

There are two basic kinds of filter which are field filters and relation filters:

• Field filters - filter records based on specific fields.
• Relation filters - filter records based on their relation to other records in the database.

Field Filters

1. "field_name" = "value": Find all the records where the named field is equal to the given value
2. "field_name" < "value": Find all the records where the named field is less than the given value
3. "field_name" > "value": Find all the records where the named field is greater than the given value
4. "field_name" * "value": Find all the records where the named field contains (as text) the given value, anywhere inside it

Note - table names, field names and values all need to be enclosed with double quotes, "name"

Relation Filters

• [another_query]: Find all records that are related to a record that's returned by the given query. For example, to find all activities that are related to an attachment

  event([attachment]);

Combining Filters

You can combine multiple filters using the boolean operators & (for and) and | (for or).

Example Queries

"event"(["person"("Username"="administrator")]); // Returns events related to person with username administrator
"person"("Name"*"d"); // Returns people with names that contain a 'd'
"person"("Name"*"d" | "Name"*"m"); // Returns people with names that contain a 'd' OR an 'm' (or both)
"event"("TypeID"="12"); // Find all events where the TypeID is 12.  Type IDs can be found in the "Event Types"
section of the framework editor

Data Dictionary

The lists below contain the fields which form each table. These fields can be used to create queries for very specific data.

attachment

Uploaded attachments

collection

Basic information about user-created activity collections

collectionevent

Associate activities with the collections that they've been added to

collectionowner

Associates a collection with the people who are allowed to modify it

event

Basic information about activities

eventevidence

Associates a piece of evidence with an activity and the person who provided it

eventfeature

Associates activities with assessment statements

eventparticipant

Associates people with activities

evidence

Defines types of evidence that can be requested for activities

feature

Contains framework assessment statements and associates them with an indicator

framework

Contains framework indicators

link

person

What can I use this for?

Advanced queries can be used to access virtually any data stored in the database. This can be useful when you need a specific bit of information or want to explore the relationship between different people and activities.