Consolidate Price Rules by Using Lookup Queries

Learning Objectives

After completing this unit, you’ll be able to:

• Explain how similar price requirements can lead to rule proliferation.
• Describe the structure of a custom object that houses pricing variation data.
• Construct a price rule with lookup queries to identify relevant data in a custom object.
• Describe critical requirements of lookup-based price rules.

The Problem of Rule Proliferation

The first price rule you created in this module had a very straightforward objective. It checked if the account industry was “Education” and if the product was a Netbook. If so, it changed the list price to $400.

It’s a good rule, but consider what you’d have to do if you wanted to give healthcare industries a similar (but different) reduced price on the Netbook product. You’d create another rule with new conditions specific to the healthcare industry. That’s not too much work. But now imagine that you have a second product, NetbookPro, that needs adjusted pricing for both education and healthcare industries. Four rules are needed now.

In this use case, each new product or industry has a multiplicative effect on how many rules you’d need in order to handle all exceptions. As an admin, you probably don’t want to spend all your time making rules. Thankfully there’s a better solution for this kind of situation.

Instead of having 4, 8, or 16 rules all doing almost the same thing, you can create a single rule that handles all the variations. The trick is to catalog those variations in a custom object, then query the object to find out how to handle the one specific variation for the current quote.

Let’s imagine the education/netbook scenario, but with an additional layer of incorporating the customer’s Service Level Agreement, which can be Bronze, Silver, Gold, and Platinum. Here are all of the possible variations, each with their own price.

The first step in creating a single price rule is to get this data into Salesforce as a custom object. To simplify this exercise, Team Trailhead has already prepopulated this data in your CPQ-enabled org. Also, be aware that throughout this exercise you’ll see picklist values that refer to the Industry Price object. We added those to the picklists in advance so you don’t have to. But in real life, you’d have to do that part yourself. If you’re new to creating custom objects, check out the Data Modeling badge, it’s well worth your time. 

Here’s what the pricing data looks like if you choose the All list view on the Industry Prices tab.

If you look closely, you’ll notice that aside from the record name, each field label starts with “IP” as in “Industry Price.” In real life you don’t need to add that kind of prefix, we just included it so when you reference the fields later, you know exactly what it represents.

Now that you know what the pricing data should look like, and you know where to find it, you can start using it in a price rule.

Tap Into Custom Data

The price rule you create to handle all 16 variations will look a lot like the rules you’ve already made. Just like others, you start by creating the price rule record.

1. In the navigation bar, click Price Rules.
2. Click New.
3. For Price Rule Name, enter Lookup Netbook Price.
4. For Evaluation Scope, choose Calculator.
5. Check Active.
6. For Lookup Object, choose IndustryPrice__c.
   This is the API name of the object housing all of the pricing data from the above table. Choosing the lookup object here allows you to reference its fields when you construct the rest of the rule.
7. Click Save.

Great, now CPQ knows that IndustryPrice__c holds the data to drive this price rule. The next challenge is to make sure CPQ uses the right data at the right time. Each quote presents a unique set of circumstances. Are you selling to an education or a healthcare customer? Do they want a Netbook or NetbookPro? What’s the customer’s SLA?

You’ll use the answers to those questions to filter the data down to a single row, so you have exactly one price to use for the List Price. For example, if you’re selling to an “Education” customer, you can disregard the bottom half of the table. If they want a NetbookPro, you can disregard the first four of the remaining rows. Finally, if their SLA is Bronze, you know that row 5 holds the price you care about.

You tell CPQ how to filter the data by creating what are called Lookup Queries. Each query compares something unique about the quote, such as Account Industry, with one of the columns in the custom object. Let’s see what that looks like.

1. In the Lookup Queries (Price Rule) related list, click New.
2. For Match Type, choose Field Value.
   Most queries will use Field Value, which tells CPQ that you plan to compare a field like Account Industry to what's in the table. You use other Match Types if you compare a static value or configuration attribute to table values.
3. For Tested Object, choose Quote.
4. For Tested Field, choose AccountIndustry__c.
   Good, CPQ now knows what information it should try to find among the data in the custom object. Next, you need to tell CPQ which column in the custom object it should try matching with Account Industry.
5. For Operator, choose equals.
6. For Lookup Field, choose IPIndustry__c.
   This identifies a field from the custom object. It’s no coincidence that you’ve chosen a Lookup Field that’s very similar to the Tested Field.
7. Click Save.

Well done, you’re one-third of the way finished with lookup queries. If you were to use only this one lookup query, CPQ would find eight rows that match, and that’s very bad. CPQ won’t know which price is right. So let’s cut those eight rows down to four by filtering by product.

1. In the Lookup Queries (Price Rule) related list, click New.
2. For Match Type, choose Field Value.
3. For Tested Object, choose Quote Line.
4. For Tested Field, choose SBQQ__Product__c.
   Every quote line is related to a product record, which is perfect because each row in the custom object has a lookup to a product record too. We can make sure they both match.
5. For Operator, choose equals.
6. For Lookup Field, choose IPProduct__c.
7. Click Save.

You’re getting closer. With these two filters in place, CPQ will find four records that match, one for each SLA value. So let’s put a final query in place to reduce the four remaining records to one.

1. In the Lookup Queries (Price Rule) related list, click New.
2. For Match Type, choose Field Value.
3. For Tested Object, choose Quote.
4. For Tested Field, choose AccountSLA__c.
   This is a custom formula field that pulls the value from the related account record. CPQ can’t query fields on the account object directly, so a formula field will have to do.
5. For Operator, choose equals.
6. For Lookup Field, choose IPSLA__c.
7. Click Save.

Now that you’ve created queries for IPIndustry__c, IPProduct__c, and IPSLA__c, CPQ will only ever get one row back from the custom data. It is critical that your combination of lookup queries never return more than one row. If they do, an error message appears in the Quote Line Editor and calculation will fail, which is a showstopper!

It’s OK if a query returns no rows at all; CPQ simply won’t update the list price. That’s good news because it means you don’t need to represent every industry in the custom object.

That brings us to the last step in setting up this rule. You have to make a price action.

1. In the Price Actions related list, click New.
2. For Target Object, choose Quote Line.
3. For Target Field, choose SBQQ__ListPrice__c.
4. For Source Lookup Field, choose IPPrice__c.
   This is the field in the custom object that houses the actual price you want to set as the List Price.
5. Click Save.

You’ve made it, a fully functional lookup price rule is ready to go. All that work deserves a payoff. Let’s add a NetbookPro to a quote and see if CPQ gives us a price based on the custom data.

1. In the navigation bar, click Accounts, then choose the All Accounts related list.
2. Click Milwaukee Distributed Health.
   Note that Industry is Healthcare and SLA is Gold.
3. Click the Related tab.
4. In the Quotes related list, click Q-00052.
5. Click Edit Lines.
6. Click Add Products.
7. Check NetbookPro.
8. Click Select.
   The moment NetbookPro was added to the quote, CPQ ran the price rule. Your List Unit Price should be $535, which is exactly what’s on the Healthcare/NetbookPro/Gold row. Nicely done!
9. Click Cancel.

It takes a bit of work to make a lookup-based price rule, but it’s a lot easier than making 16 rules. And it’s easier to maintain. If you get more products or industries that need similar price adjustments, just add more records to the Industry Prices object.

Look out for rules that are mostly similar, you may just be able to consolidate them into a single lookup-based price rule. In the next unit you learn how to coordinate multiple price rules so they don’t conflict with each other or the standard quote calculator.

Resources

• Salesforce Help: Lookup Queries
• Salesforce Help: Lookup Queries for Large Datasets