Skip to content
Developerhome
Product homepage
Sage Network API

Querying with Searchlight

  Less than to read

When querying data, Sage Network API uses Searchlight, a database query language designed to be comfortable for developers who are familiar with SQL. This language provides a comfortable syntax that is easy to understand, while it is still a compiled language that can execute on a variety of different database technologies.

Searchlight allows simple queries to be written in text. For example, if you wanted to fetch all companies whose name begins with the letter A:

GET /api/v1/companies/query?filter=companyName startswith A

In this example, Searchlight understands that you are querying based on the companyName field. You specified a rule called startswith, and you provided the parameter A. Searchlight can then compile this query into a task and search the Sage Network for data that matches your request.

Basic Comparisons

Searchlight supports the following basic comparison rules. You can either use the symbol for this rule, or you can use the short textual abbreviation if you prefer. Using the short text can sometimes make your code easier to write since text does not need to be escaped when constructing a URL.

  • Equals (=, EQ)
  • Greater Than (>, GT)
  • Greater Than Or Equal (>=, GE)
  • Less Than (<, LT)
  • Less Than Or Equal (<=, LE)
  • Not Equal (!=, NE, <>)

Complex Comparisons

Searchlight supports advanced comparisons as well. You can use logic such as:

  • BETWEEN - Test whether a value is within two other values.
  • IN - Test whether a field is within a range of possible values. For example, you can filter for invoices in one of three possible currencies: filter=currencyCode IN ('USD', 'EUR', 'GBP')
  • CONTAINS - Test whether a text string contains a specific value. This filter rule is case insensitive. For example, you can fetch comments that contain “payment”: filter=noteText contains 'payment'
  • STARTSWITH - Test whether a text string starts with a specific value. This filter rule is case insensitive.
  • ENDSWITH - Test whether a text string ends with a specific value. This filter rule is case insensitive.
  • ISNULL - Check whether a value is specifically null, or empty. This filter rule can determine whether an element of data is missing.

Relative Dates

Sometimes you want to know what records were modified within a time window. Besides date filters such as date gt 2021-01-01, you can also use relative dates such as NOW, TODAY, TOMORROW, and YESTERDAY. You can use these constants just as if they were regular date values: date gt YESTERDAY or dueDate between today and tomorrow.

Relative date logic can be extremely useful when creating a webhook. You can modify any of the constant dates using math, such as TODAY - 7 would be seven days before today. This allows you to create filters for a webhook such as the following:

  • Notify me anytime a user modifies an invoice that is due in the upcoming 30 days: dueDate > TODAY + 30
  • Notify me when an invoice is created that has a due date in the past: dueDate < TODAY
  • Notify me when the outstanding balance changes on any invoice past due by 90 days or more: dueDate < TODAY - 90

All relative date computations are done using UTC time.

Conjunctions

Searchlight allows you to construct more complex queries by chaining filter statements together. Just like in mathematics, you can wrap query elements in parenthesis and combine them. Each criteria can be joined to other criteria using AND and OR logical operators.

An example of multiple conjunctions could look like the following:

GET /api/v1/companies/query?filter=(companyName startswith 'a' OR companyName startswith 'b') AND currencyCode eq 'USD'

Nested Fetch

Searchlight provides you with the ability to fetch multiple data sets with a single query. For example, you can specify ?include=Notes on any object in the database to retrieve user-provided comments related to that object.

Using nested fetch can allow you to retrieve multiple data sets with a single call and avoid multiple roundtrip calls to the server.

To see a list of available nested data objects for an API, see the documentation for the include parameter for a GET or QUERY call.

Sorting

Searchlight automatically sorts your records using the order parameter. You can specify a list of fields and rules such as “ascending” or “descending”. For simplicity, these operators are referred to as ASC and DESC.

For example, to sort based on company status first and name second, you might use this query:

GET /api/v1/companies/query?order=companyStatus desc,companyName asc

Pagination

All requests to the Sage Network API are automatically filtered for pagination. You can specify in your query the size of each page and the page number to retrieve. All counts are zero-based.

To retrieve 100 companies at a time, and to use page 12, you would use this query:

GET /api/v1/companies/query?pageNumber=12&pageSize=100

The results for each request include the total number of records matching your filter. For ease of development, the results also include the page number and page size:

{
  "totalCount": 0,
  "pageSize": 0,
  "pageNumber": 0,
  "records": [ ... ]
}