+ Start a Discussion
AndrewFisher_TGPAndrewFisher_TGP 

Insert failed: First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY

Hi all, second post here - and I have been toiling away at this all day!! Would very much appreciate some help as I can't seem to find the similar issue in the forum and a solution that I can extrapolate to this scenario.

 

Error message from log/status in Dev Console:

Insert failed: First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, trigger_name: execution of BeforeInsert caused by: System.typeException: Invalid integer: expr0

 

Bit of background, I am writing my first apex trigger to generate a total (sum) in a field on the Account which sums the total amount for related contracts, that will eventually fit within certain criteria (I haven't added this part yet as I just want the basics working first!).

 

 

I know the problem is with my map, aggregateResult/SOQL.  I somehow had it working last night, but I didn't backup & obviously didn't stop to think what I had before I started playing with it!  Originally I had the map populate when it is declared (this isn't is but i remember vaugley it was all inline - something along the lines of):

map<ID, integer> cMap_THISFY = new map<ID, integer>(AggregateResult c: [SELECT AccountID, SUM(Invoice_Amount__c) FROM Contract WHERE AccountID IN :BookingAcctIDs GROUP BY AccountID]);

 

So after tinkering and realising I didn't know where/how all the bolts go back - but it doesn't run!  I had/having all sorts of trouble with the SOQL -> cMap_THISFY - first shot at rebuilding it was:

 

    	map<ID, integer> cMap_THISFY = new map<ID, integer>();
        for (AggregateResult c: [SELECT AccountID, SUM(Invoice_Amount__c)total
                                 FROM Contract 
                                 WHERE AccountID IN :BookingAcctIDs
                                 GROUP BY AccountID])
        	cMap_THISFY.put(c.get('AccountId'), c.get('expr0'));

 But this resulted in a compile error: 'Incompatiable key type Object for MAP<Id,Integer> - which I assume is due to AggregateResult being an object datatype

 

 

I finally got it to compile - this is the full set of the code I have at the moment:

 

/* Trigger to total up the related bookings linked to an account and get a 'total' 
   booking signed amount, when a record is inserted/updated/undeleted/deleted

   Written by: Adam Gill 
   Date: 01/11/2012
   (c) The Gap Partnership
   
*/   

trigger tr_updateBookingSigned on Contract (before insert, before update, after undelete, before delete) {

    //Create new list & map to store Account ID fields & the aggregateresult of SOQL
    set<ID> BookingAcctIDs = new set<ID>();  
	
    //Generate list of Account IDs, for accounts that need to be updated, based on type of trigger
    if (trigger.isDelete){
		for (Contract c : trigger.old){
       		BookingAcctIDs.add(c.AccountID);
      	}
    }
    else {
        for (Contract c : trigger.new){
            BookingAcctIDs.add(c.AccountID);
        }
	}
    
    
    //If Account IDs have been added successfully to list, process required calculations
    if (BookingAcctIDs.size() > 0) {

        system.debug('~~~~ CREATING MAP ~~~~');
    	
        //Create new map to store Account ID & sum amount fields 
    	map<ID, integer> cMap_THISFY = new map<ID, integer>();
        for (AggregateResult c: [SELECT AccountID, SUM(Invoice_Amount__c)total
                                 FROM Contract 
                                 WHERE AccountID IN :BookingAcctIDs
                                 GROUP BY AccountID]){
			ID aID = id.valueof('AccountID');
            integer aSUM = integer.valueof('expr0');
            system.debug('*** ' + aID + ' / ' + aSUM + ' ***');
        	cMap_THISFY.put(aID, aSUM);
        }
    	
        //Create new list to generate list of 'AccountsToUpdate'
    	list<Account> AccountsToUpdate = new list<Account>();
    	
        //From set of accounts IDs, retrieve ID & Bookings_Signed_This_FY field & assign sum from ContractMap.
    	for (Account acct : [SELECT Id, Bookings_Signed_This_FY__c
                             FROM Account
                             WHERE Id IN :BookingAcctIDs]){
//				double dblBookingsSignedThisFY = cMap_THISFY.get(acct.Id);
//          	acct.Bookings_Signed_This_FY__c = dblBookingsSignedThisFY;
            acct.Bookings_Signed_This_FY__c = cMap_THISFY.get(acct.Id);
           	AccountsToUpdate.add(acct);
      	}
    	
        //Update list of accounts with totalled data.
    	update AccountsToUpdate;
    }
}

 I feel like all I am doing is moving side ways, not actually understanding what the problem is... I have looked at a number of blogs but they seem to have been coded in previous versions of the API too: here is one of the blogs I was using to disect and try and learn while reverse engineering the solution I needed.  I orginally started out using the similar map methods but I couldn't get it to complie.  And I have tried to peice together the related examples in the developer blog for aggregateresults, bulk soql queries and maps - but I think I have sent too long looking at it and I can't make heads or tails of it anymore.

 

So with that code above I have it compling and I am using the Execute section of the dev console to create a mini-test which is very basic, but this all I had when I had the script above working, and I haven't updated any permissions in the Dev Sandbox over the last few days (trying to isolate permissions/FLS):

Contract c1 = new Contract();
c1.AccountId = '001R000000kxmFf';
c1.Delivery__c = 'a0IR0000002e7cN';
c1.Key_Booking_Contact__c = '003R000000ihmt5';
c1.Invoice_Amount__c = 50000;
c1.name = 'Test booking:' + string.valueof(datetime.now());
c1.StartDate = date.today();

insert c1;

 

I am pretty sure I am missing something quite simple - but as APEX syntax is a little different from VBA/etc that I have developed with until now, any guidance or assistance would be greatly appreciated - as I am about to start pulling out my hair!! :-)

Best Answer chosen by Admin (Salesforce Developers) 
SFAdmin5SFAdmin5

the trigger below will show, on a given acocunt, the sum total of the amount field on that account's child records (just some custom object).

 

the trigger works for insert/update and deletion of child records.  just did a quick test and it works in the ui.

 

word of warning with aggregate triggers though...they're not really very powerful for orgs with a lot of data.  they hit governor limits pretty quickly, so if you have more than say 100k records on your child object this thing will probably fail.  better to do aggregations as batch jobs.  not real time but at least you can get around the governor limits

 

trigger accountTriggerTest on Custom_Object__c (after update,after insert,after delete)
{

        if ((Trigger.isInsert || Trigger.isUpdate || Trigger.isDelete) && Trigger.isAfter) 
        {    

            set<Id> AccountIds = new set<Id>();
 
                if(trigger.isInsert || trigger.isUpdate){
                    for(Custom_Object__c p : trigger.new){
                        AccountIds.add(p.Account__c);
                    }
                }
 
                if(trigger.isDelete){
                   for(Custom_Object__c p : trigger.old){
                       AccountIds.add(p.Account__c);    
                   }
                }
 
            map<Id,Double> AccountMap1 = new map <Id,Double>();
 
                for(AggregateResult q : [select Account__c,sum(Amount__c)
                    from Custom_Object__c 
                    WHERE Account__c IN :AccountIds 
                    group by Account__c]){
                    
                        AccountMap1.put((Id)q.get('Account__c'),(Double)q.get('expr0'));
                    }
 
            List <Account> AccountsToUpdate1 = new List<Account>();
 
                for(Account a : [Select Id, CustomObjectCounter__c from Account where Id IN :AccountIds]){
                    Double Sum1 = AccountMap1.get(a.Id);
                    a.Total_of_Custom_Object_Amounts__c = Sum1 ;
                   
                        AccountsToUpdate1.add(a);
                 }
 
            update AccountsToUpdate1 ;
  
       
        }
}

 

All Answers

SFAdmin5SFAdmin5

the trigger below will show, on a given acocunt, the sum total of the amount field on that account's child records (just some custom object).

 

the trigger works for insert/update and deletion of child records.  just did a quick test and it works in the ui.

 

word of warning with aggregate triggers though...they're not really very powerful for orgs with a lot of data.  they hit governor limits pretty quickly, so if you have more than say 100k records on your child object this thing will probably fail.  better to do aggregations as batch jobs.  not real time but at least you can get around the governor limits

 

trigger accountTriggerTest on Custom_Object__c (after update,after insert,after delete)
{

        if ((Trigger.isInsert || Trigger.isUpdate || Trigger.isDelete) && Trigger.isAfter) 
        {    

            set<Id> AccountIds = new set<Id>();
 
                if(trigger.isInsert || trigger.isUpdate){
                    for(Custom_Object__c p : trigger.new){
                        AccountIds.add(p.Account__c);
                    }
                }
 
                if(trigger.isDelete){
                   for(Custom_Object__c p : trigger.old){
                       AccountIds.add(p.Account__c);    
                   }
                }
 
            map<Id,Double> AccountMap1 = new map <Id,Double>();
 
                for(AggregateResult q : [select Account__c,sum(Amount__c)
                    from Custom_Object__c 
                    WHERE Account__c IN :AccountIds 
                    group by Account__c]){
                    
                        AccountMap1.put((Id)q.get('Account__c'),(Double)q.get('expr0'));
                    }
 
            List <Account> AccountsToUpdate1 = new List<Account>();
 
                for(Account a : [Select Id, CustomObjectCounter__c from Account where Id IN :AccountIds]){
                    Double Sum1 = AccountMap1.get(a.Id);
                    a.Total_of_Custom_Object_Amounts__c = Sum1 ;
                   
                        AccountsToUpdate1.add(a);
                 }
 
            update AccountsToUpdate1 ;
  
       
        }
}

 

This was selected as the best answer
AndrewFisher_TGPAndrewFisher_TGP

Fantastic, thanks for the very speedy reply Ross - and the heads up re: AggregateResult & governer limits.  Will try it out eagerly today... :-)

AndrewFisher_TGPAndrewFisher_TGP
        map<ID, double> AcctMap_ThisFY = new map<ID, double>();   
        for (AggregateResult c1: [SELECT AccountID, SUM(Invoice_Amount__c)
								FROM Contract 
                                WHERE AccountID IN :AcctID
                                	AND Status = 'Signed'
                                 	AND Delivery_Date__c = THIS_FISCAL_YEAR
                                GROUP BY AccountID]){
                                    AcctMap_ThisFY.put((Id)c1.get('AccountID'),(double)c1.get('expr0')); 
            					}

 

Hiya, so I have tailored the SOQL a tad now and run the test class - got 89% code coverage which obviously meets the SFDC criteria, just wondering if anyone knows how to get 100%?  The Dev Console shows the AcctMap_ThisFY.put(...) as the part of code that isn't covered in the test.  But for it to use the Map.get method later in the code, I would have assumed that the map.put statement would have run...