Sunday, October 28, 2012

Can we set standard field to be unique? administrators and consultants often get business requirement to set standard fields to be unique. In some companies, we need to make sure every mobile phone in Contacts have to be unique (if it is not blank).

And as we known, until now, we are not able to set standard field be unique, only custom field have this option.

So, any solution? Yes, there are few ways to get this done, from a simple one until a difficult one.

1. Trigger

Using trigger may be not a simple way, we need a developer who well understand Salesforce Apex Trigger. Developer need to write apex code in Developer instance or Sandbox instance, make sure the test method coverage above 75% and deploy it to production. 

Developer may be like this approach, although it is not something prefer by administrator. Trigger in Contact before insert and before update, query all Contact with mobile phone enter, if query result is > 0, add error and don't allow to save or update that Contact.

2. Using new custom field

Instead of using standard standard field, using the same sample as above, we can build a new Mobile Phone field with field type = Phone. And hide standard Mobile Phone field from Field-Level Security (Prefer not just hide from Page Layout).

But, if this is an enhancement, it would be more effort to extract existing data to external file, verify there is no duplicate value and import the data back to the new fields. This even become worst, if the standard field has been used in many places, such as: reports, apex code, workflow, etc.

3. Workflow + Field Update

This is prefer option with less effort. Create a new custom field and enable Unique option then hide it from page layout. Create a workflow with criteria ISNEW() || ISCHANGED(MobilePhone). Create immediate workflow action to update the new field above. 

This option may be more simple, but the error message shown is not clear as it is not say what field is duplicate, but just inform it is duplicate with which record, example: "Duplicate value on record: John Wood". User do not know exactly what is duplicate between Contact working on with John Wood, if we have more than 1 field need to be set unique.