We have 100s of products with a standard colour picklist in the pricebook (in edit quote line item window).
We only offer certain product families in certain colors.
How do i write a validation rule to only let hats be sold in blue and red, while pants can only be sold in black.
I have an idea for this. I'm not sure it's going to be the best solution, but give this a try.
1) Promote your Colors picklist to a global value set.
2) Create a Multi-select picklist on the Product level "Available in Colors" and use the global value set.
3) Select the colors that each product is available in.4) Use this validation rule on the QuoteLineItem object:
NOT(CONTAINS(
IF(INCLUDES( PricebookEntry.Product2.Available_in_Colours__c, "Color 1"), "Color 1:", null) &
IF(INCLUDES( PricebookEntry.Product2.Available_in_Colours__c, "Color 2"), "Color 2:", null) &
IF(INCLUDES( PricebookEntry.Product2.Available_in_Colours__c, "Color 3"), "Color 3:", null) &
IF(INCLUDES( PricebookEntry.Product2.Available_in_Colours__c, "Color 4"), "Color 4:", null) &
IF(INCLUDES( PricebookEntry.Product2.Available_in_Colours__c, "Color 5"), "Color 5:", null) &
IF(INCLUDES( PricebookEntry.Product2.Available_in_Colours__c, "Color 6"), "Color 6:", null) &
IF(INCLUDES( PricebookEntry.Product2.Available_in_Colours__c, "Color 7"), "Color 7:", null) &
IF(INCLUDES( PricebookEntry.Product2.Available_in_Colours__c, "Color 8"), "Color 8:", null) &
IF(INCLUDES( PricebookEntry.Product2.Available_in_Colours__c, "Color 9"), "Color 9:", null) &
IF(INCLUDES( PricebookEntry.Product2.Available_in_Colours__c, "Color 10"), "Color 10:", null) &
IF(INCLUDES( PricebookEntry.Product2.Available_in_Colours__c, "Color 11"), "Color 11:", null) &
IF(INCLUDES( PricebookEntry.Product2.Available_in_Colours__c, "Color 12"), "Color 12:", null) &, TEXT(Colour__c)))
This will generate a string of available colors for that product (e.g. "Color1:Color 6:Color 8"). If the QLI product color selected (example, Color 5) does not match any of the ones in that string it will throw an error.
It will take some time to set up, but the only maintenance to the validation rule you would need to do is if your company introduces more colors to the global value set.
