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
Chandu007Chandu007 

Is there any solutions to avoid System.QueryException?

Error: Invalid Data. 
Review all error messages below to correct your data.
Apex trigger ProjectDuplicateCheck caused an unexpected exception, contact your administrator: ProjectDuplicateCheck: 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): Trigger.ProjectDuplicateCheck: line 11, column 1

Trigger:-
trigger ProjectDuplicateCheck on MC2__Project__c (before insert,before update) {
   
    Set<String> setProjId = new Set<String>();
    For(MC2__Project__c mp : trigger.new)
    {
        setProjId.add(mp.MC2__MC_Project_ID__c);
    }
    
    if(setProjId.size() > 0 )
    { 
        List<MC2__Project__c> lstproject = [select MC2__MC_Project_ID__c,id from MC2__Project__c where MC2__MC_Project_ID__c!=null AND MC2__MC_Project_ID__c in:setProjId];
        
        
        Map<String,MC2__Project__c> Projmap = new Map<String,MC2__Project__c>();
        For(MC2__Project__c mhp: lstproject)
        {
            Projmap.put(mhp.MC2__MC_Project_ID__c,mhp);
        }
        
        For(MC2__Project__c mhp : trigger.new)
        {
            if(Projmap.containsKey(mhp.MC2__MC_Project_ID__c))
            {
                mhp.MC2__MC_Project_ID__c.addError('Project Id already exists, Please create project with new Project Id');
            }
        } 
      }
}
MKRMKR
Hi,

Overall I think it might be easiest to declare the MC2__MC_Project_ID__c as unique external id. If you are not able to do that (the field is in a managed package), I suggest that you create another custom field (unique external id) for that purpose into which you copy the value from MC2__MC_Project_ID__c.

Doing duplicate checks with unique external id's would remove the need for this trigger completely. System would enforce that there are no duplicates without single line of duplicate check code.

Regards,
Mkr
Chandu007Chandu007
Hi MKR,

Thanks for your reply. Yes, it's from managed package & we don't have any support from them now. Business is not ready to create new field as you suggested above. Is it possible to achieve with combination of Batch class & trigger??


Thanks,
Chandra