COQL

Overview

Bigin supports CRM Object Query Language (COQL) as a Query API to filter and fetch the specific records. The Bigin COQL Query API is based on SQL that utilizes the SELECT query for retrieving specific information from a Bigin account. By constructing your own customized queries, you can access a wide range of possibilities for searching and fetching records.

Also, the COQL Query API simplifies the process of filtering and fetching records, eliminating the need for creating custom views and passing Custom View ID (cvid) through a API request. Using this API, you can retrieve up to 200 records per API call and 10000 records through multiple requests.

The COQL Query API allows you to create simple yet powerful SELECT queries that includes the following aspects:

  • While constructing a SELECT query, use field API names as column names and module API names as table names.
  • The COQL syntax consists of SELECT statement combined with the optional clauses WHERE, FROM, ORDER BY, LIMIT, and OFFSET clauses.
  • The COQL keywords are not case-sensitive. That means, SELECT and select are similar.
  • When no ORDER BY clause is specified in the query, the system sorts the records in ascending order based on the record ID by default.
  • The default values for LIMIT and OFFSET are 200 and 0 respectively. To navigate and retrieve the subsequent set of records, you can adjust these values. With a LIMIT of 200 and an OFFSET of 9800, you can fetch a maximum of 10,000 records using multiple API calls.

COQL Advantages

  • COQL is built on SQL syntax and it's simple to construct.
  • You don't have to create custom views based on specific criteria and pass custom view ID in the request. Instead, you can directly use COQL's SELECT query with different comparators in the input to fetch records that match the criteria.
  • The supported COQL comparators cover a wide range of search possibilities. For example, you can use the "in" comparator to search for records from a list of values, which isn't possible with the Search Records API.
  • It's simple to join or relate two modules using lookup fields through the dot (.) notation.
  • When establishing a relation between two modules, there's no requirement to explicitly specify the alias of the module. This is because the API names are unique for each module.

COQL Limitations

  • This API allows you to fetch a maximum of 10000 records.
  • COQL only allows the usage of SELECT Query for retrieving records from a module.
  • The SELECT queries allow a maximum of two relations (joins) while retrieving module records. If a Select query contains more than two relations, the API only validates the last two relations.
  • The WHERE clause's criteria must be enclosed within brackets to avoid ambiguity. For example, (A and (B and C)) or ((A and B) and C) should be used for three or more criteria.
  • The WHERE clause can contain up to 25 criteria.
  • Criteria cannot involve multi-line fields like "Description," "Terms and Conditions," and "Comments."
  • The SELECT column of the query can include a maximum of 50 field_API_names, such as First_Name, Last_Name, Full_Name, up to 50 field_API_names.
  • The following features are not supported in COQL:
    • Linking modules
    • Multi-Select lookup fields
    • Participants field in Events module
    • File Upload fields
    • Tags
    • Notes
    • Attachments