Unique Field combination in Salesforce

11:23 PM

While creating fields in any object you have the option of specifying the field as UNIQUE. This means that the field can only have unique values and is meant for eradicating duplicates.

But, mostly we need to have a combination of fields to determine a unique record. For ex, say that "Account Name" and "Billing Country" together determine a unique record. In such a case, you cannot make the "Account Name" as well as the "Billing Country" fields unique, since the combination of these two fields is only unique and not they both separately.

In situations like this, we mostly write a trigger. But, there is a more easy and out-of-the-box functionality available.

STEP 1: Create a new field in the account object called "AccNameBillingAdd" with the field type "Text".


Make the field invisible to all profiles, of course the "System Administrator" would still have access to this field.


Do not add the field to any page layout.

STEP 2:  Create a new Workflow rule on the "Account" object.


Create a "Field Update" for the workflow.

Do not forget to "Activate" the workflow.

STEP 3:  See it in action, by creating two account's with the same Name and Billing Country. While you try to save the second one, you will be shown an error message as below.



The error message is meaningless, do let me know if you have any suggestions.

Share This Story

Tags: ,

24 comments

  1. Jeff DouglasAugust 3, 2010 at 2:30 AM

    Very slick! Nicely done way to use the platform.

    ReplyDelete
  2. AnonymousAugust 26, 2010 at 9:19 PM

    Really this is so simple!!

    ReplyDelete
  3. AnonymousJanuary 9, 2011 at 9:07 PM

    hi,

    iam working with this,iam getting error at fromula editor,what i have to do

    ReplyDelete
  4. Edwin RaymondJanuary 11, 2011 at 1:03 PM

    Please post the error message that you are getting

    ReplyDelete
  5. CarolineJuly 11, 2011 at 10:02 PM

    Am really glad I found this idea - have just used it with a combination of Start Date/Time and Room to stop my Education Director scheduling two classes in the same place at the same time. I think it's more useful to keep the "unique" field visible, as the error message specifies which is the conflicting record, but I'm going to put the field at the bottom of my layout, generally out of the way. But thanks for the neat suggestion!!

    ReplyDelete
  6. Devendra SAugust 9, 2011 at 11:03 PM

    i want to make standard field - Name as unique.

    How i can achive this?

    ReplyDelete
  7. EdwinAugust 11, 2011 at 3:52 PM

    You may create a new custom formula field, make it unique and the formula would be to just copy the value from the standard "Name" field to the new field.

    ReplyDelete
  8. BrendaNovember 11, 2011 at 1:15 PM

    Is there any way to force the name unique without increasing the size of the data for an account?

    ReplyDelete
  9. BrendaNovember 11, 2011 at 1:17 PM

    If I don't have a unique name for the parent accounts, then how can the secondary account link to the correct parent?

    ReplyDelete
  10. BrendaNovember 11, 2011 at 1:19 PM

    I should have noted in my previous posts that I am trying to do these activities through the Data Loader as the data source is in another app's database.

    ReplyDelete
  11. ArunDecember 14, 2011 at 10:48 PM

    its a very good and Easy

    ReplyDelete
  12. SansJanuary 9, 2012 at 11:56 AM

    very nice solution..

    ReplyDelete
  13. AnonymousFebruary 13, 2012 at 6:11 AM

    this solution is very nice and busy but can u tell me how to change or dislpay a our own error msg????

    ReplyDelete
  14. Serghei SleptovOctober 4, 2012 at 1:46 PM

    Great solution !!!

    ReplyDelete
  15. MedihaFebruary 20, 2013 at 8:04 AM

    Great solution!

    Does anyone know if this wold work on multiple identificators?

    For example, I want to hava name & mobile phone number as one identificator, last name & phone number as another and email as third identificator.

    ReplyDelete
  16. UnknownMarch 31, 2013 at 11:56 PM

    How to achieve this senario if we have two picklist fields. Pls help.

    ReplyDelete
    Replies
    1. Abhijeet MitraApril 9, 2013 at 7:50 AM

      Use the formula TEXT(value) and replace the "value" with ur picklist field. TEXT converts the LOVs of the picklist to normal text.

      Delete
  17. UnknownJune 11, 2013 at 12:12 AM

    It is a simple and smart solution but there is a bit of a problem when using unique ids like this, the limit for external ids is 3 for object, so that could be a disadvantage. If you build your structure like this you need to be aware that you may need to change your approach in the future.

    Regards.

    Carlos

    ReplyDelete
  18. UnknownJanuary 3, 2014 at 3:13 AM

    It is a COOL idea

    ReplyDelete
  19. AnonymousMarch 6, 2014 at 10:52 AM

    Will this prevent duplicate record creation from web-to-leads?

    ReplyDelete
  20. UnknownDecember 23, 2016 at 6:05 AM

    Hi ,
    if i mention in the field update formula ,like
    Rating + Type -----getting error "Error: Field Rating is a picklist field. Picklist fields are only supported in certain functions." which function i should select

    ReplyDelete
  21. AnonymousJanuary 23, 2017 at 2:29 PM

    Hey guys, fortunately, Salesforce has this feature now: Duplicate management.This is the video http://salesforce.vidyard.com/watch/PdYJPLbTqiI-kCXlWrr32w

    ReplyDelete
  22. 158745236January 20, 2018 at 3:35 AM

    good

    ReplyDelete
  23. UnknownDecember 21, 2021 at 3:41 AM

    Thanks for the solution. is there a way to customize the error msg dislayed

    ReplyDelete