Preventing Duplicate Opportunity Products

Men in Black 3

If anyone could prevent duplicates, it would be the Men in Black!

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.

Click to enlarge

Click to enlarge

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:

Click to enlarge

Click to enlarge

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)

Click to enlarge

Click to enlarge

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.

Click to enlarge

Click to enlarge

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!

  • opportunity product salesforce
    How To

    Preventing Duplicate Opportunity Products

    Tweet Pin Share ShareIf you have been struggling to find a way to prevent duplicate products, then this post is for ...
Load More Related Articles
Load More By Brent Downey
Load More In How To

11 Comments


  1. SutoCom

    May 7, 2013 at 9:59 am

  2. Daniel

    May 9, 2013 at 7:55 am

    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.

    Reply

    • Brent Downey

      May 10, 2013 at 8:55 am

      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.

      Reply

  3. Jim McCabe

    August 1, 2014 at 1:45 pm

    This is awesome and works great. However my users do not get the error message. Any thoughts?

    Reply

  4. Jim McCabe

    August 1, 2014 at 1:46 pm

    Never mind I see the error now. Thanks again

    Reply

  5. SFDC Beginner

    June 28, 2017 at 5:25 am

    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.

    Reply

    • SFDC Beginner

      June 28, 2017 at 7:01 am

      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.

      Reply

      • Brent Downey

        July 6, 2017 at 5:29 pm

        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.

        Reply

  6. Karyn

    February 6, 2018 at 12:21 pm

    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?

    Reply

    • Brent Downey

      February 21, 2018 at 11:15 am

      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.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Sponsors