Knowledge Base

Desktop

Search, Views & Groups

Advanced Search Full Syntax

Summary

All searchable fields within Oneserve can be queried using OQL. This allows the user to create complex and bespoke searches that return jobs that meet very specific criteria, and can be used to craft unique and useful search views. The following is a full list of the syntax which can be used in advanced search views.

For an overview of the keywords and operators used by OQL, please see this helpcentre article.

Date Functions

Created Date

The date on which the job was created in Oneserve. This can be different from the reported date.

Syntax

createdDate BETWEEN '01/01/1900' AND '01/01/2000'

```
createdDate > '01/01/1900'
```
```
createdDate >= '01/01/1900'
```
```
createdDate < '01/01/1900'
```
```
createdDate <= '01/01/1900'
```
```
createdDate IS EMPTY
```
```
createdDate IS NOT EMPTY
```

Reported Date

The date a job was reported. This can be different from the created date.

Syntax

reportedDate BETWEEN '01/01/1900' AND '01/01/2000'

```
reportedDate > '01/01/1900'
```
```
reportedDate >= '01/01/1900'
```
```
reportedDate < '01/01/1900'
```
```
reportedDate <= '01/01/1900'
```
```
reportedDate IS EMPTY
```
```
reportedDate IS NOT EMPTY
```

Modified

The date a job was last updated.

Syntax

lastUpdate BETWEEN '01/01/1900' AND '01/01/2000'

```
lastUpdate > '01/01/1900'
```
```
lastUpdate >= '01/01/1900'
```
```
lastUpdate < '01/01/1900'
```
```
lastUpdate <= '01/01/1900'
```
```
lastUpdate IS EMPTY
```
```
lastUpdate IS NOT EMPTY
```

End Date

The actual end date of a job.

Syntax

endDate BETWEEN '01/01/1900' AND '01/01/2000'

```
endDate > '01/01/1900'
```
```
endDate >= '01/01/1900'
```
```
endDate < '01/01/1900'
```
```
endDate <= '01/01/1900'
```
```
endDate IS EMPTY
```
```
endDate IS NOT EMPTY
```

Start Date

The actual start date of a job.

Syntax

startDate BETWEEN '01/01/1900' AND '01/01/2000'

```
startDate > '01/01/1900'
```
```
startDate >= '01/01/1900'
```
```
startDate < '01/01/1900'
```
```
startDate <= '01/01/1900'
```
```
startDate IS EMPTY
```
```
startDate IS NOT EMPTY
```

Planned End Date

The planned end date of a job.

Syntax

plannedEndDate BETWEEN '01/01/1900' AND '01/01/2000'

```
plannedEndDate > '01/01/1900'
```
```
plannedEndDate >= '01/01/1900'
```
```
plannedEndDate < '01/01/1900'
```
```
plannedEndDate <= '01/01/1900'
```
```
plannedEndDate IS EMPTY
```
```
plannedEndDate IS NOT EMPTY
```

Planned Start Date

The planned start date of a job.

Syntax

plannedStartDate BETWEEN '01/01/1900' AND '01/01/2000'

```
plannedStartDate > '01/01/1900'
```
```
plannedStartDate >= '01/01/1900'
```
```
plannedStartDate < '01/01/1900'
```
```
plannedStartDate <= '01/01/1900'
```
```
plannedStartDate IS EMPTY
```
```
plannedStartDate IS NOT EMPTY
```

Target Date

The target end date of a job.

Syntax

targetDate BETWEEN '01/01/1900' AND '01/01/2000'

```
targetDate > '01/01/1900'
```
```
targetDate >= '01/01/1900'
```
```
targetDate < '01/01/1900'
```
```
targetDate <= '01/01/1900'
```
```
targetDate IS EMPTY
```
```
targetDate IS NOT EMPTY
```

Risky Date

Shows jobs at risk of going out of target based on their priority.

Syntax

riskyDate BETWEEN '01/01/1900' AND '01/01/2000'

```
riskyDate > '01/01/1900'
```
```
riskyDate >= '01/01/1900'
```
```
riskyDate < '01/01/1900'
```
```
riskyDate <= '01/01/1900'
```
```
riskyDate IS EMPTY
```
```
riskyDate IS NOT EMPTY
```

Invoice Date

The date on which an invoice was generated.

Syntax

invoiceDate BETWEEN '01/01/1900' AND '01/01/2000'

```
invoiceDate > '01/01/1900'
```
```
invoiceDate >= '01/01/1900'
```
```
invoiceDate < '01/01/1900'
```
```
invoiceDate <= '01/01/1900'
```
```
invoiceDate IS EMPTY
```
```
invoiceDate IS NOT EMPTY
```

User Functions

Owner

The current owner of the job. Can be used with the '' parameter to show all jobs owned by the logged-in user.

Syntax

```
owner = 'owner'
```
```
owner IN ('owner 1','owner 2')
```
```
owner NOT IN ('owner 1','owner 2')
```
```
owner IS EMPTY
```
```
owner IS NOT EMPTY
```

Reported By

The reporter of the job. Can be used with the '' parameter to show all jobs reported by the logged-in user.

Syntax

```
reportedBy = 'reporter'
```

reportedBy IN ('reporter 1','reporter 2')

reportedBy NOT IN ('reporter 1','reporter 2')

```
reportedBy IS EMPTY
```
```
reportedBy IS NOT EMPTY
```

Site and Contact Functions

Contact

The site contact associated with the job. Searches can be performed on the surname OR forename of the contact, but not both simultaneously. To search for both, use the AND keyword.

Syntax

contact ~ 'Contact forename OR contact surname'

contact !~ 'Contact forename OR contact surname'

Site Ref

The reference of the site associated with a job.

Syntax

```
siteRef ~ 'site ref'
```
```
siteRef !~ 'site ref'
```

Site Type

The site type of the site associated with a job (e.g. "house", "flat").

Syntax

```
siteType = 'type'
```
```
siteType IN ('type 1','type 2')
```
```
siteType NOT IN ('type 1','type 2')
```
```
siteType IS EMPTY
```
```
siteType IS NOT EMPTY
```

Post Code

The post code of the site associated with a job.

Syntax

```
postcode ~ 'Post Code'
```
```
postcode !~ 'Post Code'
```

Region

The region of the site associated with a job

Syntax

```
region = 'region'
```
```
region IN ('region 1','region 2')
```
```
region NOT IN ('region 1','region 2')
```
```
region IS EMPTY
```
```
region IS NOT EMPTY
```

Job Detail Functions

Job Reference

The Oneserve job reference

Syntax

```
jobRef = 'Job ref'
```
```
jobRef IN ('Job ref 1', 'Job ref 2')
```

jobRef NOT IN ('Job ref 1', 'Job ref 2')

```
jobRef IS EMPTY
```
```
jobRef IS NOT EMPTY
```

Client Reference

The client reference for the job.

Syntax

```
clientRef ~ 'Client Ref'
```
```
clientRef !~ 'Client Ref'
```

Client

The name of the client for whom the job is being undertaken.

Syntax

```
client = 'client'
```

client IN ('client name 1','client name 2')

client NOT IN ('client name 1','client name 2')

```
client IS EMPTY
```
```
client IS NOT EMPTY
```

Job Status

The current status of the job.

Syntax

```
jobStatus = 'job status'
```

jobStatus IN ('job status 1','job status 2')

jobStatus NOT IN ('job status 1','job status 2')

```
jobStatus IS EMPTY
```
```
jobStatus IS NOT EMPTY
```

Job Status Group

The current job status group associated with the job.

Syntax

```
jobStatusGroup = 'status group name'
```

jobStatusGroup IN ('status group name 1','status group name 2')

jobStatusGroup NOT IN ('status group name 1','status group name 2')

```
jobStatusGroup IS EMPTY
```
```
jobStatusGroup IS NOT EMPTY
```

Work Type

The work type in use for a job.

Syntax

```
workType = 'Work Type'
```

workType IN ('Work Type 1','Work Type 2')

workType NOT IN ('Work Type 1','Work Type 2')

```
workType IS EMPTY
```
```
workType IS NOT EMPTY
```

Work Log Type

The work log type in use for a job.

Syntax

```
workLogType = 'Work Log Type'
```

workLogType IN ('Work Log Type 1','Work Log Type 2')

workLogType NOT IN ('Work Log Type 1','Work Log Type 2')

```
workLogType IS EMPTY
```
```
workLogType IS NOT EMPTY
```

Coding

The job coding associated with the job.

Syntax

```
coding = 'code'
```
```
coding IN ('code 1','code 2')
```
```
coding NOT IN ('code 1','code 2')
```
```
coding IS EMPTY
```
```
coding IS NOT EMPTY
```

Coding Description

The description for the code associated with the job.

Syntax

```
codingDescription = 'code description'
```

codingDescription IN ('code description 1','code description 2')

codingDescription NOT IN ('code description 1','code description 2')

```
codingDescription IS EMPTY
```
```
codingDescription IS NOT EMPTY
```

Cause

The cause associated with a job.

Syntax

```
jobCostInvoiceNumber = '123'
```
```
jobCostInvoiceNumber IN ('123','456')
```

jobCostInvoiceNumber NOT IN ('123','456')

```
jobCostInvoiceNumber IS EMPTY
```
```
jobCostInvoiceNumber IS NOT EMPTY
```

Priority

The priority assigned to a job

Syntax

```
priority = 'priority name'
```
```
priority IN ('priority 1','priority 2')
```

priority NOT IN ('priority 1','priority 2')

```
priority IS EMPTY
```
```
priority IS NOT EMPTY
```

Service

The service associated with a job

Syntax

```
jobService = 'service'
```
```
jobService IN ('service 1','service 2')
```

jobService NOT IN ('service 1','service 2')

```
jobService IS EMPTY
```
```
jobService IS NOT EMPTY
```

Usage Type

The usage type assigned to a job.

Syntax

```
usageType = 'Usage type'
```

usageType IN ('jUsage type 1','Usage type 2')

usageType NOT IN ('Usage type 1','Usage type 2')

```
usageType IS EMPTY
```
```
usageType IS NOT EMPTY
```

Version Status

The status of the current version of the job

Syntax

```
versionStatus = 'Version status'
```

versionStatus IN ('Version status 1','Version status 2')

versionStatus NOT IN ('Version status 1','Version status 2')

```
versionStatus IS EMPTY
```
```
versionStatus IS NOT EMPTY
```

Team and Resource Functions

Team

The team assigned to undertake the work on a job.

Syntax

```
team = 'team name'
```
```
team IN ('team 1','team 2')
```
```
team NOT IN ('team 1','team 2')
```
```
team IS EMPTY
```
```
team IS NOT EMPTY
```

Team Type

The type of team assigned to the job. This will usually be "direct" or "subcontractor".

Syntax

```
teamType = 'team type'
```
```
teamType IN ('type 1','type 2')
```
```
teamType NOT IN ('type 1','type 2')
```
```
teamType IS EMPTY
```
```
teamType IS NOT EMPTY
```

Appointment Functions

Has Pending Appointments

Syntax

```
hasPendingAppointment = 'TRUE'
```
```
hasPendingAppointment = 'FALSE'
```

Pending Appointment Start Date

Syntax

pendingAppointmentStartDate BETWEEN '01/01/1900' AND '01/01/2000'

pendingAppointmentStartDate > '01/01/1900'

pendingAppointmentStartDate >= '01/01/1900'

pendingAppointmentStartDate < '01/01/1900'

pendingAppointmentStartDate <= '01/01/1900'

```
pendingAppointmentStartDate IS EMPTY
```

pendingAppointmentStartDate IS NOT EMPTY

Pending Appointment End Date

Syntax

pendingAppointmentEndDate BETWEEN '01/01/1900' AND '01/01/2000'

pendingAppointmentEndDate > '01/01/1900'

pendingAppointmentEndDate >= '01/01/1900'

pendingAppointmentEndDate < '01/01/1900'

pendingAppointmentEndDate <= '01/01/1900'

```
pendingAppointmentEndDate IS EMPTY
```
```
pendingAppointmentEndDate IS NOT EMPTY
```

In Progress Appointment Start Date

Syntax

inProgressAppointmentStartDate BETWEEN '01/01/1900' AND '01/01/2000'

inProgressAppointmentStartDate > '01/01/1900'

inProgressAppointmentStartDate >= '01/01/1900'

inProgressAppointmentStartDate < '01/01/1900'

inProgressAppointmentStartDate <= '01/01/1900'

```
inProgressAppointmentStartDate IS EMPTY
```

inProgressAppointmentStartDate IS NOT EMPTY

In Progress Appointment Actual Start Date

Syntax

inProgressAppointmentActualStartDate BETWEEN '01/01/1900' AND '01/01/2000'

inProgressAppointmentActualStartDate > '01/01/1900'

inProgressAppointmentActualStartDate >= '01/01/1900'

inProgressAppointmentActualStartDate < '01/01/1900'

inProgressAppointmentActualStartDate <= '01/01/1900'

inProgressAppointmentActualStartDate IS EMPTY

inProgressAppointmentActualStartDate IS NOT EMPTY

In Progress Appointment End Date

Syntax

inProgressAppointmentEndDate BETWEEN '01/01/1900' AND '01/01/2000'

inProgressAppointmentEndDate > '01/01/1900'

inProgressAppointmentEndDate >= '01/01/1900'

inProgressAppointmentEndDate < '01/01/1900'

inProgressAppointmentEndDate <= '01/01/1900'

```
inProgressAppointmentEndDate IS EMPTY
```

inProgressAppointmentEndDate IS NOT EMPTY

All Activities Complete

Syntax

```
isAllActivitiesComplete = 'TRUE'
```
```
isAllActivitiesComplete = 'FALSE'
```

Additional Appointment Notes

Searches for text results in the additional appointment notes field.

Syntax

```
additionalAppointmentNotes ~ 'Complete'
```

additionalAppointmentNotes !~ 'Complete'

Additional Appointment Reason

Searches for with additional appointments where the reason given matches the value provided.

Syntax

```
additionalAppointmentReason ~ 'carded'
```
```
additionalAppointmentReason !~ 'carded'
```

Additional Appointment Date

Syntax

additionalAppointmentDate BETWEEN '01/01/1900' AND '01/01/2000'

additionalAppointmentDate > '01/01/1900'

additionalAppointmentDate >= '01/01/1900'

additionalAppointmentDate < '01/01/1900'

additionalAppointmentDate <= '01/01/1900'

```
additionalAppointmentDate IS EMPTY
```
```
additionalAppointmentDate IS NOT EMPTY
```

Financial Functions

Invoice Number

The invoice number associated with an invoiced job

Syntax

```
invoiceNumber ~ '123'
```
```
invoiceNumber !~ '123'
```

Job Cost Invoice Number

The invoice numbers assigned to partially invoiced costs.

Syntax

```
jobCostInvoiceNumber = '123'
```
```
jobCostInvoiceNumber IN ('123','456')
```

jobCostInvoiceNumber NOT IN ('123','456')

```
jobCostInvoiceNumber IS EMPTY
```
```
jobCostInvoiceNumber IS NOT EMPTY
```