Advanced Searching Functions



Advanced searching makes use of the Oneserve Query Language (OQL) to interrogate data within your Oneserve application, allowing you to create bespoke views with specific data. This article contains a list of pre-defined functions that can be used with OQL when performing queries using the Advanced Searching mode.



The date offset (dtoff()) function is used to match events which occurred in a specific date range


offset = time to offset by in the format ax, where a is a positive or negative integer and x is the time type identifier which can be: m = minutes, h = hours, d = days


  • Return all jobs that were reported in the last 3 days
reportedDate >= dtoff(-3d)
  • Return all jobs which were reported more than 5 days ago
reportedDate < dtoff(-5d)
  • Return all jobs which are planned to start more than 7 days in the future
plannedStartDate > dtoff(7d)


Used to get the current date/time for use in date comparisons.


  • Return all jobs where the planned start date is in the past
plannedStartDate < now()


Allows searching for Jobs with appointments by Status and Resource.  Must be used with the job IN OQL syntax


jobsWithAppointment ( status, resource, activityCount )

status = the status of the appointment: Pending, In Progress, Complete, All

resource = the name of the resource to whom the appointment is assigned, or All

activityCount = count of the number of activities and resources - (optional argument)


  • Return all jobs that have Pending appointments for any resource
job IN jobsWithAppointments(pending,all)
  • Return all jobs that have In Progress appointments assigned to Colin Amberson
job IN jobsWithAppointments("in progress","Colin Amberson")
  • Return all jobs that have a resource count of 2 on the activity. The jobs will need to have an appointment and assigned to any supplier and any resource
job IN jobsWithAppointments(ALL,ALL,”2”) 


Allows searching for Jobs that have specified activities. Searches can be done by activity code, status, team and resource.


jobsWithActivities ( code, completed, team, resource )

code = activity code or All

completed = true or false

team = name of team or All

resource = name of resource or All


  • Return jobs which have activities with code 603103 and that are completed
job IN jobsWithActivities(603103,true,all,all)
  • Return jobs which have any incomplete activities assigned to Direct Team
job IN jobsWithActivities(all,false,"Direct Team",all)


Allows searching for Jobs that have had specified events occur on them.

If you are not sure what codes you use for particular job events please contact


jobsWithEvents ( eventCode, noteDescription, occurredWithin )

eventCode = the code of the event

noteDescription = (OPTIONAL) the description of a selectable note that is attached to the event, e.g. Carded, At Customer Request or All

occurredWithin = (OPTIONAL) time the event occurred within - specified in the format ax, where a is a positive or negative integer and x is the time type identifier. Identifiers can be: m = minutes, h = hours, d = days or All

Note: noteDescription and occurredWithin are both OPTIONAL parameters but if one if specified you will need to include both


  • Returns jobs which have had the VERSU event occur. (Change VERSU to be the code you would like to use)
job IN jobsWithEvents(VERSU)
  • Returns job withe the code SA, the description contains 'At Customer Request' and the occurs within is set to all.
job IN jobsWithEvents(SA,"At Customer Request",All)
  • Returns jobs which have had the SA event occur within the last 3 days.
job IN jobsWithEvents(SA,All,3d)

Columns to Show Event Date and Agent

Use the cog icon on the search screen, select the OQL Function Column. Add a name and use the green arrow, for the function field use the following OQL


Displays the last date that a specified event occurred.



eventCode = the code of the event to show the date for


  • Returns the last date the "VERSU" event occurred on the job.
eventDate({"code": "VERSU"})


Displays the username of the user who recorded the job event


EventLastCreator( {"eventCode":"eventCode"})

eventCode = The code you wish to filter by


  • Returns the last user who added the "VERSU" event on the job
EventLastCreator( {"eventCode":"VERSU"})