Search, Views & Groups
      Back to home
      1. Knowledge Base
      2. Desktop
      3. Search, Views & Groups

      Advanced Searching Functions

      Summary

      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.

       

      Guide

      dtoff()

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

      Syntax

      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

      Examples

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

      now()

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

      Example

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

      jobsWithAppointments()

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

      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)

      Examples

      • 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”) 

      jobsWithActivities()

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

      Syntax

      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

      Examples

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

      jobsWithEvents()

      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 support@oneserve.co.uk

      Syntax

      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

      Examples

      • 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

      eventDate()

      Displays the last date that a specified event occurred.

      Syntax

      eventDate({"code":"eventCode"})

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

      Example

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

      eventLastCreator()

      Displays the username of the user who recorded the job event

      Syntax

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

      eventCode = The code you wish to filter by

      Example

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

      img1.png