function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Joseph Barbato 5Joseph Barbato 5 

Compare records with same field value from different Objects

Hello friends, I've been having an issue comparing a set of records across different objects. I need to retrieve a list of Lead Records where Lead.Email is the exact value as the email for a corresponding Person Account. 

I've tried doing this in Excel via a VLOOKUP, but the dataset is too large and will not work in Excel. I'm new to SOQL, so I'm wondering if this is possible. 

I'd need to retrieve a List of all Person Accounts where the email = Lead.Email with the following fields selected: Account ID, Account Name, Account Owner, Person_ID__pc. 
Best Answer chosen by Joseph Barbato 5
Biswojeet Ray 11Biswojeet Ray 11

Hi Joseph

Please run this code in your Org

List<Lead> leads = [SELECT Id, Email
    FROM Lead];
List<String> leadEmails = new List<String>();
Map<Lead, String> leadMap = new Map<Lead, String>();
for (Lead l : leads) {
    leadEmails.add(l.Email);
    leadMap.put(l, l.Email);
}
List<Account> accounts = [SELECT Id, Name, OwnerId, Email__c
    FROM Account
    WHERE Email__c IN: leadEmails];
Map<Id, List<Account>> leadAccountMap = new Map<Id, List<Account>>();
for (Lead l : leadMap.keyset()) {
    accountList = new List<Account>();
    for (Account acc : accounts) {
        if (l.Email == acc.Email__c) {
            accountList.add(acc);
        }
    }
    leadAccountMap.put(l.Id, accountList);
}
system.debug(leadAccountMap);
//Here u can see records
//To see indevisual records
for (Id leadId : leadAccountMap.keyset()) {
    system.debug(leadId);
    for (Account acc : leadAccountMap.get(leadId)) {
        system.debug(acc);
    }
}
 

.

Kindly let me know if it helps you and please mark as Best Answer.

Thanks and Regards,
Biswojeet

All Answers

Biswojeet Ray 11Biswojeet Ray 11

Hi Joseph

Please run this code in your Org

List<Lead> leads = [SELECT Id, Email
    FROM Lead];
List<String> leadEmails = new List<String>();
Map<Lead, String> leadMap = new Map<Lead, String>();
for (Lead l : leads) {
    leadEmails.add(l.Email);
    leadMap.put(l, l.Email);
}
List<Account> accounts = [SELECT Id, Name, OwnerId, Email__c
    FROM Account
    WHERE Email__c IN: leadEmails];
Map<Id, List<Account>> leadAccountMap = new Map<Id, List<Account>>();
for (Lead l : leadMap.keyset()) {
    accountList = new List<Account>();
    for (Account acc : accounts) {
        if (l.Email == acc.Email__c) {
            accountList.add(acc);
        }
    }
    leadAccountMap.put(l.Id, accountList);
}
system.debug(leadAccountMap);
//Here u can see records
//To see indevisual records
for (Id leadId : leadAccountMap.keyset()) {
    system.debug(leadId);
    for (Account acc : leadAccountMap.get(leadId)) {
        system.debug(acc);
    }
}
 

.

Kindly let me know if it helps you and please mark as Best Answer.

Thanks and Regards,
Biswojeet

This was selected as the best answer
Joseph Barbato 5Joseph Barbato 5
Thanks so much for responding. Where should I run the code in my Org? Again, I'm new to SOQL / Apex so I need specific direction and want to make sure I'm cautious.
Biswojeet Ray 11Biswojeet Ray 11

Hello Joseph

User-added image
Then Click on Developer console. Then press cntrl+E. 

Then the annonomous window will be opened. 
 

User-added image
Then paste the code there. Then Click on "Execute".

 

Kindly let me know if it helps you and please mark as Best Answer.

Thanks and Regards,
Biswojeet

Joseph Barbato 5Joseph Barbato 5
I pasted the code into the Console and got this error message: 

"Line: 14, Column: 5
Variable does not exist: accountList"
Joseph Barbato 5Joseph Barbato 5
I pasted the code into the Console and got this error message:

"Line: 14, Column: 5
Variable does not exist: accountList"
Biswojeet Ray 11Biswojeet Ray 11

Hi Joseph,

 

Please use this line.

List<Account> accountList = new List<Account>();

 

Thanks,