Check that every open Opportunity has a Contact Role
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.
Why two queries fit
Section titled “Why two queries fit”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.
Before you start
Section titled “Before you start”- 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.
Step 1: Create or choose the Check Set
Section titled “Step 1: Create or choose the Check Set”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.
| Field | Value |
|---|---|
| Label | Account Record Alignment |
| Object | Account |
| Record Health Check Set Name | Account_ |
| Active | Cleared until validation succeeds |
| Card Title | Account Record Alignment |
| Card Subtitle | Confirm that related records agree before forecast review. |
| When Checks Run | When the user clicks Run |
| Found/Expected Display | Show on demand |
Step 2: Create the Check
Section titled “Step 2: Create the Check”Open Setup → Custom Metadata Types, select Manage Records next to Record Health Check, select New, enter these values, and save.
| Field | Value |
|---|---|
| Label | Open Opportunities Have Contact Roles |
| Check Set | Account Record Alignment |
| Evaluation Type | Compare two queries |
| Record Health Check Name | Open_ |
| Evaluation Order | 10 |
| Active | Cleared until validation succeeds |
| Source Query | SELECT COUNT_ |
| Source Query Field | coveredCount |
| How To Read Query Results | One row or aggregate |
| Comparison Operator | Equal to |
| Comparison Query | SELECT COUNT() FROM Opportunity WHERE AccountId = {!record. |
| Comparison Query Field | Leave blank |
| If Query Finds No Records | Fail |
| Check Title | Every open Opportunity has a Contact Role |
| Check Description | Passes when the number of open Opportunities with a Contact Role equals the number of open Opportunities. |
| Message When Failed | {!record. |
| Fix Message | Add a Contact Role to each open Opportunity that has none, then run the Check again. |
| Failure Severity | Warning |
| Action Label | Review Opportunities |
| Action URL | /lightning/r/Account/{!record. |
| Applies To | When a count query matches |
| Applies When (Count Query) | SELECT COUNT() FROM Opportunity WHERE AccountId = {!record. |
| Count Must Be | Greater than |
| Count Value | 0 |
| Message When Not Applicable | {!record. |
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 aliascoveredCountnames the value, so Source Query Field iscoveredCount. - 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 bareCOUNT()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.
Step 3: Validate and activate
Section titled “Step 3: Validate and activate”- Run the Validate Record Health Check Configuration Flow action, as described in Validate configuration before activation, and correct every error.
- Activate the Check, then the Check Set.
- Add the Record Health Check component to the Account record page, select Account Record Alignment (Account_Record_Alignment), and activate the page.
- Assign Record Health Check Card User to the users who test the card.
Step 4: Test the Check
Section titled “Step 4: Test the Check”Select Run on the card for each case.
| Open Opportunities | Open Opportunities with a Contact Role | Result on the card | Why |
|---|---|---|---|
| 0 | 0 | Skipped | Applies To does not match. |
| 1 | 0 | Warning | No rows on the Source side, which fails. |
| 1 | 1 | Pass | 1 equals 1. |
| 3 | 2 | Warning | 2 does not equal 3. |
| 3 | 3 | Pass | 3 equals 3. |
Both counts include only records the running user can see.
What users see
Section titled “What users 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.
Resolve problems
Section titled “Resolve problems”| What you see | What to check |
|---|---|
| An Opportunity with two Contact Roles counts twice | Source Query uses COUNT_. |
| A validation error on Source Query Field | It holds the alias, coveredCount, not the expression. |
| Skipped on an Account with open Opportunities | Applies When (Count Query) matches the table, and Count Must Be is Greater than 0. |
| Unable to Check | The user can read Opportunities and Opportunity Contact Roles. |