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, 2017 at 19:19
  • It already has too much data Mar 6, 2017 at 19:41
  • around 1000 records Mar 6, 2017 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, 2017 at 19:42
  • What about my answer, I figured out a way. Mar 6, 2017 at 19:49

2 Answers 2

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 Mar 6, 2017 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, 2017 at 21:09
  • updated my answer Mar 5, 2017 at 21:16
  • Thanks @SantanuBoral. But i am gettin error Variable does not exist: Trigger Mar 6, 2017 at 19:16
  • Please see my updated code.. Above I have made it more clear Mar 6, 2017 at 19:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .