function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Sid LightningSid Lightning 

Apex trigger to check if alternate phone number matches with Mobile number of any of the accounts in Salesforce?

Hi,

I am having a massive confusion,

We have a database of 400,000 records.. which calls for duplication.

I want to check if alternate phone number of any of the account matches with the Primary phone number of any of the account, If it does, there is a checkbox  called "Marked For deletion"  m that checkbox should get selected and primary phone number of that account should become the alternate phone number of the acount against which it has matched 

So lets say ,

Accoount : A  Primary No : 1234  Secondary No : 5678

Accoount : B  Primary No : 9241  Secondary No : 1234 

Accoount : C  Primary No : 9241  Secondary No :  1234

Account B and Account C should have marked for deletion checked.
and Account B's primary phone number should become Secondary phone number of Account A. 

Please help me in achieving this 
 
Best Answer chosen by Sid Lightning
Abdul KhatriAbdul Khatri
Hi Sid Lightning 

Please use the below Batch for your solution. Please try first in staging or dev org before running into Prod

Batch 
global class AccountPrimarySecondaryManageBatch implements Database.Batchable<sObject> {

    global List<String> primaryNoList = new List<String>();
    global Map<String, Account> accountPrimaryNoMap = new Map<String, Account>();
    global String query;

    global AccountPrimarySecondaryManageBatch() {
    

        AggregateResult[] groupedResults = [SELECT Primary_No__c, Count(Id) FROM Account WHERE Primary_No__c != null GROUP BY Primary_No__c];
        for (AggregateResult ar : groupedResults)  {
            primaryNoList.add((String)ar.get('Primary_No__c'));
        }
        
        if(primaryNoList.isEmpty()) return; 
        
        for(List<Account> accountList : [SELECT Id, Primary_No__c, Secondary_No__c FROM Account WHERE Primary_No__c IN :primaryNoList]) {
            
            for(Account account : accountList) {
                
                accountPrimaryNoMap.put(account.Primary_No__c, account);
            }
        }           

        query = 'SELECT Id, Primary_No__c, Secondary_No__c, Mark_for_Deletion__c FROM Account WHERE Secondary_No__c IN :primaryNoList';
    }
    
    global Database.QueryLocator start(Database.BatchableContext bc) {
        return Database.getQueryLocator(query);
    }
    
    global void execute(Database.BatchableContext bc, List<Account> scope) {
        
        Map<String, String> secondaryToPrimaryMap = new Map<String, String>();
            
        for(Account accountForDeletion : scope) {
            
            accountForDeletion.Mark_For_Deletion__c = true;
            
            if(secondaryToPrimaryMap.ContainsKey(accountForDeletion.Secondary_No__c)) continue;
            
            secondaryToPrimaryMap.put(accountForDeletion.Secondary_No__c, accountForDeletion.Primary_No__c);
        }
        
        
        for(Account account : accountPrimaryNoMap.values()) {
            
            if(!secondaryToPrimaryMap.ContainsKey(account.Primary_No__c)) continue;
            
            account.Secondary_No__c = secondaryToPrimaryMap.get(account.Primary_No__c);
        }
            
        Database.Update(accountPrimaryNoMap.values());
        Database.Update(scope);        
    }
    
    global void finish(Database.BatchableContext bc) {
    }
}

Test Class 
@isTest
public class AccountPrimarySecondaryManageBatch_Test {

    static testmethod void test_account_swapping() {
       
        insert new Account (Name = 'A', Primary_No__c = '1234', Secondary_No__c = '5678');
        insert new Account (Name = 'B', Primary_No__c = '9241', Secondary_No__c = '1234');
        insert new Account (Name = 'C', Primary_No__c = '9241', Secondary_No__c = '1234');
        
        test.startTest();
        AccountPrimarySecondaryManageBatch job = new AccountPrimarySecondaryManageBatch();
        Database.executeBatch(new AccountPrimarySecondaryManageBatch());        
        test.stopTest();
        
        Account A = [SELECT  Name, Primary_No__c, Secondary_No__c, Mark_for_Deletion__c FROM Account WHERE Name = 'A'];
        Account B = [SELECT  Name, Primary_No__c, Secondary_No__c, Mark_for_Deletion__c FROM Account WHERE Name = 'B'];
        Account C = [SELECT  Name, Primary_No__c, Secondary_No__c, Mark_for_Deletion__c FROM Account WHERE Name = 'C'];        

        system.assert(A.Secondary_No__c == B.Primary_No__c);
        system.assert(A.Secondary_No__c == C.Primary_No__c);
        system.assert(B.Mark_for_Deletion__c == C.Mark_for_Deletion__c == true);
    }
}

 

All Answers

Abdul KhatriAbdul Khatri
Couple of Questions and Suggestions
  1. What happened to the current Secondary No on Account A which is 5678, Is it OK to overwrite that with the one from Account B and C.
  2. Why don't we put a validation check of not allowing the Secondary No. if there is one as Primary No. already exist.
  3. Why the Primary No and the Secondary No of Account B and C are the same, why the system allowing that in the first place. I guess if we put a prevention measures not to allow that.
Rameshwar gaurRameshwar gaur
What happen when Account B And Account C Primary Number are also different.What will happen in that case.
Michael Anderson 40Michael Anderson 40
Hi Sid, interesting challenge.
1. Seems like you've got a one-time cleanup effort on all 400K accounts, which I would tackle with an export of the data into Excel, then sort and update data, and load back to SF. 
2. Going forward it would be best to block the creation of the duplicate accounts before they occur. It seems you could accomplish this in an Account trigger before insert. In the trigger you'll want to query Accounts where Primary Number is equal to the Secondary Number of the Account being created. If the query comes back with a result you can take that Account ID that was found and put it into an Error message, then throw that error message on the top of the page for the user to see.
3. If the account is coming through integration or in way that doesn't allow you to block creation, then you could allow the trigger to go and update the Account it found (assuming it only found one). That makes me a bit nervous. If my secondary number matches your primary number then you loose your secondary number to my primary number.
Good luck!
Sid LightningSid Lightning
@Abdul : sorry for the mistake.. Primary number will always be different

Validation rule is Ok, but we want to enter records in system right , and validation rule only match for the same record, I wnt it for other records
Rameshwar gaurRameshwar gaur
Hi sid 
If you want to check all duplicate record on your org.Write a batch class and run it for once .That solve your problem of duplicate save accounts. Then make a trigger on insert and update so in future when a new account create/update its check for the secoundry number is not present in your save record primary No.

One more thing 
Work it like that it help you a lot.

Accoount : A  Primary No : 1234  Secondary No : 5678

Accoount : B  Primary No : 9241  Secondary No : 1234 

Accoount : C  Primary No : 9241  Secondary No :  1234


New Value 

Accoount : A  Primary No : 1234  Secondary No : 5678 ,9241 

That way you don't lose any data.
Hope that solve your problem.
Abdul KhatriAbdul Khatri
Hi Sid Lightning 

Please use the below Batch for your solution. Please try first in staging or dev org before running into Prod

Batch 
global class AccountPrimarySecondaryManageBatch implements Database.Batchable<sObject> {

    global List<String> primaryNoList = new List<String>();
    global Map<String, Account> accountPrimaryNoMap = new Map<String, Account>();
    global String query;

    global AccountPrimarySecondaryManageBatch() {
    

        AggregateResult[] groupedResults = [SELECT Primary_No__c, Count(Id) FROM Account WHERE Primary_No__c != null GROUP BY Primary_No__c];
        for (AggregateResult ar : groupedResults)  {
            primaryNoList.add((String)ar.get('Primary_No__c'));
        }
        
        if(primaryNoList.isEmpty()) return; 
        
        for(List<Account> accountList : [SELECT Id, Primary_No__c, Secondary_No__c FROM Account WHERE Primary_No__c IN :primaryNoList]) {
            
            for(Account account : accountList) {
                
                accountPrimaryNoMap.put(account.Primary_No__c, account);
            }
        }           

        query = 'SELECT Id, Primary_No__c, Secondary_No__c, Mark_for_Deletion__c FROM Account WHERE Secondary_No__c IN :primaryNoList';
    }
    
    global Database.QueryLocator start(Database.BatchableContext bc) {
        return Database.getQueryLocator(query);
    }
    
    global void execute(Database.BatchableContext bc, List<Account> scope) {
        
        Map<String, String> secondaryToPrimaryMap = new Map<String, String>();
            
        for(Account accountForDeletion : scope) {
            
            accountForDeletion.Mark_For_Deletion__c = true;
            
            if(secondaryToPrimaryMap.ContainsKey(accountForDeletion.Secondary_No__c)) continue;
            
            secondaryToPrimaryMap.put(accountForDeletion.Secondary_No__c, accountForDeletion.Primary_No__c);
        }
        
        
        for(Account account : accountPrimaryNoMap.values()) {
            
            if(!secondaryToPrimaryMap.ContainsKey(account.Primary_No__c)) continue;
            
            account.Secondary_No__c = secondaryToPrimaryMap.get(account.Primary_No__c);
        }
            
        Database.Update(accountPrimaryNoMap.values());
        Database.Update(scope);        
    }
    
    global void finish(Database.BatchableContext bc) {
    }
}

Test Class 
@isTest
public class AccountPrimarySecondaryManageBatch_Test {

    static testmethod void test_account_swapping() {
       
        insert new Account (Name = 'A', Primary_No__c = '1234', Secondary_No__c = '5678');
        insert new Account (Name = 'B', Primary_No__c = '9241', Secondary_No__c = '1234');
        insert new Account (Name = 'C', Primary_No__c = '9241', Secondary_No__c = '1234');
        
        test.startTest();
        AccountPrimarySecondaryManageBatch job = new AccountPrimarySecondaryManageBatch();
        Database.executeBatch(new AccountPrimarySecondaryManageBatch());        
        test.stopTest();
        
        Account A = [SELECT  Name, Primary_No__c, Secondary_No__c, Mark_for_Deletion__c FROM Account WHERE Name = 'A'];
        Account B = [SELECT  Name, Primary_No__c, Secondary_No__c, Mark_for_Deletion__c FROM Account WHERE Name = 'B'];
        Account C = [SELECT  Name, Primary_No__c, Secondary_No__c, Mark_for_Deletion__c FROM Account WHERE Name = 'C'];        

        system.assert(A.Secondary_No__c == B.Primary_No__c);
        system.assert(A.Secondary_No__c == C.Primary_No__c);
        system.assert(B.Mark_for_Deletion__c == C.Mark_for_Deletion__c == true);
    }
}

 
This was selected as the best answer
Abdul KhatriAbdul Khatri
After cleaning the data using the above batch here is the trigger to prevent it happening in future. Also Update the test class that covers both the Batch and trigger
  
trigger ManagePrimarySecondaryNo on Account (before insert, before update) {
    
    Map<String, Account> tempSecNoMap = new Map<String, Account>();

    for (Account accountRec : trigger.new) {
        
        if (trigger.isBefore && 
            	((trigger.isInsert && accountRec.Secondary_No__c != null) || (trigger.isUpdate & accountRec.Secondary_No__c != trigger.oldMap.get(accountRec.Id).Secondary_No__c)))
        {
            tempSecNoMap.put(accountRec.Secondary_No__c, accountRec);
        }
   
    }
    
    if(tempSecNoMap.isEmpty()) return;
    
    List<Account> accountList = [SELECT Id, Primary_No__c FROM Account WHERE Primary_No__c IN :tempSecNoMap.keySet()];
    
	if(accountList.isEmpty()) return;
    
    for(Account accountRec : accountList) {
        
        if(tempSecNoMap.containsKey(accountRec.Primary_No__c))
        {
            tempSecNoMap.get(accountRec.Primary_No__c).addError('Cannot have Secondary No when Primary No for the same number exists.');
        }
    }
}

Updated Test Class 
@isTest
public class AccountPrimarySecondaryManageBatch_Test {

    static testmethod void test_account_swapping() {
       
        insert new Account (Name = 'A', Primary_No__c = '1234', Secondary_No__c = '5678');
        insert new Account (Name = 'B', Primary_No__c = '9241', Secondary_No__c = '1234');
        insert new Account (Name = 'C', Primary_No__c = '9241', Secondary_No__c = '1234');
        
        test.startTest();
        AccountPrimarySecondaryManageBatch job = new AccountPrimarySecondaryManageBatch();
        Database.executeBatch(new AccountPrimarySecondaryManageBatch());        
        test.stopTest();
        
        Account A = [SELECT  Name, Primary_No__c, Secondary_No__c, Mark_for_Deletion__c FROM Account WHERE Name = 'A'];
        Account B = [SELECT  Name, Primary_No__c, Secondary_No__c, Mark_for_Deletion__c FROM Account WHERE Name = 'B'];
        Account C = [SELECT  Name, Primary_No__c, Secondary_No__c, Mark_for_Deletion__c FROM Account WHERE Name = 'C'];        

        system.assert(A.Secondary_No__c == B.Primary_No__c);
        system.assert(A.Secondary_No__c == C.Primary_No__c);
        system.assert(B.Mark_for_Deletion__c == C.Mark_for_Deletion__c == true);
    }
    
    static testmethod void test_account_prevention() {
 
		test.startTest();
        insert new Account (Name = 'A', Primary_No__c = '1234', Secondary_No__c = '5678');
        try {
        	insert new Account (Name = 'B', Primary_No__c = '9241', Secondary_No__c = '1234');
        }catch (Exception ex){
            system.assert(ex.getMessage().contains('Cannot have Secondary No when'));
        }
        test.stopTest();

    }
}

Let me know if all that helped
Abdul KhatriAbdul Khatri
Hey there, was the last post helpful.

Please mark it best if helped.
Sid LightningSid Lightning
Thanks Abdul. It was a big help.. Do you provide, will love to know