Price Rules & Lookup Queries
Salesforce CPQ offers multiple ways to set the Price on a quote line. We have the four pricing methods, discount schedules, ability for sales reps to add Additional Discounts and so on. However, real-life business scenarios often ask for more complicated logic. For example, we may want to automatically add a 10% discount if the Quote is tied to a ‘Gold tier’ partner. Now, we can always build workflows or process builders to make this happen but using these can potentially cause unanticipated updates through CPQ’s quote calculation sequence. To meet this requirement, Salesforce CPQ offers Price Rules.
Price Rules can insert a static value, field value, or summary variable into a quote or quote line field, based on specific conditions. Price Rule has associated Price Conditions and Price Actions to define when it should fire and what it should do, respectively. Price Rules can also set price based on a custom table setup in Salesforce using Lookup Queries.
We know that Price Rules are a very powerful tool in the Salesforce CPQ arsenal; and when we use the Lookup Queries functionality the power is turbo charged!
In essence, Price Rules using Lookup Queries allow you to map the data points on your Quote Line to data fed in a custom object, find a matching record with same values, and retrieve an associated field value from the matched record. For those who have used VLOOKUP function in MSExcel, this will throw a déjà vu. It is very similar to matching your cell values, finding the equivalent in a table and fetching an associated value on the same row but different column. Well, here it is more powerful as you can match the data on multiple fields to get the exact combination you are looking for.
So, where can we use this neat feature? Well, check out our Use Case blog here:
Here the steps you need to follow to create a Price Rule with Lookup Queries:
- Create custom object with fields representing the attributes and associated price
- Pro Tip: You may want to add a custom tab so that you can view the data easily in a list view
- Load the data for the Attributes and associated Price. Ensure there is only one price for each combination of attributes you will be filtering on
- Pro Tip: To prevent duplicate combinations you can create a text field (hide it from page layout) which is automatically updated with a concatenated string of all the Attributes using a workflow/process builder. Mark this field to have only Unique values. This setup will throw an error if you enter a duplicate combination
- Update the picklist values on the following fields:
- Set up the Price Rule with the ‘Lookup Object’ field referencing the api name of the custom object created
- Add the Price Condition per business requirement
- Add Lookup Queries to match the Quote Line fields to the custom object fields
If you want to understand Price Rules in general and with Lookup Queries, below is your go-to video. We have talked through how to implement the Use Case example, in detail, here: