Lookup Object / Table

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

OVERVIEW

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.

 

DETAILS

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.

 

PROCEDURES

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.

 

USE CASE: USING A PROMO CODE TO APPLY A PROMOTIONAL DISCOUNT

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

 

USE CASE - LOOKUP QUERIES - ONE LARGE TABLE VS MULTIPLE TABLES

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.

Comments
by Pratima
on ‎02-21-2017 12:35 PM

Can a lookup field such as 'Account' used to trigger the price rule. My look up table has different discounts based on certain accounts (no, we cannot use contracted prices)

 

by Hunter_Sabol
on ‎02-24-2017 08:20 AM

Hi,

 

I was reading several other posts regarding set price discounting and they recommended using a lookup table & price rules. I am not sure I understand what fields/values would go on the lookup table object. My scenario pricing is shown below. I will have different versions of the same price structure for different products. There are no ranges or % discounts that apply to all products.

 

Product one

Quantity = set discounted amount

1 item = $10

2 items = $8.50

3 items = $7 and so forth 

 

Product two

 1 item = $29

2 items = $18

3 items = $15 and so forth 

 

 

My thought is to have all the discount set pricing on the table and then populate a lookup field on the quote line.

 

Can someone please clarify the use of lookup tables in my scenario?

 

Thanks

Hunter

by Scooby6995
2 weeks ago

I've got a few questions about the article:

1: In Step #3, it has a screen shot of an object called 'Promotion', but in prior steps it recommends using the Lookup Data object.Which is it? If I use the Lookup Data object, I'm having a heck of a time making the layout visible and being able to see the inserted data. The only way I've been able to insert and "view" the data is using the Data Loader,- not very efficient.

2: In Step#6 of the example, are these simply criteria to refine the record selection for the lookup?

3: In Step#7, there are two steps: Storing the variable, then performing a calculation inserting the calculated value in the list price field. Am I interpreting this correctly? Do I need to add a custom field on the Price Action object to hold these "temporary" values used for formulas?

by Hunter_Sabol
2 weeks ago

@Scooby6995

 

The way I got lookup table to work is to 

  1. Create a custom object like you would normally in SFDC.
      1. On that custom object create the fields to store your data. For example Min and max values, price, product name or code. Create a record for each price. 
  1. Create a price rule 
    1. Create the Price Condition like a workflow criteria.
    2. In the lookup query you will need to add the API names of the fields you created on that custom object to the “lookup field” on the query object. Then you will create your queries like below.Queries.JPG
    3. In the action: add the object API to "rule lookup object" on the Action object.

Hope this helps.

by Scooby6995
2 weeks ago

Thank you @Hunter_Sabol I'm sorry I'm so dense on this topic. Just so I understand: In the lookup query, you are adding more criteria to identify the exact row you are pulling from your custom object. Then, in your Price Action, you are actually storing specific values, then either returning them in specific fields or using them to perform calculations against. Does that sound right?


by Hunter_Sabol
2 weeks ago

@Scooby6995

 

Don't worry this stuff can be confusing. 

 

- Regarding the query you are correct

- The price action is injecting that information into a designated field. For example if you have the min and max values with a certain price on your object. The price action is populating that price in the List Price field. 

 

by Scooby6995
2 weeks ago

@Hunter_Sabol If I need to store values before calculations are performed to return a value,- should I always put those values on custom fields created against the Quote Line Item object? Thank you for your assistance and patience!

by Hunter_Sabol
2 weeks ago

@Scooby6995 Yes I think that is correct. 

Contributors