Skip to content

Bulk query grammar

Reference6 min read

See how Record Health Check runs the SOQL of a Query or Compare two queries Check for up to 200 records with one query instead of one query per record, which query shapes it accepts, and how to rewrite a query it rejects. The same rules apply to Applies When (Count Query).

Record Health Check never runs a Check’s SOQL once per record. Whether the card checks one record or a Flow checks 200, it rewrites the query so a single execution returns the rows for every record, then gives each record its own rows. That keeps a large run within Salesforce’s limit of 100 SOQL queries per transaction. The rewrite depends on how the query refers to the record, so only some query shapes are accepted.

Write each query with at most one {!record.…} token outside quotation marks, and put it in the query’s outer WHERE clause as an equality with the field on the left:

SELECT COUNT() FROM Contact WHERE AccountId = {!record.Id}

A query Record Health Check cannot rewrite returns UNABLE_TO_EVALUATE with UNSUPPORTED_BULK_QUERY_SHAPE. Configuration validation reports most of these before the Check runs. Record Health Check never falls back to one query per record.

The examples assume a Check Set on Account.

StrategyWhen it appliesExampleWhat Record Health Check runs
SELFThe query reads the records being checked: Id = {!record.Id}SELECT AnnualRevenue FROM Account WHERE Id = {!record.Id}One query for all records with Id IN (…); each record receives its own row
CHILD_DIRECTA lookup field on the queried object equals {!record.Id}SELECT COUNT() FROM Contact WHERE AccountId = {!record.Id}One query with AccountId IN (…); rows are grouped by AccountId
CHILD_PATHA relationship path equals {!record.Id}SELECT COUNT() FROM OpportunityContactRole WHERE Opportunity.AccountId = {!record.Id}One query with Opportunity.AccountId IN (…); rows are grouped by that path
TOKEN_INDIRECTA field equals another Id-valued token from the record, such as {!record.OwnerId}SELECT IsActive FROM User WHERE Id = {!record.OwnerId}Collects every record’s OwnerId, queries once, and gives each record the rows for its own owner. Records can share a row
SCOPE_INVARIANTThe query has no record tokenSELECT COUNT() FROM Case WHERE Priority = 'High' AND IsClosed = falseRuns once; every record receives the same result
ORDERED_PICK_IN_MEMORYA correlated query has an outer ORDER BY and LIMIT NSELECT Subject FROM Task WHERE WhatId = {!record.Id} ORDER BY ActivityDate DESC LIMIT 1Removes the LIMIT, fetches the candidate rows in one query, and keeps the first N rows for each record in the authored order

Ordered picks are never rewritten as MIN or MAX aggregates. The authored field selection and ordering stay intact, including a relationship selection such as Owner.Name, and an ordered pick can use several ORDER BY fields and a per-record limit greater than one.

RuleAcceptedRejected with UNSUPPORTED_BULK_QUERY_SHAPE
At most one token outside quotation marksWHERE AccountId = {!record.Id}WHERE AccountId = {!record.Id} AND OwnerId = {!record.OwnerId}
The token is unquotedWHERE AccountId = {!record.Id}A query whose only token is '{!record.Id}'
The comparison is =, with the field on the left and the token on the rightWHERE AccountId = {!record.Id}WHERE {!record.Id} = AccountId, WHERE AccountId != {!record.Id}, WHERE Id IN ({!record.Id})
The comparison is in the outer WHERE clause and joined to any other condition with ANDWHERE AccountId = {!record.Id} AND (Email = null OR HasOptedOutOfEmail = true)WHERE AccountId = {!record.Id} OR Email = null; a token inside a semi-join subquery
A token other than {!record.Id} holds an Id, such as a lookup field. This is checked when the Check runs, so validation does not report itWHERE Id = {!record.ParentId}WHERE Industry = {!record.Industry}
Without an outer ORDER BY, the only LIMIT allowed is LIMIT 1, which then applies to each record. A SELF query may use any LIMITWHERE AccountId = {!record.Id} LIMIT 1WHERE AccountId = {!record.Id} LIMIT 5

A fallback does not change the strategy. WHERE Id = {!record.ParentId fallback="001000000000000AAA"} is still TOKEN_INDIRECT; a record whose ParentId is blank uses the fallback Id. A record whose token is blank and has no fallback receives no rows, so If Query Finds No Records decides its result. Use a real Id that is valid for the referenced object as a fallback; the Id shown here only illustrates the grammar.

A correlated aggregate query is grouped by the correlating field, so each record receives its own total:

You writeEach record receives
SELECT COUNT() FROM Contact WHERE AccountId = {!record.Id}Its own count. A record with no matching Contacts receives 0, so If Query Finds No Records does not apply
SELECT SUM(Amount) total FROM Opportunity WHERE AccountId = {!record.Id}Its own total. A record with no matching Opportunities receives no row, so If Query Finds No Records decides the result

The same applies to COUNT(field), COUNT_DISTINCT(field), AVG, MIN, and MAX: only a bare COUNT() turns “no rows” into 0.

Max Query Rows limits the rows that one execution returns. Because one execution serves every record in the run, the limit applies to the combined rows for all records, not to each record:

RunQueryRows returnedResult with Max Query Rows 200
The card on one Account with 40 ContactsSELECT Email FROM Contact WHERE AccountId = {!record.Id}40Evaluated normally
A Flow checking 200 Accounts, 3 Contacts eachThe same query600Every record returns UNABLE_TO_EVALUATE / SCOPE_ROW_CAP_EXCEEDED
The same FlowSELECT COUNT() FROM Contact WHERE AccountId = {!record.Id}200 (one grouped row per Account)Evaluated normally

An ordered pick fetches every candidate row before it keeps the first N for each record, so the combined candidate rows count toward the limit even though each record keeps only N. To stay within the limit in a multi-record run, prefer an aggregate, filter the query more narrowly, raise Max Query Rows up to 2000, or send fewer records per request. A single record whose own rows exceed the limit returns ROW_LIMIT_EXCEEDED instead.

Before it runs the query, Record Health Check also confirms that the transaction has enough query rows left for the limit. If it does not, the Check returns GOVERNOR_LIMIT_RISK.

Rejected queryRewrite
SELECT COUNT() FROM Opportunity WHERE AccountId = {!record.Id} AND OwnerId = {!record.OwnerId}SOQL cannot compare two fields of the same row, and a second token cannot correlate the query. Use a Verify with Apex Check
SELECT COUNT() FROM Account WHERE Industry = {!record.Industry}A text value cannot correlate the query. Use a Verify with Apex Check, or correlate on an Id and filter by a fixed value
SELECT Id FROM Contact WHERE AccountId = {!record.Id} LIMIT 5Add the order that defines which five: … ORDER BY CreatedDate DESC LIMIT 5
SELECT Id FROM Contact WHERE AccountId IN (SELECT Id FROM Account WHERE ParentId = {!record.Id})Correlate through the relationship path in the outer WHERE: SELECT Id FROM Contact WHERE Account.ParentId = {!record.Id}
SELECT COUNT() FROM Case WHERE AccountId = {!record.Id} OR Priority = 'High'Decide what the OR should mean for each record and express it inside an AND group: … WHERE AccountId = {!record.Id} AND (Priority = 'High' OR IsEscalated = true). A condition that must match rows outside the record needs its own Check