0

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".

0

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.

| improve this answer | |
  • All of the 3 fields Dom1__c, Dom2__c and Dom3__c are Picklist fields. And the values in each field are unique and do not repeat in the other. For example -> Dom1 has "Yes", "No", Dom2 has "Day" and "Night" and Dom3 has "Call", "Email", "Post". – Cheryl Jan 23 '19 at 18:27
  • Then just omit the first if statement, concatenate to a field marked as unique, and you should be all good! – Mitch Spano Jan 23 '19 at 18:31
  • Can we do this without having to create a new field? – Cheryl Jan 23 '19 at 18:38
0

I think Trigger is not required this can be achieved by creating an unique Text enter image description herefield, 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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.