You need to sign in to do that
Don't have an account?
sfadmin 131
Help with Trigger!
Hi,
I'm hoping some kind stranger will help me. I copied this trigger from another admin friend but i'm getting an error when i add it to my sandbox. The trigger is suppose to find a potential matching Account from a Lead record based on either the Account Name or a custom Domain field derived from the Account website compared to the email domain on a lead. Here's the code:
trigger FindDupes on Lead (before insert, before update) {
// initialize set for SOQL query
Set<String> emailDomains = New Set<String>();
Set<String> companyNames = New Set<String>();
// loop through leads to build sets for lookup
for (Lead myLead : Trigger.new) {
// if lead doesn't have an Account ID
if (myLead.Account__c == null) {
// if the lead has a company name
if (myLead.Company != null) {
// add to set for SOQL query lookup
companyNames.add(myLead.Company);
}
// if the lead has an email domain
if (myLead.Domain__c != null) {
// add to set for SOQL query lookup
emailDomains.add(myLead.Domain__c);
}
}
}
// perform queries and add results to maps
// initialize map of Email Domain to Account
Map<String, Account> emailToAccountMap = New Map<String, Account>();
// if there are email domains to look up
if (emailDomains.isEmpty() == false) {
// perform query matching email domains
List<Account> accounts = [SELECT Id, Domain__c FROM Account
WHERE Domain__c IN :emailDomains];
// if accounts found, add to map
if (accounts.isEmpty() == false) {
for (Account a : accounts) {
// add to map, key: Email Domain, value: Account
emailToAccountMap.put(a.Domain__c, a);
}
}
}
// initialize map of Company Name to Account
Map<String, Account> nameToAccountMap = New Map<String, Account>();
// if there are company names to look up
if (companyNames.isEmpty() == false) {
// perform query matching email domains
List<Account> accounts = [SELECT Id, Name FROM Account
WHERE Name IN :companyNames];
// if accounts found, add to map
if (accounts.isEmpty() == false) {
for (Account a : accounts) {
// add to map, key: Name, value: Account
nameToAccountMap.put(a.Name, a);
}
}
}
// loop through leads and get results form queries to fill in Account ID
for (Lead myLead : Trigger.new) {
// if lead doesn't have an Account ID
if (myLead.Account__c == null) {
// get related Account by Company Name
Account account1 = nameToAccountMap.get(myLead.Company);
// get related Account by Email Domain
Account account2 = emailToAccountMap.get(myLead.Domain__c);
// if account found by company name
if (account1 != null) {
myLead.Account__c = account1.Id;
// if account found by email domain
} else if (account2 != null) {
myLead.Account__c = account2.Id;
}
}
}
}
I went to try and test the trigger and created a new lead and recieved the following error as soon as i tried to add an email address and save the record (the domain field on Leads is a formula field isolating the domain part of the email address field).
Error: Invalid Data.
Review all error messages below to correct your data.
Apex trigger FindDupes caused an unexpected exception, contact your administrator: FindDupes: execution of BeforeUpdate caused by: System.QueryException: Non-selective query against large object type (more than 100000 rows). Consider an indexed filter or contact salesforce.com about custom indexing. Even if a field is indexed a filter might still not be selective when: 1. The filter value includes null (for instance binding with a list that contains null) 2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times): Trigger.FindDupes: line 40, column 1
Any ideas???? Help!!!
Thank you in advance!!
I'm hoping some kind stranger will help me. I copied this trigger from another admin friend but i'm getting an error when i add it to my sandbox. The trigger is suppose to find a potential matching Account from a Lead record based on either the Account Name or a custom Domain field derived from the Account website compared to the email domain on a lead. Here's the code:
trigger FindDupes on Lead (before insert, before update) {
// initialize set for SOQL query
Set<String> emailDomains = New Set<String>();
Set<String> companyNames = New Set<String>();
// loop through leads to build sets for lookup
for (Lead myLead : Trigger.new) {
// if lead doesn't have an Account ID
if (myLead.Account__c == null) {
// if the lead has a company name
if (myLead.Company != null) {
// add to set for SOQL query lookup
companyNames.add(myLead.Company);
}
// if the lead has an email domain
if (myLead.Domain__c != null) {
// add to set for SOQL query lookup
emailDomains.add(myLead.Domain__c);
}
}
}
// perform queries and add results to maps
// initialize map of Email Domain to Account
Map<String, Account> emailToAccountMap = New Map<String, Account>();
// if there are email domains to look up
if (emailDomains.isEmpty() == false) {
// perform query matching email domains
List<Account> accounts = [SELECT Id, Domain__c FROM Account
WHERE Domain__c IN :emailDomains];
// if accounts found, add to map
if (accounts.isEmpty() == false) {
for (Account a : accounts) {
// add to map, key: Email Domain, value: Account
emailToAccountMap.put(a.Domain__c, a);
}
}
}
// initialize map of Company Name to Account
Map<String, Account> nameToAccountMap = New Map<String, Account>();
// if there are company names to look up
if (companyNames.isEmpty() == false) {
// perform query matching email domains
List<Account> accounts = [SELECT Id, Name FROM Account
WHERE Name IN :companyNames];
// if accounts found, add to map
if (accounts.isEmpty() == false) {
for (Account a : accounts) {
// add to map, key: Name, value: Account
nameToAccountMap.put(a.Name, a);
}
}
}
// loop through leads and get results form queries to fill in Account ID
for (Lead myLead : Trigger.new) {
// if lead doesn't have an Account ID
if (myLead.Account__c == null) {
// get related Account by Company Name
Account account1 = nameToAccountMap.get(myLead.Company);
// get related Account by Email Domain
Account account2 = emailToAccountMap.get(myLead.Domain__c);
// if account found by company name
if (account1 != null) {
myLead.Account__c = account1.Id;
// if account found by email domain
} else if (account2 != null) {
myLead.Account__c = account2.Id;
}
}
}
}
I went to try and test the trigger and created a new lead and recieved the following error as soon as i tried to add an email address and save the record (the domain field on Leads is a formula field isolating the domain part of the email address field).
Error: Invalid Data.
Review all error messages below to correct your data.
Apex trigger FindDupes caused an unexpected exception, contact your administrator: FindDupes: execution of BeforeUpdate caused by: System.QueryException: Non-selective query against large object type (more than 100000 rows). Consider an indexed filter or contact salesforce.com about custom indexing. Even if a field is indexed a filter might still not be selective when: 1. The filter value includes null (for instance binding with a list that contains null) 2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times): Trigger.FindDupes: line 40, column 1
Any ideas???? Help!!!
Thank you in advance!!
This query:
Forces the system to search all of the records within the account object, which apparently number more than fit under the selectivity threshold. (I believe that's where the error message is saying the issue is.)
What the error is telling you is that you should try an indexed filter (i.e., include an indexed field in your WHERE clause) such that the number of rows that need to be searched is under the selectivity threshold. For more information, see: https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_VLSQ.htm
Hope that helps get you started.