Follow

Advanced Searching Functions

Summary | Guide | dtoff() | now() | jobsWithAppointments() | jobsWithActivities() | jobsWithEvents() | Columns to Show Event Date and Agent | eventDate() | eventLastCreator() | Related Articles

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.

Back to top

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)

Back to top

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

Back to top

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

Back to top 

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)

Back to top 

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)

Back to top

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"})

Back to top

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"})

img 1

Back to top

Related Articles

OQL Keywords And Operators

Advanced Column Display

Creating a Search View

Back to top

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request