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.

24 comments

  1. Very slick! Nicely done way to use the platform.

    ReplyDelete
  2. Really this is so simple!!

    ReplyDelete
  3. hi,

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

    ReplyDelete
  4. Please post the error message that you are getting

    ReplyDelete
  5. 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. i want to make standard field - Name as unique.

    How i can achive this?

    ReplyDelete
  7. 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. Is there any way to force the name unique without increasing the size of the data for an account?

    ReplyDelete
  9. 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. 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. this solution is very nice and busy but can u tell me how to change or dislpay a our own error msg????

    ReplyDelete
  12. 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
  13. How to achieve this senario if we have two picklist fields. Pls help.

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

      Delete
  14. 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
  15. Will this prevent duplicate record creation from web-to-leads?

    ReplyDelete
  16. 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
  17. Hey guys, fortunately, Salesforce has this feature now: Duplicate management.This is the video http://salesforce.vidyard.com/watch/PdYJPLbTqiI-kCXlWrr32w

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

    ReplyDelete