You need to sign in to do that
Don't have an account?
MXG
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:
The code works on our test system, but when I deploy it on production I get the following error:
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.
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.
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 ...!!