Define Price Floor and Price Ceiling for Products in CPQ

1. Create a Custom Object to Store Price Floor and Price Ceiling information. New records must be created for this object to define each product’s price floor or price ceiling.

• Setup  >  Object Manager  
  • Create Custom Object
    • Label: Price Floor & Ceiling
    • Object Name: Price_Floor_Ceiling
    • Description: Price Rule populates quote line Price Floor and Price Ceiling from these records
    • Data Type: Text  >  Save
  • Create Fields > Fields & Relationships  >  New 
    • Click New to create a Product Lookup Field
      • Data Type: Lookup Relationship  >  Next
      • Related To: Product  >  Next
      • Field Label: Product
      • Field Name: Product  >  Next  >  Next  >  Save
    • Click New to Create a Price Book Lookup Field
      •  Data Type: Lookup Relationship  >  Next
      •  Related To: Price Book  >  Next
      •  Field Label: Price Book
      • Field Name: Price_Book  >  Next  >  Next  >  Save
    • Click New to Create a Price Floor Currency Field
      • Data Type: Currency  >  Next
      • Field Label: Price Floor  >  Next
      • Field Name: Price_Floor  >  Next  >  Next  >  Save
    • Click New to Create a Price Ceiling Currency Field
      • Data Type: Currency  >  Next
      • Field Label: Price Ceiling  >  Next
      • Field Name: Price_Ceiling

2. Create Quote Line fields for Price Floor and Price Ceiling.

• Setup > Object Manager > Quote Line object  >  Create New Fields
  • Fields & Relationships > New
    • Create a Price Floor Currency Field
      • Data Type: Currency
      • Field Label: Price Floor
      • Name: Price_Floor
    • Click New to Create a Price Ceiling Currency Field
      • Data Type: Currency
      • Field Label: Price Ceiling
      • Field Name: Price_Ceiling

3. Create a Lookup Query Price Rule to Populate the Quote Line's Price Floor and Price Ceiling.

• Setup > Object Manager > Open Price Rule Object  > Fields & Relationships
  • Click Lookup Object, navigate to the Values related list and click New to add a Picklist Value
    • Add Picklist value: Price_Floor_Ceiling__c
• Setup > Object Manager > Open Lookup Query Object  > Fields & Relationships
  • Click Tested Field, navigate to the Values related list and click New to add a Picklist Value
    • Add Picklist values:  SBQQ__Product__c and SBQQ__PriceBook__c
  • Click Lookup Field, navigate to the Values related list and click New to add a Picklist Value
    • ​ Add Picklist values: Product__c and Price_Book__c
• Setup > Object Manager > Open Price Action Object  > Fields & Relationships
  • Click Target Field, navigate to the Values related list and click New to add a Picklist Value
    • Add Picklist Values for Price_Floor__c and Price_Ceiling__c
• Setup > Object Manager > Open Price Action Object  > Fields & Relationships
  • Click Source Lookup Field, navigate to the Values related list and click New to add a Picklist Value
    • Add Picklist Values: Price_Floor__c and Price_Ceiling__c
• Navigate to the Price Rules and click New
  • Price Rule Name: Populate QL Price Floor & Ceiling
    • Evaluation Scope: Calculator
    • Conditions Met: All
    • Active: True
    • Lookup Object: Price_Floor_Ceiling__c  >  Save
  • Navigate to the Price Rule's Lookup Queries Related List and click New
    • Create a Lookup Query to match the Quote's Price book with the Price Floor & Ceiling Record's Price Book
      • Match Type: Field Value
      • Tested Object: Quote
      • Tested Field: SBQQ__PriceBook__c
      • Operator: Equals
      •  Lookup Field: Price_Book__c >  Save
    • Create another Lookup Query that matches the Quote Line's Product with the Price Floor & Ceiling Record's Product
      •  Match Type: Field Value
      •  Tested Object: Quote Line
      •  Tested Field: SBQQ__Product__c
      •  Operator: Equals
      •  Lookup Field: Product__c  > Save
  • Navigate to the Price Rule's Price Actions Related List
    • Create a Price Action to populate Price Floor
      • Target Object: Quote Line
      • Target Field: Price_Floor__c
      • Source Lookup Field: Price_Floor__c  >  Save
    • Create another Price Action to populate Price Ceiling
      • Target Object: Quote Line
      • Target Field: Price_Ceiling__c
      • Source Lookup Field: Price_Ceiling__c

4. Create a Custom Quote Line Formula Field and Summary Variable to Count the number of Quote Lines where Net Unit Price is less than Price Floor or Net Unit Price is greater than Price Ceiling

• Setup > Object Manager > Open Quote Line object  >  Fields & Relationships  >  New
  • Create a Quote Line formula field to identify if Net Unit Price is less than the Price Floor or greater than the Price Ceiling
    • Data Type: Formula
    • Field Label: Price Floor or Ceiling Met
    • Field Name: Price_Floor_or_Ceiling_Met__c
    • Formula Return Type: Number
      • Formula: IF (SBQQ__CustomerPrice__c < Price_Floor__c * SBQQ__ProrateMultiplier__c || SBQQ__CustomerPrice__c > Price_Ceiling__c * SBQQ__ProrateMultiplier__c, 1, 0
      • Logic: Returns 1 if price is above Price Ceiling or below Price Floor
      • Logic: Returns 0 if price is within the Price Ceiling and Price Floor
    • Blank Field Handling: Treat blank fields as blanks
• Navigate to the Summary Variable object >  Fields & Relationships
  • Click Aggregate Field, navigate to the Values related list and click New to add a Picklist Value
    • Add Picklist Values: Price_Floor_or_Ceiling_Met__c
• Navigate to the Summary Variable tab
  • Create a Summary Variable to sum the Quote Lines' Price Floor or Ceiling Met field value 
    • Variable Name: Sum Quote Line Price Floor or Ceiling Met
    • Target Object: Quote Line
    • Aggregate Function: Sum
    • Aggregate Field: Price_Floor_or_Ceiling_Met__c

5. Create a Validation Rule (Product Rule) to Prevent Users from Saving a Quote

• Create a Product Rule: 
  • Product Rule Name: Price Floor & Ceiling Validation
  • Conditions Met: All
  • Scope: Quote
  • Evaluation Event: Always
  • Type: Validation
  • Active: True
  • Message: "Insert Validation message your users will see when saving a Quote" 
• Create an Error Condition, navigate to the Error Conditions related list and click New:
  • Tested Object: Quote Line
  • Tested Variable: Sum Quote Line Price Floor or Ceiling Met
  • Operator: greater than
  • Filter Type: Value
  • Filter Value: 0

6. Create a tab for the Price Floor & Ceiling custom object to create/View  Price Floor & Ceiling records 

• Setup > Tabs
  • Click New and choose Price Floor & Ceiling 
  • Choose an icon > Next
  • Choose the Profiles for which the object should be visible > Next > Save

Note: You must create Price Floor and Price Ceiling object records for each product. Records can be created manually create or via data upload to house the Price Floor and Price Ceiling information.

Test and Verify

1. Create a Quote
2. Click Add Products and select a Product which has a Price Floor & Ceiling record created for it
3. Click Save to add the product to the Quote Line Editor
4. Click Quick Save and observe that no Validation Message is seen
5. Populate the Quote Line's Additional Discount so that the Net Unit Price is less than the Price Floor
6. Click Quick Save and observe that the Validation Message is shown and prevents the user from saving the Quote
7. Populate the Quote Line's Additional Discount with a negative Additional Discount so that the Net Unit Price is greater than the Price Ceiling
8. Click Quick Save and observe that the Validation Message is shown and prevents the user from saving the Quote

Considerations:

1. If Multi-Currency is enabled, additional Price Floor and Price Ceiling records will need to be created for each active currency. An additional Lookup query also needs to be created in Step 3 in order to match the Quote Line's currency to the currency for the appropriate Price Floor & Ceiling record. 
2. If Large Quote Experience is enabled, admins should use a Quote Line Validation Rule instead of a Product Rule Validation.