Useful Advanced Queries and Functions

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)

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.

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
  
  

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

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