You need to sign in to do that
Don't have an account?
matthew.w.hampton.ax1837
Need Help With Non-Selective Query
Good Evening:
I am in need of some help/suggestions here on how to make this query work. I am getting the dreded non-selective query error when I fire the trigger.
Summary
I have custom object MTU_Billing_Account__c that has a lookup field Account_Name__c that looks up to a custom object. Account_Name__c has a custom field called BTN__c. MTU_Billing_Account__c also has a lookup to the standard Accounts object called Account__c.
I have a second custom object called Billing_Account__c that has a field on it called Billing_Account_ID__c. This field is an External ID field. Billing_Account__c also has a lookup relationship to the standard Accounts object.
What I am looking to do is the following:
Take field Account_Name__r.BTN__c
Loop through Billing_Account__c records where Account_Name__r.BTN__c = Billing_Account__c.Billing_Account_ID__c (and a few other filters)
Updated Account__c on MTU_Billing_Account__c with Billing_Account__c.Account__r.ID.
Billing_Account__c has 770,815 records. Billing_Account_ID__c is an External ID and the key. I am out of filter options.
Any suggestions would be greatly appreciated.
Thanks,
Matt
trigger MTUBillingAccountAccountUpdate on MTU_Billing_Account__c (before update, before insert)
{
Map<String, MTU_Billing_Account__c> mtuAccount = new Map<String, MTU_Billing_Account__c>();
for ( MTU_Billing_Account__c mtu2 : Trigger.new )
{
mtuAccount.put( mtu2.Account_Name__r.BTN__c, mtu2);
}
Set<String> keys = mtuAccount.keyset();
Map<String, Billing_Account__c> billingAccountMap = new Map<String, Billing_Account__c>();
for(Billing_Account__c account1:[Select ID, Name, Account__r.ID, Billing_Account_ID__c from Billing_Account__c where Billing_Account_ID__c in : keys and Billing_Account_Status__c = 'Live' and Billing_System__c = 'CRIS'])
billingAccountMap.put(account1.Billing_Account_ID__c, account1);
List<MTU_Billing_Account__c> accountsToUpdate = new List <MTU_Billing_Account__c>();
for(MTU_Billing_Account__c a : trigger.new) {
if(billingAccountMap.containskey(a.Account_Name__r.BTN__c)) {
a.Account__c = billingAccountMap.get(a.Account_Name__r.BTN__c).Account__r.ID;
}
update accountsToUpdate;
}
}
I am in need of some help/suggestions here on how to make this query work. I am getting the dreded non-selective query error when I fire the trigger.
Summary
I have custom object MTU_Billing_Account__c that has a lookup field Account_Name__c that looks up to a custom object. Account_Name__c has a custom field called BTN__c. MTU_Billing_Account__c also has a lookup to the standard Accounts object called Account__c.
I have a second custom object called Billing_Account__c that has a field on it called Billing_Account_ID__c. This field is an External ID field. Billing_Account__c also has a lookup relationship to the standard Accounts object.
What I am looking to do is the following:
Take field Account_Name__r.BTN__c
Loop through Billing_Account__c records where Account_Name__r.BTN__c = Billing_Account__c.Billing_Account_ID__c (and a few other filters)
Updated Account__c on MTU_Billing_Account__c with Billing_Account__c.Account__r.ID.
Billing_Account__c has 770,815 records. Billing_Account_ID__c is an External ID and the key. I am out of filter options.
Any suggestions would be greatly appreciated.
Thanks,
Matt
trigger MTUBillingAccountAccountUpdate on MTU_Billing_Account__c (before update, before insert)
{
Map<String, MTU_Billing_Account__c> mtuAccount = new Map<String, MTU_Billing_Account__c>();
for ( MTU_Billing_Account__c mtu2 : Trigger.new )
{
mtuAccount.put( mtu2.Account_Name__r.BTN__c, mtu2);
}
Set<String> keys = mtuAccount.keyset();
Map<String, Billing_Account__c> billingAccountMap = new Map<String, Billing_Account__c>();
for(Billing_Account__c account1:[Select ID, Name, Account__r.ID, Billing_Account_ID__c from Billing_Account__c where Billing_Account_ID__c in : keys and Billing_Account_Status__c = 'Live' and Billing_System__c = 'CRIS'])
billingAccountMap.put(account1.Billing_Account_ID__c, account1);
List<MTU_Billing_Account__c> accountsToUpdate = new List <MTU_Billing_Account__c>();
for(MTU_Billing_Account__c a : trigger.new) {
if(billingAccountMap.containskey(a.Account_Name__r.BTN__c)) {
a.Account__c = billingAccountMap.get(a.Account_Name__r.BTN__c).Account__r.ID;
}
update accountsToUpdate;
}
}
As you mentioned that the Billing_Account__c object has 770,815 records - as per Salesforce best practice - the where clause needs to subset 10% or less of the data which might not be happening in your case.
You have te following options which you can try:
- You may need a custom index on the field which ill narrow down the record count - rach out to salesforce support if you wish to go down this path
- Divide the set of records according to date of creation(as Created date is indexed automatically) and add the result to a list and then use the list to loop in the FOR()