Query Console and COQL

Settings → Query Console runs COQL (CRMSix Object Query Language) queries and shows the results as a table you can export. It’s a quick way to answer questions a report doesn’t, such as “which accounts have more than five open cases?”. Queries only read: they never change anything.

Run a query

  1. Go to Settings → Query Console.
  2. Type a query, or pick one from the Examples… list. The fields of the object you’re querying are listed beside the editor.
  3. Choose Run, or press Ctrl / ⌘ + Enter.
  4. Use Export CSV to download the results. Your recent queries are kept in this browser, under the recent-queries list.

A query sees exactly what you can see in CRMSix: your organization’s records that sharing lets you see, and only the objects and fields your profile allows. The console needs the Query console permission. Integrations can run the same queries through the API (POST /api/query).

The shape of a query

SELECT field, field, lookup.field, …   (or *)
FROM object
WHERE condition [AND | OR | NOT …]
GROUP BY field, …
ORDER BY field [ASC | DESC] [NULLS FIRST | LAST], …
LIMIT n          (default 200, at most 2000)
OFFSET n

The object is cases, leads, opportunities, accounts, contacts, users or a custom object (projects__c). Without ORDER BY, the newest records come first.

Fields and lookups

  • Use API names, as listed beside the editor: case_number, status, custom fields such as region__c.
  • id is the record’s 14-character ID. name on leads and contacts is the full name.
  • A lookup (owner, account, contact, support team, created by…) returns the related record’s ID. Add a field after a dot to read it: owner.name, assigned_to.name on cases, account.name, contact.email, queue.name.

Conditions

Write Meaning
= != < <= > >=Compare; text comparisons ignore case
IN ('a', 'b'), NOT IN (…)One of a list
LIKE '%printer%'Contains (% matches anything)
IS NULL, IS NOT NULLEmpty or not
owner = CURRENT_USERYour own records (assigned_to on cases)

Quote text with single quotes; numbers and true / false without. Group conditions with brackets.

Dates

Write a date as 2026-01-31, or use TODAY, YESTERDAY, THIS_WEEK, LAST_MONTH, THIS_QUARTER, THIS_YEAR, LAST_N_DAYS:30, NEXT_N_DAYS:7 and similar. created_at = LAST_N_DAYS:7 means within that period; < and > mean before and after it. Days follow UTC.

Totals

COUNT(), COUNT(field), COUNT_DISTINCT(field), SUM, AVG, MIN and MAX, with GROUP BY. Name a total by writing a word after it, and sort by that name.

Examples

-- Open high-priority cases, oldest first
SELECT case_number, subject, account.name, assigned_to.name
FROM cases
WHERE status != 'Closed' AND priority = 'High'
ORDER BY created_at ASC

-- Cases per status
SELECT status, COUNT(id) total FROM cases GROUP BY status ORDER BY total DESC

-- Pipeline by owner this quarter
SELECT owner.name, SUM(amount) pipeline
FROM opportunities
WHERE close_date = THIS_QUARTER
GROUP BY owner.name ORDER BY pipeline DESC

-- Leads created in the last 30 days without an email address
SELECT name, company, source FROM leads
WHERE created_at = LAST_N_DAYS:30 AND email IS NULL

Limits

  • Up to 2,000 rows per query (200 unless you set LIMIT); use OFFSET for more.
  • A query stops after 10 seconds: add conditions or a LIMIT to speed it up.
  • Queries only read. To change records in bulk, use Data Import.