Query API - Limitations

  • You can fetch up to 10,000 records using pagination with LIMIT and OFFSET. Each API call can retrieve a maximum of 2,000 records. To fetch 10,000 records, you need to make five API calls. Refer to the Pagination section to learn how to fetch more than 10,000 records.
  • You can use only Select Query in COQL to get records from a module.
  • You can use a maximum of two relations(joins) in a select query to get the records from a module.
  • If you use more than two relations in a select query, system validates only the last two relations.
  • User must have the scopes required to access the base module.
  • Only the following comparators(highlighted in bold) are supported in COQL:
    • = (equal)
    • > (greater than)
    • < (less than)
    • >= (greater_equal)
    • <= (less_equal)
    • is null
    • is not null
    • between and
    • in (up to 50 values)
    • not in (up to 50 values)
    • like (used only for starts_with, ends_with, and contains)
    • not like(used for not_contains)
  • The criteria specified in the WHERE clause must be enclosed within brackets properly to avoid ambiguity. If WHERE has more than two criteria such as A, B, and C, use them as (A and (B and C)) or ((A and B) and C).
  • You can include a maximum of 25 criteria in the WHERE clause.
  • You can use a maximum of 50 columns i.e, 50 field_API_names in the select column of the query. Example: Select First_Name, Last_Name, Full_Name...upto 50 field_API_names.
  • When constructing queries, it is crucial to parse special characters and SQL reserved words properly to avoid issues. They should be enclosed within quotes for proper handling.

The following are not supported in COQL:

  • Territory fields
  • Multi-Select lookup fields
  • You cannot use multi-line fields such as "Description", "Terms and Conditions", "Comments" etc., in criteria.
  • Line items(Pricing_Details and Product_Details) in Price Books and Quotes modules
  • Participants field in Events module
  • File Upload fields
  • Attachments

Notes

  • You can query subform data using its corresponding module API name, as the subform is treated as a separate module. 

    Example:

    { 

    "select_query": "select Parent_Id.Subject, Parent_Id.Grand_Total, Product_Name, Quantity, List_Price, Discount, Tax, Net_Total from Purchase_Items where Parent_Id.PO_Number = '101' " 

    } 

    The query retrieves data from the Purchase Items subform, along with related information from its parent module, Purchase Orders. It retrieves fields like Subject, Grand Total, Product Name, Quantity, List Price, Discount, Tax, and Net Total, where the Purchase Order Number (PO_Number) is 101.

  • You can query multi-select lookup fields (MxN fields) through the corresponding multi-select lookup module, instead of querying them directly from the parent module.