You are here:
Lookup Queries for Large Datasets
When you query a large dataset, you have several options for how you set up your rules and lookup queries. (Salesforce CPQ Managed Package)
Required Editions
| Price rule lookup queries are available in Salesforce CPQ Winter ’16 and later |
| Product rule lookup queries are available in Salesforce CPQ Summer ’17 and later |
For example, let’s say you have a dataset containing 75,000 pricing records that you imported from an external system. You want a price rule setup that can query those 75,000 records to find a matching value and update a value in your quote line editor.
You can use one price rule that sends multiple lookup queries to a large table. Or you can break your dataset into several smaller tables and use several price rules that each send one query to one of the smaller tables. The ideal workflow depends on whether your lookup query has a narrow scope. Multiple rules, each with one specific lookup query, allow you to access specific data more quickly than one rule with multiple lookup queries. However, if your lookup queries contain any of the following setups, you can minimize the number of records you need the dataset to return.
- An error condition that uses the equals operator
- A tested field on the Quote object
- A static tested value, like a text or number field
If you’re using any of these three setups, you can use one set of lookup object records and one rule, which makes administration easier. Here’s one way you could use a price rule to query a dataset of 75,000 records on a single lookup object.
- Condition
- Picklist value isn’t blank.
- Lookup Query
- To find one match out of 75,000 records, match two quote line text values to two dataset record values.
- Actions
- Update a percentage field and a number field on the quote line with the value you matched.
To improve query performance:
- To avoid using a lookup query, use a rollup field on your quote or quote line.
- When running multiple lookup queries on one object type in the same action use fewer lookup queries to reduce the number of records.
- Combine multiple product rules with similar lookup queries on the same object into one product rule.
- Limit when a product rule lookup query applies to the Load or Save actions when possible.
- Avoid using “not equal to” operator in lookup queries on object types with large datasets. Queries perform better when you search for what you’re looking for rather than what you’re not looking for.
