Skip to content

Query Check examples

Example4 min read

Use these examples to build Record Health Checks with the Verify with a query Evaluation Type. A query Check runs one SOQL query, such as a count of an Account’s Contacts or a list of its open Opportunities, and compares the result with an expected value. Choose it when the requirement depends on 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.

ExampleQuestion it answersWhat it teaches
Check that an Account has at least one ContactDoes the Account have a Contact?A COUNT() query compared with a fixed number
Check that every open Opportunity has a Next StepDoes each open Opportunity have a Next Step?Every record passes, blank values that fail, and skipping when there are no records
Check that an Account has a significant open OpportunityIs any open Opportunity worth more than 10% of Annual Revenue?Any record passes, an expected value from a formula, and an applicability formula
Check that proposal-stage Opportunities meet a minimum AmountIs every proposal worth at least 25,000?At least with a fixed value, and custom Found and Expected text
Check Contact emails for a placeholder domainDoes any Contact email contain @example.com?Does not contain text, ignored blank values, and a prerequisite Check
Check that the Account Owner is on the Account TeamIs the owner one of the Account Team members?List mode: find one value in a list returned by a query
Check that the high-priority Case backlog is within a limitAre there three or fewer open high-priority Cases?At most, a count in the message, and an optional Platform Event

Paste SOQL into Source Query. A query that finds an Account’s Contacts looks like this:

SELECT Email FROM Contact WHERE AccountId = {!record.Id}

SELECT names the field to return, FROM names the object, and WHERE limits the rows to records related to the open record. {!record.Id} is a merge token that Record Health Check replaces with the open record’s ID. Never put quotation marks around a merge token in a query.

Set Source Query Field to the field the Check reads, such as Email. For an aggregate with an alias, such as SUM(Amount) total, enter the alias, total. Leave it blank for a bare COUNT().

How To Read Query ResultsUse it when
One row or aggregateThe query returns one value, such as COUNT(), SUM(), or one field from one row.
Any record passesAt least one returned record must meet the comparison.
Every record passesEvery returned record must meet the comparison.
Compare as listsThe Check looks for one value in a list, with List contains any or List contains none.

With Any record passes, Every record passes, or Compare as lists, you must also choose If Query Finds No Records: Pass, Fail, Skip, or Unable to evaluate. Choose the business meaning deliberately; Pass and Fail can reverse what the Check means. With One row or aggregate, the field is optional: it decides the result when a field query or an aliased aggregate, such as SUM(Amount) total, returns no row, and when it is blank the Check is skipped. A bare COUNT() always returns a number, so zero is compared normally. If Field Value Is Empty decides how a blank field compares: Ignore the record, Treat as blank, or Treat as not matching, the default.

  • Max Query Rows (1-2000) caps the rows each query can return, and defaults to 200. A query that returns more rows than the cap produces Unable to Check instead of a result from a partial list.
  • Queries run with the running user’s access. A record the user cannot see is not counted, so two users can see different results. If the user cannot read the object or a field, the Check shows Unable to Check.
  • Text comparisons with Contains text and Does not contain text are case-sensitive. Equal to, List contains any, and List contains none ignore capitalization.

The Query reference describes every setting, operator, and limit.

Know when to choose another Evaluation Type

Section titled “Know when to choose another Evaluation Type”

Use a Formula example when every value is on the record or a parent record. Use a Compare two queries example when both sides of the comparison come from queries. Use an Apex example when the rule needs custom logic.