Preventing Duplicate Opportunity Products

If you have been struggling to find a way to prevent duplicate products, then this post is for you! When this request came to me from a manager in my organization, I really struggled with how to make this work. But, thanks to some smart people who helped me out, we managed to come up with a declarative way of making this happen!

Before we get started, there are a few items to mention:

1. This is not the only way to prevent duplicate products but it was easy and worked in this scenario.
2. You cannot customize the error message that displays. Thankfully, in most instances, what is displayed is worded well enough that it will probably make sense to most users.
3. Your requirements may be different than what is outlined below. If this is the case, you will need to modify the formulas to meet your specific business requirements.
4. If you have suggestions on how to make this functionality better, or you have another method for preventing duplicate products, please post them as a comment and share your knowledge! This is the power of the community at work!

The Requirements
Users should not be able to enter multiple lines of the same product at the same Sales Price. For example, if I add a product titled Kits to the opportunity for $5,000, I should not be able to enter another separate line item for Kits at the same Sales Price.

Ingredients
Like any good recipe, we will list the ingredients first, then show you how to mix them together!

• 1 unique text field 255 characters in length
• 1 workflow rule with a field update
• 1 evaluation formula
• 1 new value formula

This is a really easy recipe, so let’s get cookin’!

Create the text field
Open the Opportunity Product object to create your new text field. I have called this field Unique Product ID but you can name it whatever makes you happy! Because we are Admin Heroes, we will add a field description. I have entered the following: Populated via workflow rule to create a unique product entry and prevent duplicate products on an opportunity.

In the General Options section, make sure that the checkbox for “Do not allow duplicate values” is checked and ensure that the first option (case insensitive) is checked. In the field length field, enter 255. You probably won’t use all of these characters, but we’ll play it safe! You don’t need to provide visibility to all users as it will be a jumbled looking text string. Instead, make it visible to the System Administrator profile, and add it to the page layout to validate. You can then remove it once it is working.

Create the workflow rule
The workflow rule will be used to populate the text field and provide us with a unique ID which will then be cross checked with the other product Unique IDs on the opportunity.

Your workflow rule will be built on the Opportunity Product object. I have called mine Unique Product Update. In the description field, enter the following formula:

OR(ISNULL(Id), ISCHANGED(OpportunityId), ISCHANGED(PricebookEntryId), ISCHANGED(UnitPrice))

NOT(ISNULL(OpportunityId))

I am not sure why this works in the description field and not the formula field, but I am not a formula ninja so perhaps someone can help out with that! In the actual formula field, enter the word true. Be sure to check the syntax and make sure you don’t get an error message.

The rule evaluation option should be created, and every time it is edited. Your rule criteria should look like this:

Now that the evaluation criteria is set and the rule is created, it is time to build our field update. In the Immediate Workflow Actions section, we need to create a field update. The object we are updating is the Opportunity Product, and the field we are updating is the text field we created in the first step – Unique Product ID.

When specifying a new value, select use a formula to select a new value and enter the following:

OpportunityId & ‘_’ & PricebookEntryId & ‘_’ & TEXT(UnitPrice)

Excellent! Once you have saved the field update, you can activate the workflow and give it a test! In this scenario, you shouldn’t be able to add the same product twice for the same amount. If you do, you should receive the following error message.

As mentioned earlier, this isn’t the only solution, but it is the only solution that I could find that uses declarative functionality. A coded option will probably provide you with greater possibilities, and a customized error message, but I am a click kind of person!

If you have suggestions or other possible methods to preventing duplicate products please leave a comment below!