Query API - Limitations

  • You can fetch up to 1,00,000 records using pagination with LIMIT and OFFSET. Each API call can retrieve a maximum of 2,000 records. To fetch 1,00,000 records, you need to make 50 API calls. Refer to the Pagination section to learn how to fetch more than 1,00,000 records.
  • A maximum of 10 fields can be given in the ORDER BY clause.
  • Territory fields are only supported in the SELECT column.
  • Tuple is not supported in COQL.

  • Duplicate fields in the ORDER BY and the GROUP BY clauses will result in a "DUPLICATE_DATA" error in the response.

    Example: 

    {"select_query":"select id from Contacts where id is not null order by id,id"}
  • A maximum of four fields can be given in the GROUP BY clause.
  • A maximum of five aggregate fields can be given in a query.
  • You can use only Select Query in COQL to get records from a module.
  • A maximum of five base JOINs can be applied in a single query. Refer to the Base JOIN section for more information. 
  • A maximum of 15 SELECT clause JOINs can be applied in a single query. Refer to the SELECT column JOIN section for more information.
  •  Image upload field is not supported
  • 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 100 values)
    • not in (up to 100 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 include a maximum of 500 columns in your query, meaning  you can include up to 500field_API_names in the SELECT clause. For example: SELECT First_Name, Last_Name, Full_Name,... (up to 500 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.