Compare two queries examples
Use these examples to build Record Health Checks with the Compare two queries Evaluation Type. A Check of this type runs two SOQL queries and compares their results: two numbers, such as covered and total Opportunities, or two lists, such as Opportunity owners and Account Team members. Choose it when both sides of the comparison come from related records.
Each example is a set of instructions; the package does not install these Checks. You create each one in Setup, in Custom Metadata Types → Record Health Check → Manage Records.
Choose an example
Section titled “Choose an example”| Example | Question it answers | What it teaches |
|---|---|---|
| Check that every open Opportunity has a Contact Role | Do all open Opportunities have a Contact Role? | Two counts compared with Equal to, an aggregate alias, and a count-query applicability rule |
| Check that open pipeline includes a previously purchased Product | Does open pipeline share a Product with past purchases? | Two lists compared with Lists overlap |
| Check that the Account Team includes every open Opportunity owner | Is every open Opportunity owner on the Account Team? | Two lists compared with Lists contain all |
Fields every Compare two queries Check uses
Section titled “Fields every Compare two queries Check uses”| Field | What to enter |
|---|---|
| Source Query | The first query. Its result is shown as Found. |
| Source Query Field | The field or aggregate alias to read from the first query. Leave blank for a bare COUNT(). |
| Comparison Query | The second query. Its result is shown as Expected. |
| Comparison Query Field | The field or aggregate alias to read from the second query. Leave blank for a bare COUNT(). |
| How To Read Query Results | One row or aggregate to compare two values, or Compare as lists to compare two lists. |
| Comparison Operator | For two values: Equal to, Not equal to, Greater than, At least, Less than, At most, Contains text, or Does not contain text. For two lists: Lists overlap, Lists contain all, or Lists match exactly. |
| If Query Finds No Records | What to report when a query returns no rows. Required with Compare as lists. |
Leave Expected Value Comes From blank; the Comparison Query is the expected side.
Know how empty results behave
Section titled “Know how empty results behave”- A bare
SELECT COUNT()always returns a number, even 0. - An aggregate such as
COUNT_DISTINCT(field),COUNT(field), orSUM(field)returns no rows when nothing matches. If Query Finds No Records then decides the result; when it is blank, the Check is skipped. - With Compare as lists, an empty list on either side uses If Query Finds No Records.
The list operators pass in these cases:
| Operator | Passes when |
|---|---|
| Lists overlap | At least one value appears in both lists. |
| Lists contain all | The Comparison Query list contains every value in the Source Query list. It can contain others. |
| Lists match exactly | Both lists contain the same values, each the same number of times. |
List comparisons ignore capitalization. They also ignore repeated values, except Lists match exactly, which counts them.
Know when to choose another Evaluation Type
Section titled “Know when to choose another Evaluation Type”Use a Query example when one side is a fixed value or a value from the open record. Use an Apex example when the comparison needs custom logic.