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
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