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
jrosser1.39050288155405E12jrosser1.39050288155405E12 

Apex Trigger updating all records in batch with the same value

I have a trigger on opportunites that will add a field from a custom object (TSG__c)  if a match is found on .P21_Customer_ID.  The issue is that when multiple records are being updated through an upsert (Informatica Cloud) all of those opportunites are getting updated with the same field value.

Below is my trigger, please let me know where I went wrong.

Thanks again.


TRIGGER populate_TSG on Opportunity (before insert,before update) {

Set<string> ss = New Set<string>();
List<TSG__c> TheTSG=new LIST<TSG__c>();  
   
//Loop through all records in the Trigger.new collection
FOR(Opportunity O: Trigger.new)
{
    ss.Add(O.P21_Customer_ID__c );
}

TheTSG = [SELECT ID from TSG__c WHERE P21_CompanyCustomer_ID__c IN: ss];
   
    System.debug('@@@@--'+TheTSG.size());

    IF (TheTSG.size() >0)
    {
        For (Opportunity O: Trigger.new)
        {
            For(TSG__c tsg:TheTSG)
            {
               
           
            O.p21_account_number__c = tsg.ID;
            System.debug('@@@@--'+tsg.ID);
           
            }
        }
    }
}
Best Answer chosen by jrosser1.39050288155405E12
AdrianCCAdrianCC
This is written in a text editor without being saved and checked if it works:
@isTest
private class StuffClass {
	
	@isTest
	static void test_checkTSGPopulateOnOpportunityInsert() {
		// create the accounts(or whatever are the related objects for the oppty 
		// P21_Customer_ID__c field)
		List<Account> accList = new List<Account>();
		for (Integer i = 0; i < 200; i++) {
			Account newAcc = new Account(Name='TestAcc' + i);
			accList.add(newAcc);
		}
		insert accList;

		// create the TSGs
		List<TSG__c> tsgList = new List<TSG__c>();
		for (Integer i=0; i < 200; i++) {
			TSG__c newTSG = new TSG__c(Name='Test' + i, 
								P21_CompanyCustomer_ID__c = accList[i].Id);
			tsgList.add(newTSG);
		}
		insert tsgList;

		// create the Opportunities
		List<Opportunity> oppList = new List<Opportunity>();
		for (Integer i=0; i<200; i++) {
			// observe the same name for Opp and TSG
			Opportunity newOpp = new Opportunity(Name = 'Test' + i, 
										P21_Customer_ID__c = accList[i].Id);
			oppList.add(newOpp);
		}
		insert oppList;

		// the trigger has matched the opportunities with their TSGs
		// we check that the p21_account_number__c has been correctly set
		oppList = [SELECT Id, Name, p21_account_number__c, 
				p21_account_number__r.Name 
				FROM Opportunity];

		for (Opportunity opp: oppList) {
			System.assertEquals(opp.Name, opp.p21_account_number__r.Name);
		}
	}
}
I've also made some assumptions, like how the related object for both Opp and TSG is Account, and that p21_account_number__c is a lookup to TSG and not a text.

You need to addapt this to your structure

Thank you,
Adrian

All Answers

AdrianCCAdrianCC
Hi,

Where exactly are you matching the oppty.P21_Customer_ID__c with the tsg.P21_CompanyCustomer_ID__c?
You are extracting the TSG using the set of P21_Customer_ID__c from oppties but when parsing the oppties from the trigger.new you just overwrite the p21_account_number__c with the tsg.Id for all of them instead of just the one that matches...
if (!TheTSG.isEmpty()) {
    for (Opportunity o: Trigger.new) {
        for (TSG__c tsg: TheTSG) {
            if (o.P21_Customer_ID__c == tsg.P21_CompanyCustomer_ID__c) {   // make sure we match on P21_Customer_ID__c   
                o.p21_account_number__c = tsg.Id;
                System.debug('@@@@--'+tsg.ID);
                break;      // match found we can break out of the loop and go to the next opportunity
            }
        }
    }
}
Try to add this and tell me how it goes...
Also you should have a mass opportunity insert/update test in your test class for your trigger. Just create in a loop 200 oppties(or how many Informatica can updtate at a time) + ther TSG correspondent records and do an update on them to see how the trigger behaves

Ty,
Adrian
jrosser1.39050288155405E12jrosser1.39050288155405E12
I am matching the opp customer with the tsg customer in this statement: 

TheTSG = [SELECT ID from TSG__c WHERE P21_CompanyCustomer_ID__c IN: ss];

I am getting an error now with the changes you recommended.
Error: Invalid Data.
Review all error messages below to correct your data.
Apex trigger popTSG_2 caused an unexpected exception, contact your administrator: popTSG_2: execution of BeforeUpdate caused by: System.SObjectException: SObject row was retrieved via SOQL without querying the requested field: TSG__c.P21_CompanyCustomer_ID__c: Trigger.popTSG_2: line 22, column 1


Since O.p21_account_number__c  is a lookup field to the TSG__c object I am populating the TSG__c to link these two objects.  Is seems as though when the trigger files for multiple opps at the same time it is updating all of them with the same value.

Some how I need to reference them individually but still make the trigger bulk friendly.

I am at a loss.
AdrianCCAdrianCC
Yes dude... my statement is correct. Let me give you an example:

There are 2 opportunities: Opp1 with P21_Customer_ID__c = '1' and Opp2 with P21_Customer_ID__c = '2'. 
There are 2 TSG: TSG1 with P21_CompanyCustomer_ID__c = '1' and TSG2 with P21_CompanyCustomer_ID__c ='2'. 
It should be a straight match Opp1 gets the Id from TSG1 and Opp2 gets the Id from TSG2.
You get the list of TSG__c and since it is not empty you start the first for, going through the 2 opportunities. The second for starts for the Opp1 and it will first set the p21_Account to TSG1 then to TSG2. SAME for Opp2.
The second for without any if will ALWAYS set p21_account_number__c to the Id of the last TSG__c record in the TheTSG list.

It is a trivial logic  error (I hope). To see it for yourself add a debug to see the value of the TheTSG list after you SELECT it, plus another System.debug to the for that cycles the opportunities in trigger.new and a final debug after the two for statements to see the value of trigger.new. The p21_account_number__c will always have the Id of the last TSG record in list

As for the Exception that you are getting pls add the P21_CompanyCustomer_ID__c to the SELECT 
TheTSG = [SELECT ID, P21_CompanyCustomer_ID__c from TSG__c WHERE P21_CompanyCustomer_ID__c IN: ss];

Tell me how it works 

Ty,
Adrian
jrosser1.39050288155405E12jrosser1.39050288155405E12
Adrian, ok I am not getting any errors and the trigger is working testing 1 opp at a time.  What code can I add to the class to test the bulk (200+ records)?

Thanks again.
AdrianCCAdrianCC
This is written in a text editor without being saved and checked if it works:
@isTest
private class StuffClass {
	
	@isTest
	static void test_checkTSGPopulateOnOpportunityInsert() {
		// create the accounts(or whatever are the related objects for the oppty 
		// P21_Customer_ID__c field)
		List<Account> accList = new List<Account>();
		for (Integer i = 0; i < 200; i++) {
			Account newAcc = new Account(Name='TestAcc' + i);
			accList.add(newAcc);
		}
		insert accList;

		// create the TSGs
		List<TSG__c> tsgList = new List<TSG__c>();
		for (Integer i=0; i < 200; i++) {
			TSG__c newTSG = new TSG__c(Name='Test' + i, 
								P21_CompanyCustomer_ID__c = accList[i].Id);
			tsgList.add(newTSG);
		}
		insert tsgList;

		// create the Opportunities
		List<Opportunity> oppList = new List<Opportunity>();
		for (Integer i=0; i<200; i++) {
			// observe the same name for Opp and TSG
			Opportunity newOpp = new Opportunity(Name = 'Test' + i, 
										P21_Customer_ID__c = accList[i].Id);
			oppList.add(newOpp);
		}
		insert oppList;

		// the trigger has matched the opportunities with their TSGs
		// we check that the p21_account_number__c has been correctly set
		oppList = [SELECT Id, Name, p21_account_number__c, 
				p21_account_number__r.Name 
				FROM Opportunity];

		for (Opportunity opp: oppList) {
			System.assertEquals(opp.Name, opp.p21_account_number__r.Name);
		}
	}
}
I've also made some assumptions, like how the related object for both Opp and TSG is Account, and that p21_account_number__c is a lookup to TSG and not a text.

You need to addapt this to your structure

Thank you,
Adrian


This was selected as the best answer
jrosser1.39050288155405E12jrosser1.39050288155405E12
Adrian, most of your assumptions were correct, I just had to make a few changes with your class with respect to the relationship between the objects.

Currently I am only getting 54% (6/11) on the trigger coverage.

Here is the modified class,  let me know if there are any changes we can make to get better coverage.

@isTest

private class StuffClass {

    

    @isTest

    static void test_checkTSGPopulateOnOpportunityInsert() {

        // create the accounts(or whatever are the related objects for the oppty

        // P21_Customer_ID__c field)

        List<Account> accList = new List<Account>();

        for (Integer i = 0; i < 200; i++) {

            Account newAcc = new Account(P21_CompanyCustomer_ID__c = '1-10009' + i , Name='TestAcc' + i);

            accList.add(newAcc);

        }

        insert accList;



        // create the TSGs

        List<TSG__c> tsgList = new List<TSG__c>();

        for (Integer i=0; i < 200; i++) {

            TSG__c newTSG = new TSG__c(Name='Test' + i,

                                P21_CompanyCustomer_ID__c = '1-10009' + i);

            tsgList.add(newTSG);

        }

        insert tsgList;



        // create the Opportunities

        List<Opportunity> oppList = new List<Opportunity>();

        for (Integer i=0; i<200; i++) {

            // observe the same name for Opp and TSG

            Opportunity newOpp = new Opportunity(CloseDate = date.parse('08/26/2014'), Stagename = 'Won', Name = 'Test' + i,

                                        p21_account_number__c = tsgList[i].ID);

            oppList.add(newOpp);

        }

        insert oppList;



        // the trigger has matched the opportunities with their TSGs

        // we check that the p21_account_number__c has been correctly set

        oppList = [SELECT Id, Name, p21_account_number__c,

                p21_account_number__r.Name

                FROM Opportunity];


        for (Opportunity opp: oppList)  {

            System.assertEquals(opp.Name, opp.p21_account_number__r.Name);

        }

    }

}
jrosser1.39050288155405E12jrosser1.39050288155405E12
I changed the class to make it work but keep it as close as possible to yours, still getting the same coverage though.

@isTest
private class StuffClass {
   
    @isTest
    static void test_checkTSGPopulateOnOpportunityInsert() {
        // create the accounts(or whatever are the related objects for the oppty
        // P21_Customer_ID__c field)
        List<Account> accList = new List<Account>();
        for (Integer i = 0; i < 200; i++) {
            Account newAcc = new Account(Name='TestAcc' + i);
            accList.add(newAcc);
        }
        insert accList;

        // create the TSGs
        List<TSG__c> tsgList = new List<TSG__c>();
        for (Integer i=0; i < 200; i++) {
            TSG__c newTSG = new TSG__c(Name='Test' + i,
                                P21_CompanyCustomer_ID__c = accList[i].Id);
            tsgList.add(newTSG);
        }
        insert tsgList;

        // create the Opportunities
        List<Opportunity> oppList = new List<Opportunity>();
        for (Integer i=0; i<200; i++) {
            // observe the same name for Opp and TSG
            Opportunity newOpp = new Opportunity(CloseDate = date.parse('09/26/2014'), Stagename = 'Won', Name = 'Test' + i,
                                        p21_account_number__c = tsgList[i].Id);
            oppList.add(newOpp);
        }
        insert oppList;
       
       


        // the trigger has matched the opportunities with their TSGs
        // we check that the p21_account_number__c has been correctly set
        oppList = [SELECT Id, Name, p21_account_number__c,P21_Customer_ID__c,
                p21_account_number__r.Name
                FROM Opportunity];

        for (Opportunity opp: oppList) {
            System.assertEquals(opp.Name, opp.p21_account_number__r.Name);
        }
        }
    }
jrosser1.39050288155405E12jrosser1.39050288155405E12
ok, I finally got it, I had to use a white board to lay it out. 

Thank you so much for your help with all of this.


@isTest
private class StuffClass {
   
    @isTest
    static void test_checkTSGPopulateOnOpportunityInsert() {
        // create the accounts(or whatever are the related objects for the oppty
        // P21_Customer_ID__c field)
        List<Account> accList = new List<Account>();
        for (Integer i = 0; i < 200; i++) {
            Account newAcc = new Account(Name='Test' + i,P21_CompanyCustomer_ID__c = '1-10009' + i );
            accList.add(newAcc);
        }
        insert accList;

        // create the TSGs
        List<TSG__c> tsgList = new List<TSG__c>();
        for (Integer i = 0; i < 200; i++) {
            TSG__c newTSG = new TSG__c(Name='Test' + i,
                                P21_CompanyCustomer_ID__c = accList[i].P21_CompanyCustomer_ID__c);
            tsgList.add(newTSG);
        }
        insert tsgList;

        // create the Opportunities
        List<Opportunity> oppList = new List<Opportunity>();
        for (Integer i = 0; i< 200; i++) {
            // observe the same name for Opp and TSG
            Opportunity newOpp = new Opportunity(CloseDate = date.parse('09/26/2014'), Stagename = 'Won', Name = 'Test' + i,
                                        AccountID = accList[i].Id);
            oppList.add(newOpp);
        }
        insert oppList;
       
       


        // the trigger has matched the opportunities with their TSGs
        // we check that the p21_account_number__c has been correctly set
        oppList = [SELECT Id, Name, p21_account_number__c,
                p21_account_number__r.Name
                FROM Opportunity];

        for (Opportunity opp: oppList) {
            System.assertEquals(opp.Name, opp.p21_account_number__r.Name);
        }
        }
    }