Compare two queries
Configure a Compare two queries Check, which compares the result of one SOQL query with the result of a second SOQL query: two counts, two single values, or two lists. The sections below explain the required SOQL, compatible result settings and operators, access behavior, limits, and outcomes; for each field’s size, default, and help text, see the Check field reference.
Both queries are stored on the same Check record in Setup: Source Query is the left side and Comparison Query is the right side. Write and test both queries in a sandbox before activation; when one formula or one query can express the requirement, use that instead.
Required Compare two queries settings
Section titled “Required Compare two queries settings”| Setup field | API name | Requirement |
|---|---|---|
| Evaluation Type | EvaluationType_ | Compare two queries: COMPARE_ |
| Source Query | SourceQuery_ | Required left-side SOQL template |
| Source Query Field | SourceQueryField_ | Selected field or aggregate alias; blank for bare COUNT() |
| Comparison Query | ComparisonQuery_ | Required right-side SOQL template |
| Comparison Query Field | ComparisonQueryField_ | Selected field or aggregate alias; blank for bare COUNT() |
| How To Read Query Results | QueryResultHandling_ | One row or aggregate or Compare as lists |
| Comparison Operator | ComparisonOperator_ | Operator compatible with the selected mode |
Expected Value Comes From is not used. The Comparison Query always supplies the Expected value.
Single-value mode
Section titled “Single-value mode”Choose One row or aggregate (ONE_RESULT) to compare Number, Text, Date, Date/Time, or Checkbox
values. Common examples are two bare COUNT() queries or two aggregate queries with aliases.
- Leave both query-field settings blank for bare
COUNT(). - For an aliased aggregate, enter each alias in its corresponding Query Field.
- For a selected field, each query must produce the single value expected by the comparison.
- Use an equality, ordering, or contains operator: Equal to, Not equal to, Greater than,
At least, Less than, At most, Contains text, or Does not contain text.
Is empty and Is not empty return
UNABLE_TO_EVALUATEwithINVALID_OPERATOR, because the Comparison Query always supplies an Expected value.
For example, to require a Contact Role on every open Opportunity of an Account, count the open
Opportunities that have at least one Contact Role in Source Query, with Source Query Field
coveredCount:
SELECT COUNT_DISTINCT(OpportunityId) coveredCountFROM OpportunityContactRoleWHERE Opportunity.AccountId = {!record.Id} AND Opportunity.IsClosed = falseCount all open Opportunities in Comparison Query, and leave Comparison Query Field blank:
SELECT COUNT()FROM OpportunityWHERE AccountId = {!record.Id} AND IsClosed = falseWith Comparison Operator set to Equal to, the Check passes when the two counts match. The complete configuration, including what happens when the Account has no open Opportunities, is in Check that every open Opportunity has a Contact Role.
List mode
Section titled “List mode”Choose Compare as lists: COMPARE_AS_LISTS and one of these operators:
| Setup label | API value | Pass condition |
|---|---|---|
| Lists overlap | LISTS_ | At least one normalized value occurs in both lists |
| Lists contain all | LISTS_ | The Comparison/Expected list contains every value in the Source/Found list; it may contain additional values |
| Lists match exactly | LISTS_ | Both lists contain the same values the same number of times |
List matching ignores letter case. For example, Chicago and CHICAGO match. In single-value
mode, Equal to and Not equal to also ignore letter case for text, but Contains text and
Does not contain text are case-sensitive. Select the list column with each Query Field and configure the
result to use when either query finds no records.
No rows, empty values, and row caps
Section titled “No rows, empty values, and row caps”| Setup field | API name | Behavior |
|---|---|---|
| If Query Finds No Records | NoRowsResult_ | Determines the outcome when a required list/query side has no records |
| If Field Value Is Empty | EmptyValueHandling_ | Ignores, preserves as blank, or forces no match for empty selected values |
| Max Query Rows (1-2000) | MaxQueryRows_ | Applies to returned rows; defaults to 200, maximum 2000 |
Both queries execute in the same evaluation transaction. Keep their selected fields and row counts as small as the comparison requires.
Each side is capped independently. Record Health Check probes one row beyond Max Query Rows for
the Source Query and again for the Comparison Query; either side exceeding the cap returns
UNABLE_TO_EVALUATE / ROW_LIMIT_EXCEEDED before comparison. The detail names the configured cap,
not the true row count.
A bare COUNT() query always returns one aggregate row, even when the count is zero. If Query
Finds No Records therefore applies to a query that returns zero rows, not to a COUNT() value of
zero.
SOQL templates and security
Section titled “SOQL templates and security”- Both templates support current-record merge tokens such as
{!record.Id}and parent paths. Add a quotedfallbackattribute when an optional lookup can be blank (for example{!record.ParentId fallback="001000000000000AAA"}). - Use API names in SOQL and aliases in the matching Query Field setting.
- Queries run using the current user’s effective record, object, and field access.
- Access, invalid-query, alias, value-conversion, and incompatible result problems return
UNABLE_TO_EVALUATEwhen Record Health Check can safely return a result. - Unexpected Apex failures return
ERROR; Salesforce governor limits can still stop the current Apex transaction.
Outcomes and testing
Section titled “Outcomes and testing”Test unequal/equal values, each ordering boundary, one empty side, both empty sides, blank selected values, duplicate list values, case differences, the configured row limit, and access denied to each query source. Also test every applicability and prerequisite path.
Use stable status and reasonCode values for automation. Found represents the resolved Source
Query value; Expected represents the resolved Comparison Query value.
Compatibility and deprecation
Section titled “Compatibility and deprecation”The Apex response does not contain a separate Compare-two-queries version number. Its global Apex
types are the compile-time contract supplied by the installed package. Flow responses currently
report contract 2.0, and Platform Events report their separate contract 1.0. A future field can
be added without changing existing fields; removing or renaming a field, Status, operator, or Reason
Code requires a new contract version. No Compare-two-queries field is currently deprecated.