function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
AAngeles10AAngeles10 

Apex Trigger - Update Field Values from Other Object (unrelated)

Hi,

I'm pretty new to writing triggers so please bear with me, I've been searching all over and can't seem to find an answer.

I have an object (Commission__c) that I want to update 3 fields (TradeID__c, Account__c, Revenue_in_Branch_Currency__c) from another object (Trade__c).
There is a text field (Deal_Number__c) on both objects that will be used to link them together.
  
trigger UpdateCommissionFields on Commission__c (before insert, before update)
{
    for (Commission__c cm: Trigger.new)
    {
    List<Trade__c> tr = [SELECT Id, Deal_Number__c, Account__c, Revenue_in_Branch_Currency__c FROM Trade__c WHERE Deal_Number__c = :cm.Deal_Number__c]; //error is in here
        for(Trade__c t: tr)
        {
        	    cm.TradeID__c = t.Id;
                cm.Account__c = t.Account__c;
                cm.Revenue_in_Branch_Currency__c = t.Revenue_in_Branch_Currency__c;
        }
    }
}

This is the error I receive with the trigger I've build

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

I understand that the issue is within the List, but I'm just not sure how to correct it.

Cheers,
Best Answer chosen by AAngeles10
AAngeles10AAngeles10
I ended up filtering the SOQL query by using CreateDate > LAST_MONTH some more to get around the error.
Here's what I ended up with.
I ended up removing one of the other fields and just used a formula field to grab the details. 
trigger UpdateCommissionFields on Commission__c (before insert, before update)
{
/*
Description:
When Commission is created,sa populate the Account, Trade and Revenue in Branch Currency Fields.
*/
for (Commission__c cm: Trigger.new)
    {
        If(cm.Deal_Number__c != null)
        {
            List<Trade__c> tr = [SELECT Id, Deal_Number__c, Account__c, Revenue_in_Branch_Currency__c 
                                        FROM Trade__c WHERE Deal_Number__c = :cm.Deal_Number__c AND CreatedDate > LAST_MONTH]; 
            for(Trade__c t: tr)
        {
               cm.TradeID__c = t.Id;
               cm.Account__c = t.Account__c;
            }
        }
    }

}

 

All Answers

HARSHIL U PARIKHHARSHIL U PARIKH
Does this SOQL always fetch 1 record? Because if not then you are overriding cm.TradeID__c with t.Id over and over again.

Try using the following trigger, 
Trigger UpdateCommissionFields on Commission__c (before insert, before update)
{
    for (Commission__c cm: Trigger.new)
    {
        If(cm.Deal_Number__c != null)
        {
            List<Trade__c> tr = [SELECT Id, Deal_Number__c, Account__c, Revenue_in_Branch_Currency__c 
                                        FROM Trade__c WHERE Deal_Number__c = :cm.Deal_Number__c LIMIT 1]; 
                                       
            If(!tr.IsEmpty())
            {
                    cm.TradeID__c = tr[0].id;
                    cm.Account__c = tr[0].Account__c;
                    cm.Revenue_in_Branch_Currency__c = tr[0].Revenue_in_Branch_Currency__c 
            }
        }
        
    }
}
Hope this helps & if it solves the query then please mark it as best answer since it will help others with similar issue!
 
AAngeles10AAngeles10
Hi Harshil,

I'm not sure if the SOQL fetches just 1 record, but that's all I need, just the record that matches on Deal Number.
We have many Commission and Trade records in the system, however the Deal Number is a unique value.

Thank you for sharing, I tried the updated trigger but I'm still getting the same error.

Cheers,
 Allan
AAngeles10AAngeles10
I ended up filtering the SOQL query by using CreateDate > LAST_MONTH some more to get around the error.
Here's what I ended up with.
I ended up removing one of the other fields and just used a formula field to grab the details. 
trigger UpdateCommissionFields on Commission__c (before insert, before update)
{
/*
Description:
When Commission is created,sa populate the Account, Trade and Revenue in Branch Currency Fields.
*/
for (Commission__c cm: Trigger.new)
    {
        If(cm.Deal_Number__c != null)
        {
            List<Trade__c> tr = [SELECT Id, Deal_Number__c, Account__c, Revenue_in_Branch_Currency__c 
                                        FROM Trade__c WHERE Deal_Number__c = :cm.Deal_Number__c AND CreatedDate > LAST_MONTH]; 
            for(Trade__c t: tr)
        {
               cm.TradeID__c = t.Id;
               cm.Account__c = t.Account__c;
            }
        }
    }

}

 
This was selected as the best answer
Chandramohan Yetukuri 1Chandramohan Yetukuri 1
Hi,
Here i could see the SOQL statement is used within for loop. I am sure whether it is theright approach and we can easily hit the governer limits.

Regards,
Chandra
 
Harshita VHarshita V
@chandramohan

You can write SOQL statement within for loops but can't write DML inside a for loop