Add criteria to query fields

You can add criteria to queries to restrict the number of results returned. For example, you may want to view the details for one part of the hierarchy only, or for full time employees only.

You can add as many criteria to fields as you need to filter the results, but it’s best to build queries in stages, so that if anything goes wrong it’s easy to spot which criteria caused the problem.

When you are adding criteria to fields in Query Builder, you have three different options, depending on the type of field selected:

Text fields

Most fields in the database are text fields and can hold any combination of characters, including numbers as well as text. However, text fields in Query Builder do not hold date or number information.

The table below lists the options available when you are adding criteria to text fields.

Criteria	Description
Equal	Is equal to the criteria entered in the adjacent drop-down menu
Not Equal	Is not equal to the criteria entered in the adjacent drop-down menu
Begins with	Searches for any word that begins with the character(s) entered. For example, you could search for all surnames beginning with B
Ends with	Searches for any word that ends with the character(s) entered
Contains	Searches for items that contain a certain character or word. For example, searching for ‘manager’ using the Contains function will return all manager types (admin manager, accounts manager etc)
Not Contains	Searches for items that do not contain a certain character or word. For example, if your organisation has a standard six-month probation period term and you want to find all employees with circumstances that exclude them from this term, you could select Not Contains and 3 Months to find all other employees and their probation terms

Number fields

The Salary field is classed as a number field in the database. Number fields only hold numerical information, for example, salary figures or employee ID numbers.

The table below lists the options available when you are adding criteria to number fields, based on an example of a £30,000 salary.

Criteria	Description
Equal	Is equal to the criteria entered in the adjacent drop-down menu
Not Equal	Is not equal to the criteria entered in the adjacent drop-down menu
Less Than	Any salary less than £29,999.99. A salary of £30,000 would not be included
Equal To or Less Than	Records with a salary of equal to or less than £30,000. £30,000 would be included and £30,001 and above would not
More Than	Records with a salary of more than £30,000
Equal To or More Than	Records with a salary of equal to or more than £30,000

When you run queries using fields from the Job & Salary screen and select All Information at the top of the screen, a hidden formula field called Current Active Job in Job & Salary is automatically added ahead of the field you select, with criteria set as Equal to Yes.

This field will filter your results to look only at employees’ current records:

• If you leave this as part of the query, historical closed records are not included in the results

• If you delete this from the query, all current and historical job and salary records are included in the results

• If you change the criteria for this row to Equal to No, every historical record for employees is included in the results

Date fields

Date fields contain date information, for example, Start Date, Probation Expiry, Left Date. This table lists the options available when you are adding criteria to date fields.

Criteria	Description
Equal	Is equal to the criteria entered in the adjacent drop-down menu
Not Equal	Is not equal to the criteria entered in the adjacent drop-down menu
Before	Searches for events that occurred before the selected date
On or Before	Searches for events that occurred on or before the selected date
After	Searches for events that occurred after the selected date
On or After	Searches for events that occurred on or after the selected date
Between	Searches for events that occurred between two specific dates

You can type dates or add them by clicking the calendar icon.

Dynamic dates

You can choose the fx option to choose dynamic dates. You can create your own dynamic dates via Admin > Dynamic Dates.

Deleting criteria

You can easily remove any criteria from queries by clicking the trash icon.