0

In my Salesforce they are multiple accounts which currently have null value in their Email field. I am trying to figure out a way so that duplicate Email-ids are not entered. My current code is including the current row as well as I guess. So when I press save to update email of an account(which I am sure is distinct), I get email already exist message.

UPDATE ---------- SOLUTION

 integer count= [SELECT COUNT() FROM Account WHERE Email__c =:a.Email__c AND Id !=:a.Id];
        if (count > 0)

QUESTION

How to find the count of email-id except the current row?

My Code

trigger AccountCallout on Account (before update) {
   // make the asynchronous web service callout
for (Account a : Trigger.new) {
      integer count=[select count() from Account where Email__c=:a.Email__c];
        if (count > 0)
          {     
            a.Email__c.adderror('This email already exists');
          }

Thanks for the Answers, I am unable to achieve the same

trigger AccountCallout on Account (before update) {
  // make the asynchronous web service callout
 set<String> emailIds = new set<String>();
   set<Id> idSet = new set<Id>();
for (Account a : Trigger.new) {

// Comment by tushar on 15 sept 2016
// to check email and name are not null
if(a.Name == null || a.Email__c ==null || a.NumberOfEmployees > 32766){   
    if(a.Name == null || a.Email__c ==null)
    {
       a.Email__c.adderror('There was a problem updating the accounts. Email and Account name cannot be blank');
    }
    if(a.NumberOfEmployees > 32767)
    {
        a.NumberOfEmployees.adderror('Number is too large');
    }
}
else
{

  Account OldAccount = Trigger.oldMap.get(a.Id);

  // to make sure email can be changed only when its old value was null
   if((OldAccount.Email__c == null && a.Email__c != null) || OldAccount.Email__c == a.Email__c)    
    {
      // **This is where I am trytinh to avoid duplicacy**
     if(a.Email__c != Trigger.oldMap(a.id).Email__c)
        {               
    // To make sure webservice is called only when there is a change in data
    if(OldAccount.Name != a.Name || OldAccount.BillingStreet != a.BillingStreet)                          
    {
     WebServiceCallout.sendNotification(a.Name,a.Email__c, a.BillingStreet); 
    }   
   }            
    }        
   else
    {
         a.Email__c.adderror('There was a problem updating the accounts. Email cannot be changed');
    }

    }
}
 List<AggregateResult> lst = 
           [SELECT Email__c, COUNT(Id) 
            FROM Account
            WHERE Email__c IN : emailIds
            AND Id NOT IN:idSet
            AND Email__c !=null
            GROUP By Email__c
            HAVING COUNT(Id)>0];

List<String> lstStr = new List<String>();                   
for (AggregateResult ar : lst)  
{
    lstStr.add((String) ar.get('Email__c'));
}
Trigger.new[0].addError(String.join(lstStr,',') + ' already exist');    
}   
}
10
  • why can't you simply make the field unique? Did you try that? – Eric Mar 6 '17 at 19:19
  • It already has too much data – Tushar Narang Mar 6 '17 at 19:41
  • around 1000 records – Tushar Narang Mar 6 '17 at 19:41
  • 1
    I don't see your point.... You will just have to update those records to make them unique before you can edit them again. Its a data cleaning exercise. Same result as your trigger, if you get trigger working you won't be able to edit them either. Basically making it unique does the same thing. Export the data, find unique values and update them. Then set as unique – Eric Mar 6 '17 at 19:42
  • What about my answer, I figured out a way. – Tushar Narang Mar 6 '17 at 19:49
6

First, you need to get the query outside of your loop.

Second, you can do this via Declarative

Just set the Email__c field to a Unique field and possibly External Id field if needed.

Then it will let you save if no other Accounts have that email but produce an error if other accounts have that email. Null values are allowed and do not trigger the unique check.

1
  • +1 Yes this is the best and the most easy approach. However i would like to achieve the same via Apex code – Tushar Narang Mar 6 '17 at 19:49
1

Apart from Eric's suggestion, if you really handle by trigger, then this could be an approach.

Also, make sure SOQL will be outside of for loop.

trigger AccountCallout on Account (before update) 
{
// make the asynchronous web service callout
    set<String> emailIds = new set<String>();
    set<Id> idSet = new set<Id>();

    for (Account a : Trigger.new) 
     {
        if(Trigger.isUpdate)
        {
            if(a.Email__c != Trigger.oldMap(a.id).Email__c)
            {
                emailIds.add(a.Email__c);
                idSet.add(a.id);
            }
        }           
     }

    List<AggregateResult> lst = 
               [SELECT Email__c, COUNT(Id) 
                FROM Account
                WHERE Email__c IN : emailIds
                AND Id NOT IN:idSet
                AND Email__c !=null
                GROUP By Email__c
                HAVING COUNT(Id)>0];

    List<String> lstStr = new List<String>();                   
    for (AggregateResult ar : lst)  {
        lstStr.add((String) ar.get('Email__c'));
    }
    Trigger.new[0].addError(String.join(lstStr,',') + ' already exist');

}
4
  • You will need to account for nulls as well (if email was removed) – Eric Mar 5 '17 at 21:09
  • updated my answer – Santanu Boral Mar 5 '17 at 21:16
  • Thanks @SantanuBoral. But i am gettin error Variable does not exist: Trigger – Tushar Narang Mar 6 '17 at 19:16
  • Please see my updated code.. Above I have made it more clear – Tushar Narang Mar 6 '17 at 19:17

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.