Trigger to prevent duplicate combination of 3 fields on insert and update

Question

We have 3 picklist fields Dom1, Dom2, and Dom3. We want to write a trigger before insert and update to prevent duplicate records with the same combination of these 3 fields.

For Example:
Dom1 Picklist Values = Yes, No
Dom2 Picklist Values= Day, Night
Dom3 Picklist Values = Call, Email, Post

If I create an Opportunity record with "Yes", "Day" and "Call", I must not be able to create another record with the same combination of "Yes", "Day", "Call".

Answer 1

The tricky part of this problem is to ensure that the order of the Doms is handled appropriately.

Consider the case where we have two records:

Record1 - Dom1:A, Dom2:B, Dom3:C

Record2 - Dom1:B, Dom2:A, Dom3:C

Is this a valid scenario? If it is then the problem is a bit more difficult to manage, especially as the number of records processed goes above 10,000.

I would suggest enforcing exclusivity as well as alphabetical order of selected Dom values in order to make identification of duplicate Doms.

If this is the case, here's what I would do: Create a new Field Called "ConcatenatedDoms" which is a text field marked as unique. Then before insert/update use a method like this:

 public static void SyncDoms(List<Account> newList, Map<Id,Accout> oldMap){
    for( Account a : newList){
        if(a.Dom1__c != null && a.Dom2__c !=null && String.valueOf(a.Dom1__c) >= String.valueOf(a.Dom2__c) || 
           a.Dom2__c != null && a.Dom3__c !=null && String.valueOf(a.Dom2__c) >= String.valueOf(a.Dom3__c) ||
           a.Dom1__c != null && a.Dom3__c !=null && String.valueOf(a.Dom1__c) >= String.valueOf(a.Dom3__c) ){
              a.AddError('Dom1, Dom2, and Dom3 Must be in exclusive alphabetical order.');
          }
        if( oldMap == null || oldMap.get(a.id).Dom1__c != a.Dom1__c || oldMap.get(a.id).Dom2__c != a.Dom2__c || oldMap.get(a.id).Dom3__c != a.Dom3__c){
            a.ConcatenatedDoms = a.Dom1__c + a.Dom2__c + a.Dom3__c;
        }
    }
}

This way, the system will catch any duplicate values and throw an error without additional queries in your apex.

Answer 2

I think Trigger is not required this can be achieved by creating an unique Text field, putting the restriction at object level and use work flow or process builder to update the field with desired formula. You might need to take care of insert and update case