0

Is it possible to use SOQL to compare two different fields for duplicate values across records? For example, I need to check them on 2 different records. They would be "duplicates" if Phone from Record1 matches Mobile from Record2.

  • Possible duplicate of Unknown Error Parsing Lead Query. In short, Its not possible in Salesforce with SOQL query, have to use a formula field and use it for filter. – Raul Apr 16 '17 at 17:28
  • And can I use that formula field to compare fields belonging to different records? eg: Lead1 has Phone 000111 and Lead2 has Mobile 000111 => they are duplicates! – Adrian Suteu Apr 16 '17 at 17:39
  • That's not possible with SOQL. You need to write some logic in apex for it. Whats the scenario. – Raul Apr 16 '17 at 18:39
  • I see. The scenario is: An existing Lead has a value for the field Phone: Let's say 115-115-000 and another value for the field Mobile: 100-100-000. And the same Lead is about to be created again without any value for Phone and the value 115-115-000 for Mobile. So I need the user to be alerted that the Lead is already in the system due to the existing record's Phone = new record's Mobile. But I am new to Salesforce so I have to learn and understand apex first :-) – Adrian Suteu Apr 16 '17 at 20:04
1

For what you want to accomplish you can use one of the very standard triggers that SFDC has been using for years. With some small modifications, you can have your Apex trigger doing exactly what you want.

This code will check for Phone and MobilePhone numbers on any Lead before you try to insert or update a record. If it finds that any of those two fields are equal to a Lead record already in the system it will fire an error on the field that is causing the possible duplication.

trigger leadPhoneAndMobilePhoneDup on Lead (before insert, before update){

Map<String, Lead> leadMap =new Map<String, Lead>();
for (Lead lead : System.Trigger.new)
{
 if ((lead.Phone !=null) &&
       (System.Trigger.isInsert ||
         (lead.Phone != System.Trigger.oldMap.get(lead.Id).Phone)))
    {

     if (leadMap.containsKey(lead.Phone))
        {
            lead.Phone.addError('Another new lead has the same Phone address.');
        }else{
            leadMap.put(lead.Phone, lead);
        }
   }

 if ((lead.MobilePhone !=null) &&
       (System.Trigger.isInsert ||
         (lead.MobilePhone != System.Trigger.oldMap.get(lead.Id).MobilePhone)))
    {

     if (leadMap.containsKey(lead.MobilePhone))
        {
            lead.MobilePhone.addError('Another new lead has the same Phone address.');
        }else{
            leadMap.put(lead.MobilePhone, lead);
        }
   }
}


for (Lead lead : [SELECT Phone FROM Lead
                  WHERE Phone IN :leadMap.KeySet()])
{
    Lead newLead = leadMap.get(lead.Phone);
    newLead.Phone.addError('A lead with this Phone address already exists.');
}

for (Lead lead : [SELECT MobilePhone FROM Lead
                  WHERE MobilePhone IN :leadMap.KeySet()])
{
    Lead newLead = leadMap.get(lead.MobilePhone);
    newLead.MobilePhone.addError('A lead with this Mobile Phone address already exists.');
  }
}

Try out this one and let me know if it works.

Adrian, we encourage here to try things yourself as 'coding yourself' a solution before asking. Next time try to find out a bit more information about possible solutions before asking, you will get a better response.

The scenario that you are describing here it is very much what an Apex Trigger will do. Apex Triggers are used to handle DML operations that could be impossible to do otherwise. Please, take a look at the basic information available in SFDC documentation:

Salesforce - Apex Triggers

Good luck!

  • Great solution. Thnak you very much @Carlos Naranjo – Adrian Suteu Apr 17 '17 at 10:15

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.