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.
Create Lookup Object
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.
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.