Follow

Useful Advanced Queries and Functions

Summary | Guide | Date Diff (dtoff) | Events in OQL Function Columns | Costs in OQL Function Columns | Related Articles

Summary

Advanced search queries can be very useful when trying to interrogate your Oneserve data. They can be used to search for specific events, timelines, and users to give insight into how jobs are being managed. Oneserve Query Language (OQL) is also useful in constructing views to show information at-a-glance. Below are some useful examples of OQL in action.

Guide

Date Diff (dtoff)

Jobs using reported date

  • Jobs received between XX and XX

reportedDate <dtoff(-XXd) and reportedDate > dtoff(-XXd)

  • Job received over XX days

reportedDate <= dtoff(-XXd)

  • Jobs received under XX days

reportedDate >= dtoff(-XXd)

Completed Jobs

  • Completed within the last XX days

EndDate >= dtoff(-XXd)

Additional Appointment 

  • Additional Appointment over XX days

AdditionalAppointmentReason ~ 'Carded' and job not in jobsWithEvents (RA, Carded, XXd)

Back to top

Events in OQL Function columns

Days Since Event Was Added

Using Function Columns, you can show how many days since an event occurred on a job in a view. This can be used to show how many days a job has been at a particular stage using the sig event applied when a job moves between transitions.

  • Click on the cog icon on the search page
  • Add a *Oql Function Column 
  • Enter a name for the column by clicking on the *Oql Function Column 
  • Click on the --empty-- field to enter the function
  • Enter the following: dateDiff({"endDate": "CWOPR_lastevent", "dateFormat":"d"})

You can replace CWOPR with any event code you wish to interrogate

  • Use the green tick to save the details

img 1

Which User Added an Event

  • Click on the cog icon on the search page
  • Add a *Oql Function Column 
  • Enter a name for the column by clicking on the *Oql Function Column 
  • Click on the --empty-- field to enter the function
  • Enter the following: EventLastCreator( {"eventCode":"CWOPR"})

You can replace CWOPR with any event code you wish to interrogate

  • Use the green tick to save the details

Date Event Was Added

  • Click on the cog icon on the search page
  • Add a *Oql Function Column 
  • Enter a name for the column by clicking on the *Oql Function Column 
  • Click on the --empty-- field to enter the function
  • Enter the following: eventDate({"code":"CWOPR"})

You can replace CWOPR with any event code you wish to interrogate

  • Use the green tick to save the details

Back to top

Costs in OQL Function Columns

The search views has a Total Cost and Total Sales column, you can create new columns to show cost totals for individual cost types. This example shows how you can create a column to show Total Activity cost against a job. 

  • Click on the cog icon on the search page
  • Add a *Oql Function Column 
  • Enter a name for the column by clicking on the *Oql Function Column 
  • Click on the --empty-- field to enter the function
  • Enter the following: costSum({"costType": ["Activity"]})

You can replace Activity with other cost types, such as Labour

  • Use the green tick to save the details 

img 2

Back to top

Related Articles

Advanced Searching Functions

Advanced Column Display

OQL Keywords and Operators

Back to top

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