Lookup Object / Table

by Community Manager ‎03-22-2016 03:23 PM - edited ‎10-19-2016 12:46 PM


Lookup tables are user-defined custom objects that can be used to store data (as records and fields) within a lookup query. The object is the table itself, with each lookup query record defining a row and each lookup query field defining a column.



Lookup tables provide a powerful tool in your pricing protocol. One notable use case is variable insurance rates. You can use a lookup to look at age, crash history, and other variables to correctly assign monthly premiums to a prospective customer.


You can also use lookup tables to track sales accountability, i.e. when a sales rep gives a discount to a specific quote line. The lookup table can consider the discount, customer, product, and other variables during quote calculation and assign a score to the specified discount. Let the price action populate a field used in the line editor to give the salesperson immediate feedback.

NOTE: SteelBrick CPQ only supports equality/inequality logic like less than, greater than, equals, and not equals. We do not currently support queries of the type starts with, ends with,  or contains.



Create Lookup Object

  1. Navigate to Setup | Create | Objects | [New Custom Object]
  2. Enter your desired labels.
  3. Navigate to Enter Record Name Label and Format  and select your desired Data Type. For example, use Text if your object will display names, or Auto Number if your object will display financial records. Choose whichever labeling system fits your needs.
  4. Click Save.
  5. If needed, add any number of custom fields needed for lookup queries or custom actions.

NOTELookup data can be generated like any other object data. We recommend using Data Loader to pull table data as it can easily process large-scale data collections.



This example is for a Sales Rep who would like to apply a Promotional Discount if a Product is quoted during a certain period of time. If the Quote Promo Code is entered and Products are added to a Quote during the Promotional period, a Discount should be applied to the Calculated List Price, thus allowing Sales Reps to apply an additional discretionary discount on top of the promotional discount.


The following example is intended for the Winter ‘16 release as we will use a lookup table to retrieve the discount percent and to evaluate the time period in which the promotional discount should be available.


  1. Create an Object or utilize the SteelBrick Lookup Data Object (Setup | Create | Objects | Lookup Data) to hold the Promotional data that will be referenced in the Price Rule.
  2. Create Custom Fields on the Lookup Object for the Promotion Values: I.E: Promotion Code, Promotional Discount, Start Date, End Date.
  3. Create a record on the respective object containing a Promotion Code, Promotional Discount %, and Promotion Start Date & End Date.original.png
  4. Create a Price Rule with Evaluation Scope set to Calculator and Conditions Met set to All and the Lookup Object set to whatever Object was used in the above steps.
  5. Create a Price Condition for the rule to fire if promo code is not equal to “null.”
  6. Create Lookup Queries which will match Quote or Quote Line fields to an available Promotion:
    • Quote Promo Code equals Lookup Field Promo Code
    • Quote Expiration Date greater than Lookup Field Promo Code Start Date
    • Quote Expiration Date less than Promo Code End Date
  7. Create Price Actions:
    • Source Lookup Field Discount into Target Field Custom Discount Percentage Field. This field holds the value of the Promotional Discount on the Quote Line Object so it can be used in the next Price Action's Source Formula (or Source Field Formula if using the Legacy Calculator.)
    • Source Formula calculating Discount * Original Price and injecting into SBQQ__ListPrice__c target field

Screen Shot 2016-01-14 at 12.37.55 PM.png



Some users may have to decide to between using one Price Rule that sends multiple lookup queries to a large table or multiple Price Rules that each send one query to a smaller table. For example, a user has a table of 75,000 rows and could set one Price Rule to query the table, or break the table into 25 tables of 3,000 rows.


The optimal workflow here depends on the selectivity of the user's Lookup Queries, since multiple rules with a single Lookup Query each are likely to be more selective than a single rule with multiple Lookup Queries. However, if the Lookup Queries all use the "equals" operator, or test Quote field values or static values, the number of records needed to return for the table can be minimized - making it easier to use a single Price Rule.


Since administration for one large table is generally easier than many smaller tables, users should first try to create a workflow that allows for one large table with a very selective Price Rule that can quickly query a table of that size. Consider the above user's case:


Option 1: Query one large table

Rule Condition: Picklist not blank

Lookup Query: Match 2 quote line text values to 2 lookup table record values to find one match out of 75,000 records.

Actions: Populate a percent field and a number field on the quote line with the table values.


Option 2: Query 25 smaller tables.

Rule condition: Picklist = [certain value]. 25 Price Rules are needed to query the 25 tables.

Lookup Query: Same as option 1's lookup query, except it would need 1 match out of 3,000 possible records.

Actions: Same as Option 1.


Lookup Queries testing Quote fields or Static Values are much more selective than Queries testing Quote Line fields, so consider replacing Quote Line Queries where possible. In this case, the user should put the value from the Account in a Quote field instead of a Quote Line field. This would enable a very selective query that could be used against one large table.