You need to sign in to do that
Don't have an account?

Receiving Non-Selective Query Error
Hi All,
I am receiving the above arror only for phone and Street. As email Is already idexed its working fine.
How can i make Phone and Street Fields Indexed. I contactacted salesforce support and ther redirected me to post my question here.
Can someone help me out with this.
Code:
trigger leadDuplicatePreventer on Lead (before insert, before update) {
//ORE Record Type
Map<String, Lead> leadMapORE = new Map<String, Lead>(); //for email
Map<String, Lead> leadMapOREPhone = new Map<String, Lead>(); //for phone
Map<String, Lead> leadMapOREStreet = new Map<String, Lead>(); //for street
for (Lead leadORE : System.Trigger.new) {
if(leadORE.RecordTypeId=='012i0000000VWJS')
{
// check weater any new lead with same email,phone, street is being created(list uploads)
if ((leadORE.Email != null) &&
(System.Trigger.isInsert ||
(leadORE.Email !=System.Trigger.oldMap.get(leadORE.Id).Email))) {
// Make sure another new lead isn't also a duplicate
if (leadMapORE.containsKey(leadORE.Email)) {
leadORE.Email.addError('Another new lead has the same email Address.');
} else {
leadMapORE.put(leadORE.Email, leadORE); //add email to email list
}
}
if ((leadORE.Phone != null) &&
(System.Trigger.isInsert ||
(leadORE.Phone !=System.Trigger.oldMap.get(leadORE.Id).Phone))) {
if (leadMapOREPhone.containsKey(leadORE.Phone)) {
leadORE.Email.addError('Another new lead has the same Phone Number.');
} else {
leadMapOREPhone.put(leadORE.Phone, leadORE); //add phone to phone list
}
}
if ((leadORE.Street != null) &&
(System.Trigger.isInsert ||
(leadORE.Street !=System.Trigger.oldMap.get(leadORE.Id).Street))) {
if (leadMapOREStreet.containsKey(leadORE.Street)) {
leadORE.Street.addError('Another new lead has the same Street Address.');
} else {
leadMapOREStreet.put(leadORE.Street, leadORE); //add street to street list.
}
}
}
}
//Referral Affiliate Record Type
Map<String, Lead> leadMapRA = new Map<String, Lead>(); //for email
Map<String, Lead> leadMapRAPhone = new Map<String, Lead>(); //for phone
Map<String, Lead> leadMapRAStreet = new Map<String, Lead>(); //for street
for (Lead leadRA : System.Trigger.new) {
if(leadRA.RecordTypeId=='012i0000000XXNU')
{
// check weater any new lead with same email,phone, street is being created(list uploads)
if ((leadRA.Email != null) &&
(System.Trigger.isInsert ||
(leadRA.Email !=System.Trigger.oldMap.get(leadRA.Id).Email))) {
// Make sure another new lead isn't also a duplicate
if (leadMapRA.containsKey(leadRA.Email)) {
leadRA.Email.addError('Another new lead has the same email Address.');
} else {
leadMapRA.put(leadRA.Email, leadRA); //add email to email list
}
}
if ((leadRA.Phone != null) &&
(System.Trigger.isInsert ||
(leadRA.Phone !=System.Trigger.oldMap.get(leadRA.Id).Phone))) {
if (leadMapRAPhone.containsKey(leadRA.Phone)) {
leadRA.Email.addError('Another new lead has the same Phone Number.');
} else {
leadMapRAPhone.put(leadRA.Phone, leadRA); //add phone to phone list
}
}
if ((leadRA.Street != null) &&
(System.Trigger.isInsert ||
(leadRA.Street !=System.Trigger.oldMap.get(leadRA.Id).Street))) {
if (leadMapRAStreet.containsKey(leadRA.Street)) {
leadRA.Email.addError('Another new lead has the same Street Address.');
} else {
leadMapRAStreet.put(leadRA.Street, leadRA); //add street to street list.
}
}
}
}
if(leadMapORE.size()!=0)
{
// Query for all the leads with same email address in the email list for ORE Record Type.
List<Lead> phoneore = new list<Lead>();
for (Lead lead : [SELECT Email,Name,email_notification__c, Isconverted FROM Lead WHERE Email IN :leadMapORE.KeySet()
and RecordTypeId='012i0000000VWJS' and Name!='' and Isconverted=False
and Email!='']) {
Lead newLead = leadMapORE.get(lead.Email);
if(newLead.Email != 'noemail@noemail.com')
{
if(newLead.email_notification__c == false) // check to see wheater it is through web-to-lead
{
newLead.addError(' A lead with this email already exists -' + lead.Name + '. If you do not know the Email use noemail@noemail.com');
System.debug(Logginglevel.Finest,'MsgTxt');
}
else //if lead is from web-to-lead create lead and update following fields.
{
newLead.Duplicate_lead__c = true;
newLead.Duplicate_lead_Id__c = lead.Id;
}
}
}
}
if(leadMapOREPhone.size()!=0)
{
// Query for all the leads with same email address in the email list for Referral Affiliate Record type.
for (Lead lead : [SELECT Email,Name,email_notification__c, Isconverted FROM Lead WHERE Email IN :leadMapRA.KeySet()
and RecordTypeId='012i0000000XXNU' and Name!='' and Isconverted=False
and Email!='']) {
Lead newLead = leadMapRA.get(lead.Email);
if(newLead.Email != 'noemail@noemail.com')
{
if(newLead.email_notification__c == false) // check to see wheater it is through web-to-lead
{
newLead.addError(' A lead with this email already exists -' + lead.Name + '. If you do not know the Email use noemail@noemail.com');
System.debug(Logginglevel.Finest,'MsgTxt');
}
else //if lead is from web-to-lead create lead and update following fields.
{
newLead.Duplicate_lead__c = true;
newLead.Duplicate_lead_Id__c = lead.Id;
}
}
}
}
if(leadMapOREPhone.size()!=0)
{
// Query for all the leads with same Phone in the Phone list for ORE Record Type.
for (Lead lead1 : [SELECT Email,Name,Phone,email_notification__c, Isconverted FROM Lead WHERE Phone IN :leadMapOREPhone.KeySet()
and RecordTypeId='012i0000000VWJS'and Name!='' and Isconverted=False
and Phone!='' ]) {
Lead newLead1 = leadMapOREPhone.get(lead1.Phone);
if(newLead1.Phone != '(555) 555-5555')
{
if(newLead1.email_notification__c == false ) // check to see wheater it is through web-to-lead
{
newLead1.addError(' A lead with this Phone Number already exists -'+Lead1.Name + '. If you do not know the Phone number use (555) 555-5555');
System.debug(Logginglevel.Finest,'MsgTxt');
}
else //if lead i from web-to-lead create lead and update following fields.
{
newLead1.Duplicate_lead__c = true;
newLead1.Duplicate_lead_Id__c = lead1.Id;
}
}
}
}
if(leadMapRAPhone.size()!=0)
{
// Query for all the leads with same Phone in the Phone list for Referral Affiliate Record type.
for (Lead lead1 : [SELECT Email,Name,Phone,email_notification__c, Isconverted FROM Lead WHERE Phone IN :leadMapRAPhone.KeySet()
and RecordTypeId='012i0000000XXNU' and Name!='' and Isconverted=False
and Phone!='']) {
Lead newLead1 = leadMapRAPhone.get(lead1.Phone);
if(newLead1.Phone != '(555) 555-5555')
{
if(newLead1.email_notification__c == false ) // check to see wheater it is through web-to-lead
{
newLead1.addError(' A lead with this Phone Number already exists -'+Lead1.Name + '. If you do not know the Phone number use (555) 555-5555');
System.debug(Logginglevel.Finest,'MsgTxt');
}
else //if lead i from web-to-lead create lead and update following fields.
{
newLead1.Duplicate_lead__c = true;
newLead1.Duplicate_lead_Id__c = lead1.Id;
}
}
}
}
if(leadMapOREStreet.size()!=0)
{
// Query for all the leads with same street address in the Street list for ORE Record Type.
for (Lead lead2 : [SELECT Email,Name,Phone,Street,email_notification__c, Isconverted FROM Lead WHERE Street IN :leadMapOREStreet.KeySet()
and recordtypeId= '012i0000000VWJS' and Name!='' and Isconverted=False
and Street!='']) {
Lead newLead2 = leadMapOREStreet.get(lead2.Street);
if(newLead2.email_notification__c == false) // check to see wheater it is through web-to-lead
{
newLead2.addError('A lead with this Street Address already exists.' + lead2.Name);
System.debug(Logginglevel.Finest,'MsgTxt');
}
else //if lead is from web-to-lead create lead and update following fields.
{
newLead2.Duplicate_lead__c = true;
newLead2.Duplicate_lead_Id__c = lead2.Id;
}
}
}
if(leadMapRaStreet.size()!=0)
{
// Query for all the leads with same street address in the Street list for Referral Affiliate Record type.
for (Lead lead2 : [SELECT Email,Name,Phone,Street,email_notification__c, Isconverted FROM Lead WHERE Street IN :leadMapRAStreet.KeySet()
and recordtypeId= '012i0000000XXNU' and Name!='' and Isconverted=False
and Street!='']) {
Lead newLead2 = leadMapRAStreet.get(lead2.Street);
if(newLead2.email_notification__c == false) // check to see wheater it is through web-to-lead
{
newLead2.addError('A lead with this Street Address already exists.' + lead2.Name);
System.debug(Logginglevel.Finest,'MsgTxt');
}
else //if lead is from web-to-lead create lead and update following fields.
{
newLead2.Duplicate_lead__c = true;
newLead2.Duplicate_lead_Id__c = lead2.Id;
}
}
}}
Thanks,
Vishnu
I am receiving the above arror only for phone and Street. As email Is already idexed its working fine.
How can i make Phone and Street Fields Indexed. I contactacted salesforce support and ther redirected me to post my question here.
Can someone help me out with this.
Code:
trigger leadDuplicatePreventer on Lead (before insert, before update) {
//ORE Record Type
Map<String, Lead> leadMapORE = new Map<String, Lead>(); //for email
Map<String, Lead> leadMapOREPhone = new Map<String, Lead>(); //for phone
Map<String, Lead> leadMapOREStreet = new Map<String, Lead>(); //for street
for (Lead leadORE : System.Trigger.new) {
if(leadORE.RecordTypeId=='012i0000000VWJS')
{
// check weater any new lead with same email,phone, street is being created(list uploads)
if ((leadORE.Email != null) &&
(System.Trigger.isInsert ||
(leadORE.Email !=System.Trigger.oldMap.get(leadORE.Id).Email))) {
// Make sure another new lead isn't also a duplicate
if (leadMapORE.containsKey(leadORE.Email)) {
leadORE.Email.addError('Another new lead has the same email Address.');
} else {
leadMapORE.put(leadORE.Email, leadORE); //add email to email list
}
}
if ((leadORE.Phone != null) &&
(System.Trigger.isInsert ||
(leadORE.Phone !=System.Trigger.oldMap.get(leadORE.Id).Phone))) {
if (leadMapOREPhone.containsKey(leadORE.Phone)) {
leadORE.Email.addError('Another new lead has the same Phone Number.');
} else {
leadMapOREPhone.put(leadORE.Phone, leadORE); //add phone to phone list
}
}
if ((leadORE.Street != null) &&
(System.Trigger.isInsert ||
(leadORE.Street !=System.Trigger.oldMap.get(leadORE.Id).Street))) {
if (leadMapOREStreet.containsKey(leadORE.Street)) {
leadORE.Street.addError('Another new lead has the same Street Address.');
} else {
leadMapOREStreet.put(leadORE.Street, leadORE); //add street to street list.
}
}
}
}
//Referral Affiliate Record Type
Map<String, Lead> leadMapRA = new Map<String, Lead>(); //for email
Map<String, Lead> leadMapRAPhone = new Map<String, Lead>(); //for phone
Map<String, Lead> leadMapRAStreet = new Map<String, Lead>(); //for street
for (Lead leadRA : System.Trigger.new) {
if(leadRA.RecordTypeId=='012i0000000XXNU')
{
// check weater any new lead with same email,phone, street is being created(list uploads)
if ((leadRA.Email != null) &&
(System.Trigger.isInsert ||
(leadRA.Email !=System.Trigger.oldMap.get(leadRA.Id).Email))) {
// Make sure another new lead isn't also a duplicate
if (leadMapRA.containsKey(leadRA.Email)) {
leadRA.Email.addError('Another new lead has the same email Address.');
} else {
leadMapRA.put(leadRA.Email, leadRA); //add email to email list
}
}
if ((leadRA.Phone != null) &&
(System.Trigger.isInsert ||
(leadRA.Phone !=System.Trigger.oldMap.get(leadRA.Id).Phone))) {
if (leadMapRAPhone.containsKey(leadRA.Phone)) {
leadRA.Email.addError('Another new lead has the same Phone Number.');
} else {
leadMapRAPhone.put(leadRA.Phone, leadRA); //add phone to phone list
}
}
if ((leadRA.Street != null) &&
(System.Trigger.isInsert ||
(leadRA.Street !=System.Trigger.oldMap.get(leadRA.Id).Street))) {
if (leadMapRAStreet.containsKey(leadRA.Street)) {
leadRA.Email.addError('Another new lead has the same Street Address.');
} else {
leadMapRAStreet.put(leadRA.Street, leadRA); //add street to street list.
}
}
}
}
if(leadMapORE.size()!=0)
{
// Query for all the leads with same email address in the email list for ORE Record Type.
List<Lead> phoneore = new list<Lead>();
for (Lead lead : [SELECT Email,Name,email_notification__c, Isconverted FROM Lead WHERE Email IN :leadMapORE.KeySet()
and RecordTypeId='012i0000000VWJS' and Name!='' and Isconverted=False
and Email!='']) {
Lead newLead = leadMapORE.get(lead.Email);
if(newLead.Email != 'noemail@noemail.com')
{
if(newLead.email_notification__c == false) // check to see wheater it is through web-to-lead
{
newLead.addError(' A lead with this email already exists -' + lead.Name + '. If you do not know the Email use noemail@noemail.com');
System.debug(Logginglevel.Finest,'MsgTxt');
}
else //if lead is from web-to-lead create lead and update following fields.
{
newLead.Duplicate_lead__c = true;
newLead.Duplicate_lead_Id__c = lead.Id;
}
}
}
}
if(leadMapOREPhone.size()!=0)
{
// Query for all the leads with same email address in the email list for Referral Affiliate Record type.
for (Lead lead : [SELECT Email,Name,email_notification__c, Isconverted FROM Lead WHERE Email IN :leadMapRA.KeySet()
and RecordTypeId='012i0000000XXNU' and Name!='' and Isconverted=False
and Email!='']) {
Lead newLead = leadMapRA.get(lead.Email);
if(newLead.Email != 'noemail@noemail.com')
{
if(newLead.email_notification__c == false) // check to see wheater it is through web-to-lead
{
newLead.addError(' A lead with this email already exists -' + lead.Name + '. If you do not know the Email use noemail@noemail.com');
System.debug(Logginglevel.Finest,'MsgTxt');
}
else //if lead is from web-to-lead create lead and update following fields.
{
newLead.Duplicate_lead__c = true;
newLead.Duplicate_lead_Id__c = lead.Id;
}
}
}
}
if(leadMapOREPhone.size()!=0)
{
// Query for all the leads with same Phone in the Phone list for ORE Record Type.
for (Lead lead1 : [SELECT Email,Name,Phone,email_notification__c, Isconverted FROM Lead WHERE Phone IN :leadMapOREPhone.KeySet()
and RecordTypeId='012i0000000VWJS'and Name!='' and Isconverted=False
and Phone!='' ]) {
Lead newLead1 = leadMapOREPhone.get(lead1.Phone);
if(newLead1.Phone != '(555) 555-5555')
{
if(newLead1.email_notification__c == false ) // check to see wheater it is through web-to-lead
{
newLead1.addError(' A lead with this Phone Number already exists -'+Lead1.Name + '. If you do not know the Phone number use (555) 555-5555');
System.debug(Logginglevel.Finest,'MsgTxt');
}
else //if lead i from web-to-lead create lead and update following fields.
{
newLead1.Duplicate_lead__c = true;
newLead1.Duplicate_lead_Id__c = lead1.Id;
}
}
}
}
if(leadMapRAPhone.size()!=0)
{
// Query for all the leads with same Phone in the Phone list for Referral Affiliate Record type.
for (Lead lead1 : [SELECT Email,Name,Phone,email_notification__c, Isconverted FROM Lead WHERE Phone IN :leadMapRAPhone.KeySet()
and RecordTypeId='012i0000000XXNU' and Name!='' and Isconverted=False
and Phone!='']) {
Lead newLead1 = leadMapRAPhone.get(lead1.Phone);
if(newLead1.Phone != '(555) 555-5555')
{
if(newLead1.email_notification__c == false ) // check to see wheater it is through web-to-lead
{
newLead1.addError(' A lead with this Phone Number already exists -'+Lead1.Name + '. If you do not know the Phone number use (555) 555-5555');
System.debug(Logginglevel.Finest,'MsgTxt');
}
else //if lead i from web-to-lead create lead and update following fields.
{
newLead1.Duplicate_lead__c = true;
newLead1.Duplicate_lead_Id__c = lead1.Id;
}
}
}
}
if(leadMapOREStreet.size()!=0)
{
// Query for all the leads with same street address in the Street list for ORE Record Type.
for (Lead lead2 : [SELECT Email,Name,Phone,Street,email_notification__c, Isconverted FROM Lead WHERE Street IN :leadMapOREStreet.KeySet()
and recordtypeId= '012i0000000VWJS' and Name!='' and Isconverted=False
and Street!='']) {
Lead newLead2 = leadMapOREStreet.get(lead2.Street);
if(newLead2.email_notification__c == false) // check to see wheater it is through web-to-lead
{
newLead2.addError('A lead with this Street Address already exists.' + lead2.Name);
System.debug(Logginglevel.Finest,'MsgTxt');
}
else //if lead is from web-to-lead create lead and update following fields.
{
newLead2.Duplicate_lead__c = true;
newLead2.Duplicate_lead_Id__c = lead2.Id;
}
}
}
if(leadMapRaStreet.size()!=0)
{
// Query for all the leads with same street address in the Street list for Referral Affiliate Record type.
for (Lead lead2 : [SELECT Email,Name,Phone,Street,email_notification__c, Isconverted FROM Lead WHERE Street IN :leadMapRAStreet.KeySet()
and recordtypeId= '012i0000000XXNU' and Name!='' and Isconverted=False
and Street!='']) {
Lead newLead2 = leadMapRAStreet.get(lead2.Street);
if(newLead2.email_notification__c == false) // check to see wheater it is through web-to-lead
{
newLead2.addError('A lead with this Street Address already exists.' + lead2.Name);
System.debug(Logginglevel.Finest,'MsgTxt');
}
else //if lead is from web-to-lead create lead and update following fields.
{
newLead2.Duplicate_lead__c = true;
newLead2.Duplicate_lead_Id__c = lead2.Id;
}
}
}}
Thanks,
Vishnu
You are recieving this error as your query is not selective.
The performance of a SOQL will depend on the presence of a selective filter. If a SOQL query contains at least a selective filter, the query is said to be selective. If the SOQL query does not contain a selective filter, the query is said to be un-selective and will require a full table scan, which may time out (take longer than 2 minutes) if querying large volumes of data.
From the perspective of the query optimizer, a filter can be simple or composite. A simple filter would be each of the field expressions (<field> <operator> <value>) in a condition expression that uses the AND operator. In contrast, the result of joining two or more field expressions via the OR operator is a composite filter. To further clarify:
1) fieldExpression1 AND fieldExpression2: shows 2 simple filters
2) fieldExpression1 OR fieldExpression2: is a composite filter
3) fieldExpression1 AND (fieldExpression2 OR fieldExpression3): shows 2 filters (one simple, and one composite).
Please follow the best practices to make the query selective-
https://help.salesforce.com/apex/HTViewSolution?urlname=How-can-I-make-my-SOQL-query-selective-And-the-process-to-determine-the-fields-that-can-be-custom-indexed&language=en_US
Hope it helps.
http://www.infallibletechie.com/2014/04/query-error-systemqueryexception-non.html
If this solves your problem, kindly mark it as the best answer.
Hit Like, if it saved your work :-)
Regards,
Magulan
http://www.infallibletechie.com