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.

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.

Unique Product ID text field

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))


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:

Unique Product ID Workflow Rule Prop

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)

Unique Product ID field update

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.

Oppty Duplicate Error

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!

13 thoughts on “ Preventing Duplicate Opportunity Products ”

  1. This is a great trick for blocking duplicates that I’ve used before. The formula in the description field doesn’t do anything though. Having the “true” in the formula criteria makes the workflow rule execute every time the record is touched.


    1. Thank you Daniel! It seemed a little odd to me, but I was having trouble saving the record with out the formula in the description. I tried it again, and it works. I must have had something else in my formula other than the word true for it to kick back.


  2. It worked for Opportunity, Thanks! , I have a custom Object (Contracted Product) where I need to add products related to particular Opportunity , Similarly I need handle duplicate records . Please let me know how can I achieve this.


    1. Getting error on Visualforce Page like below

      Insert failed. First exception on row 0; first error: DUPLICATE_VALUE, duplicate value found: Unique_Product_ID__c duplicates value on record with id: a066F000016gQO4: []
      Error is in expression ‘{!add}’ in component in page cms_insertselcetedproducts: Class.CMS_InsertSelectedProducts.add: line 83, column 1

      An unexpected error has occurred. Your development organization has been notified.


      1. I’m not entirely sure if the issue is with the Visualforce page, or with the validation rule. My guess is that the visualforce page needs to be updated so that it will correctly display the error message.


  3. I used this and it works great, but it’s had some unforeseen consequences. Now, when we try to clone an opportunity with products, it also clones the unique product ID, recognizes it as a duplicate and sends back an error message. Does anyone know how we could work around that so that we can still duplicate opportunities with products and prevent duplicate line items?


    1. I haven’t looked at this for a while! when I wrote it, Process Builder wasn’t a thing, and I don’t know if Flow was either. You may be able to leverage one of those technologies for better results since they can have more logic tied to them.


  4. Replace the workflow with the a Flow [Save before you Update] and the issue with cloning is solved.

    1. Custom field = Unique Value ID
    2. Flow = Update Unique value with the formula

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

    Due to the new Flow, the OpportunityID is updated before saving and therefore you have a new unique value for that specific record only.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.