Advanced Search Full Syntax

Avatar
Oneserve Product Team
  • Updated
Follow

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

The date on which an invoice was generated.

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

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

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

  • site ~ 'site ref'
  • site !~ '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