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
MXGMXG 

Apex Lead Trigger (Non-selective query against large object type (more than 200000 rows))

Hi community,
I want to create a trigger. This trigger should generate a 6 digit code and should check, if this code is unique.

Here is my code:
 
trigger LeadIdentCodeTrigger on Lead (before insert, before update) {  
    for(Lead leadObject: Trigger.new) {    
        Boolean isUniqueIdent = true;
        
        // If lead cloned, check if ident is unique
        List<Lead> leadListWithSameIdent = [SELECT Id FROM Lead WHERE Ident__c=:leadObject.Ident__c AND Id!=:leadObject.Id LIMIT 1];
        if(leadListWithSameIdent.size() > 0) {
            isUniqueIdent = false;
        }
        
        if(leadObject.Ident__c == null || isUniqueIdent == false) {
            Boolean identExists = true;
            
            while(identExists == true) {
                // 1) Create new number
                
                identExists = false;
                
                // ---------------- Stringbuilder ------------------
                final String chars = '0123456789abcdefghijklmnopqrstuvwxyz';
                String ident = '';
                while (ident.length() < 6) {
                    Integer idx = Math.mod(Math.abs(Crypto.getRandomInteger()), chars.length());
                    ident += chars.substring(idx, idx+1);
                }
                // ------------------------------------------------
                
                // 2) Does the leadId exist?
                List<Lead> leadsWithSameIdent = [SELECT Id FROM Lead WHERE Ident__c=:ident AND Id!=:leadObject.Id LIMIT 1];
                if(leadsWithSameIdent.size() > 0) {
                    identExists = true;
                }
                
                // 3) Yes => return Step 1, No => update lead
                if(identExists == false) {
                    leadObject.Ident__c = ident;
                }
            }
        }
    }
}



The code works on our test system, but when I deploy it on production I get the following error:
 
LeadIdentCodeTrigger: execution of BeforeUpdate caused by: System.QueryException: Non-selective query against large object type (more than 200000 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) ()



I don't understand the problem.. The query should return max. 1 result.. I have to search for duplicates in leads.

I tried to work with a list out of the for-loop, but then I have to load to much data (every of our 500.000 leads will have an unique 6 digit code in the future).

I hope someone has an idea. Thanks.
VamsiVamsi
Hi,

This error occurrs when we query the object having more than 200000 records in it. So we need to index the custom fields used in the Query or contact salesforce support for suggestion on custom indexing.

Give it a try by making the custom field Ident__c external ID then it becomes indexed ...

Please mark as best answer if the above helps ...!!
MXGMXG
Thank for your answer, but I try to set Ident__c to a external Id field and I get the same result.
VamsiVamsi
Better to raise a case with salesforce support and they may be able to help you with custom indexing.
VamsiVamsi
Please post this question to Salesforce stack exchange as well and experts in that community may shed some lights on this...