Skip to content
Suggest a correction

Check that open pipeline includes a previously purchased Product

Example4 min read

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.

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.

  • 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.

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 Pipeline Includes Purchased Product
Check SetAccount Record Alignment
Evaluation TypeCompare two queries
Record Health Check NameOpen_Pipeline_Includes_Purchased_Product
Evaluation Order20
ActiveCleared until validation succeeds
Source QuerySELECT Product2Id FROM OpportunityLineItem WHERE Opportunity.AccountId = {!record.Id} AND Opportunity.IsClosed = false
Source Query FieldProduct2Id
How To Read Query ResultsCompare as lists
Comparison OperatorLists overlap
Comparison QuerySELECT Product2Id FROM OpportunityLineItem WHERE Opportunity.AccountId = {!record.Id} AND Opportunity.IsWon = true
Comparison Query FieldProduct2Id
If Query Finds No RecordsSkip
If Field Value Is EmptyIgnore the record
Check TitleOpen pipeline includes a previously purchased Product
Check DescriptionPasses when at least one Product on an open Opportunity is also on a won Opportunity.
Message When FailedNone of the Products on open Opportunities for {!record.Name fallback="this Account"} has been purchased before.
Fix MessageReview the Products on open Opportunities, and correct them only if they do not match the intended sale.
Failure SeverityInfo
Action LabelReview Opportunities
Action URL/lightning/r/Account/{!record.Id}/related/Opportunities/view
Applies ToAll 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.
  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.

In this table, A, B, and C are three different Products. Select Run on the card for each case.

Products on open OpportunitiesProducts on won OpportunitiesResult on the card
NoneAnySkipped
AnyNoneSkipped
ABInfo
A, BB, CPass

Both lists include only records the running user can 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.

What you seeWhat to check
Every Account shows SkippedThe Account has both open and won Opportunities with Products, visible to the user.
A matching Product is not foundBoth queries select Product2Id, and both field settings are Product2Id.
Unable to CheckThe user can read Opportunity Products.