Check that open pipeline includes a previously purchased Product
Create a Record Health Check on an Account that compares the Products on its open Opportunities with the Products on its won Opportunities, and passes when the two lists share at least one Product. The example uses the Compare two queries Evaluation Type with the Lists overlap operator.
Why two queries fit
Section titled “Why two queries fit”Each list comes from a separate set of related records: open Opportunity Products and won Opportunity Products. Compare two queries returns both lists and tests whether they have a value in common, without code.
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 won Opportunity Products are the right record of past purchases. If your org records purchases on Assets or Orders, change Comparison Query to read that object.
- Confirm that the people who will use the card can read Opportunities, Opportunity Products, and Products.
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 Pipeline Includes Purchased Product |
| Check Set | Account Record Alignment |
| Evaluation Type | Compare two queries |
| Record Health Check Name | Open_ |
| Evaluation Order | 20 |
| Active | Cleared until validation succeeds |
| Source Query | SELECT Product2Id FROM OpportunityLineItem WHERE Opportunity. |
| Source Query Field | Product2Id |
| How To Read Query Results | Compare as lists |
| Comparison Operator | Lists overlap |
| Comparison Query | SELECT Product2Id FROM OpportunityLineItem WHERE Opportunity. |
| Comparison Query Field | Product2Id |
| If Query Finds No Records | Skip |
| If Field Value Is Empty | Ignore the record |
| Check Title | Open pipeline includes a previously purchased Product |
| Check Description | Passes when at least one Product on an open Opportunity is also on a won Opportunity. |
| Message When Failed | None of the Products on open Opportunities for {!record. |
| Fix Message | Review the Products on open Opportunities, and correct them only if they do not match the intended sale. |
| Failure Severity | Info |
| Action Label | Review Opportunities |
| Action URL | /lightning/r/Account/{!record. |
| Applies To | All records |
How the values work:
- Source Query returns the Product of each line on an open Opportunity. Comparison Query returns the Product of each line on a won Opportunity.
- Lists overlap passes when any Product appears in both lists.
- If Query Finds No Records set to Skip shows Skipped when either list is empty, because there is nothing to compare when the Account has no open pipeline or no purchase history.
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”In this table, A, B, and C are three different Products. Select Run on the card for each case.
| Products on open Opportunities | Products on won Opportunities | Result on the card |
|---|---|---|
| None | Any | Skipped |
| Any | None | Skipped |
| A | B | Info |
| A, B | B, C | Pass |
Both lists include only records the running user can see.
What users see
Section titled “What users see”A failed row shows Info, because Failure Severity is Info, with the message, the fix message, and a Review Opportunities link. Found lists the open-pipeline Product IDs, and Expected reads “to overlap with” followed by the purchased Product IDs, each showing up to ten values. Because IDs are hard to read, consider setting the Check’s Show Found and Expected to Hide.
Resolve problems
Section titled “Resolve problems”| What you see | What to check |
|---|---|
| Every Account shows Skipped | The Account has both open and won Opportunities with Products, visible to the user. |
| A matching Product is not found | Both queries select Product2Id, and both field settings are Product2Id. |
| Unable to Check | The user can read Opportunity Products. |