Skip to content
Suggest a correction

Check that every open Opportunity has a Contact Role

Example5 min read

Create a Record Health Check on an Account that counts the open Opportunities with at least one Contact Role and compares that number with the count of all open Opportunities. Equal counts mean every open Opportunity has a Contact Role. The example uses the Compare two queries Evaluation Type with two aggregate queries.

Both numbers come from related records: one from Opportunity Contact Roles and one from Opportunities. Compare two queries runs both queries and compares their results, so no code is needed. A formula cannot count related records, and one query cannot produce both numbers.

  • Install Record Health Check, and assign yourself Record Health Check Admin and the Salesforce Customize Application permission.
  • Confirm that your process requires a Contact Role on every open Opportunity.
  • Confirm that the people who will use the card can read Opportunities and Opportunity Contact Roles.

You can add the Check to an Account Check Set you already have. To create one, open Setup → Custom Metadata Types, select Manage Records next to Record Health Check Set, select New, enter these values, and save. Leave other fields at their defaults.

FieldValue
LabelAccount Record Alignment
ObjectAccount
Record Health Check Set NameAccount_Record_Alignment
ActiveCleared until validation succeeds
Card TitleAccount Record Alignment
Card SubtitleConfirm that related records agree before forecast review.
When Checks RunWhen the user clicks Run
Found/Expected DisplayShow on demand

Open Setup → Custom Metadata Types, select Manage Records next to Record Health Check, select New, enter these values, and save.

FieldValue
LabelOpen Opportunities Have Contact Roles
Check SetAccount Record Alignment
Evaluation TypeCompare two queries
Record Health Check NameOpen_Opportunities_Have_Contact_Roles
Evaluation Order10
ActiveCleared until validation succeeds
Source QuerySELECT COUNT_DISTINCT(OpportunityId) coveredCount FROM OpportunityContactRole WHERE Opportunity.AccountId = {!record.Id} AND Opportunity.IsClosed = false
Source Query FieldcoveredCount
How To Read Query ResultsOne row or aggregate
Comparison OperatorEqual to
Comparison QuerySELECT COUNT() FROM Opportunity WHERE AccountId = {!record.Id} AND IsClosed = false
Comparison Query FieldLeave blank
If Query Finds No RecordsFail
Check TitleEvery open Opportunity has a Contact Role
Check DescriptionPasses when the number of open Opportunities with a Contact Role equals the number of open Opportunities.
Message When Failed{!record.Name fallback="This Account"} has open Opportunities with no Contact Role.
Fix MessageAdd a Contact Role to each open Opportunity that has none, then run the Check again.
Failure SeverityWarning
Action LabelReview Opportunities
Action URL/lightning/r/Account/{!record.Id}/related/Opportunities/view
Applies ToWhen a count query matches
Applies When (Count Query)SELECT COUNT() FROM Opportunity WHERE AccountId = {!record.Id} AND IsClosed = false
Count Must BeGreater than
Count Value0
Message When Not Applicable{!record.Name fallback="This Account"} has no open Opportunities.

How the values work:

  • Source Query counts the distinct open Opportunities that have at least one Contact Role. COUNT_DISTINCT() counts an Opportunity once, even when it has several Contact Roles. The alias coveredCount names the value, so Source Query Field is coveredCount.
  • Comparison Query counts all open Opportunities. A bare COUNT() needs no field setting.
  • Equal to passes when the two counts are the same. The Source result is Found, and the Comparison result is Expected.
  • When no open Opportunity has a Contact Role, COUNT_DISTINCT() returns no rows, not 0. If Query Finds No Records set to Fail reports that case as a failure. Only a bare COUNT() returns 0 when nothing matches.
  • Applies To skips Accounts with no open Opportunities, so the “no rows” case above can only mean that Contact Roles are missing.
  1. Run the Validate Record Health Check Configuration Flow action, as described in Validate configuration before activation, and correct every error.
  2. Activate the Check, then the Check Set.
  3. Add the Record Health Check component to the Account record page, select Account Record Alignment (Account_Record_Alignment), and activate the page.
  4. Assign Record Health Check Card User to the users who test the card.

Select Run on the card for each case.

Open OpportunitiesOpen Opportunities with a Contact RoleResult on the cardWhy
00SkippedApplies To does not match.
10WarningNo rows on the Source side, which fails.
11Pass1 equals 1.
32Warning2 does not equal 3.
33Pass3 equals 3.

Both counts include only records the running user can see.

A failed row shows Warning, the message, the fix message, and a Review Opportunities link. When both queries returned a count, Found shows the covered count and Expected reads “to equal” followed by the total, such as Found 2 and Expected to equal 3.

What you seeWhat to check
An Opportunity with two Contact Roles counts twiceSource Query uses COUNT_DISTINCT(OpportunityId).
A validation error on Source Query FieldIt holds the alias, coveredCount, not the expression.
Skipped on an Account with open OpportunitiesApplies When (Count Query) matches the table, and Count Must Be is Greater than 0.
Unable to CheckThe user can read Opportunities and Opportunity Contact Roles.