Autotask Plugin Custom Query

A Custom Schema's Query for the Autotask plugin relies on filter expressions in JSON data format rather than SQL. This approach harnesses the power of JSON data filtering through a flexible and efficient method provided by the Autotask API.

Creating an Autotask Custom Query

Custom Query for Autotask plugin's consists of 2 lines:

  • Line 1: Entity ID/Object

  • Line 2: Filter

Custom Query Example:

EntityID
{"Filter":[{"field":"Id","op":"gte","value":0}]}

Warning: Users who create a Custom Query with more than the 2 specified lines will encounter an error when validating their query via the Validate Query button: AT Validation Error

Entity ID/Object

An Entity ID is a unique identifier associated with each entity or object in the JSON dataset. When querying by Entity ID, the user will obtain data exclusively associated with the defined Entity ID.

Filter

JSON filtering is used to narrow the scope of data from a JSON dataset based on user defined criteria. Filters consist of three essential components: field, operator, and value. These components are used to define the criteria for including or excluding data from an Entity ID.

Field: The field refers to the property to which the user wants to have the filter applied.

Operator: The operator defines the type of logical action the user wants to have leveraged against the selected field.

Please reference the table below for filter operators, and their definitions, that are accepted in JSON.

Operator Definition
eq Requires that the field value match the exact criteria provided
noteq Requires that the field value be anything other than the criteria provided
gt/gte Requires that the field value be greater than or equal to the criteria provided
it/ite Requires that the field value be less than or equal to the criteria provided
beginsWith Requires that the field value begin with the defined criteria
endsWith Requires that the field value end with the defined criteria
contains Allows for the string provided as criteria to match any resource that contains the string in its value
exist Enter exist to query for fields in which the data is not null
notExist Enter notExist to query for fields in which the specified data is null
in With this value specified, the query will return only the values in the list array that match the field value you specify
notIn With this value specified, the query will only return the values in the list array that do not match the field value you specify

Value: The value is user defined criteria that the operator references when applying the filter against a field.

Users leveraging version 2.1.0 and above of the Autotask plugin can precisely control the date range of their results by utilizing the TODAYMINUS_N_DAYS value.

  • Users can adjust the time range of their queries using TODAYMINUS_N_DAYS. For instance, setting N as 0 retrieves results only for the current day, while N as 1 includes results from the current and previous day, and so on.

  • To maintain data integrity and query efficiency, N is limited to values between 0 and 999.

  • TODAYMINUS_N_DAYS can only be applied to date/time fields within the Custom Query's filter.

For additional information see Making Basic Query Calls to the REST API

Autotask Plugin Custom Query Example

The following Custom Query for the Autotask plugin example effectively equates to a select * statement in SQL; the filter will select all records within the “Holidays” Entity ID/Object that has an ID with a value greater than or equal to 0:

Holidays
{"Filter":[{"field":"Id","op":"gte","value":0}]}
  • "field": "Id" is the field against which the user would like to perform the filter
  • "op": "gte" defines greater than or equal to as the selected operator
  • "value": 0 defines 0 as the value the operator will reference when performing its action against the field

When applying the above filter to the “Holidays” Entity ID/Object, users can expect to be returned the following: Custom Query Sample

Autotask Plugin Custom Query Example with Dynamic Date Filtering

TicketCharges
{"Filter":[{"field":"createDate","op":"gte","value":":"TODAYMINUS_10_DAYS"}]}

Dynamic Date Filter

Upon execution, the returned results will include Ticket Charges spanning from the current day to the preceding 10 days.