Apex Code Development You need to sign in to do that
Don't have an account?
eric_wc not able to pull in ID
My trigger is failing below because I am not able to return an id for some reason in my query. Any ideas see code, unit test and error below.
Thank You for any help/ideas
trigger InvoiceCreated on Invoice__c (before insert) {
Opportunity[] updateOpportunity = new Opportunity[]{};
//Loop over every invoice passed in.
for(Invoice__c inv : Trigger.new)
{
string PC = inv.EPICOR_Project_Code__c;
System.debug('>>>>>Project code: '+PC);
ID opp = [select ID from Opportunity where EPICOR_Project_Code__c = :PC].id;
System.debug('>>>>>Update Opp: '+opp);
//if (inv.InvoiceID__c == null)
//if (in.Status == 'Closed')
//if (in.Survey_Sent__c == True)
//{
//Add them to list to update.
Opportunity thisOpportunity = new Opportunity(ID = opp);
thisOpportunity.InvoiceID__c = inv.ID;
updateOpportunity.add(thisOpportunity);
update updateOpportunity;
// }
}
}
@isTest
private class TestInvoice {
static testMethod void myUnitTest() {
// TO DO: implement unit test
Account account = new Account (Name = 'testa');
insert account;
Opportunity opportunity = new Opportunity (name = 'testo', account = account, EPICOR_Project_Code__c = 'TEST', stagename = 'Renew', closedate = system.today());
insert Opportunity;
Invoice__c[] invoices = new Invoice__c[]{
new Invoice__c(name = 'testi',
EPICOR_Project_Code__c = 'TEST',
account__c = account.id
)
};
//test InvoiceCreated trigger
insert invoices;
//cases[0].Survey_Sent__c = True;
//cases[0].Status = 'Closed';
//Test.startTest();
//update cases;
//Test.stopTest();
}
}
|
Method Name |
Total Time (ms) |
Message |
Stack Trace |
|
596.0 |
System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, InvoiceCreated: execution of BeforeInsert caused by: System.QueryException: List has no rows for assignment to SObject Trigger.InvoiceCreated: line 10, column 17: [] |
Class.TestInvoice.myUnitTest: line 39, column 9 External entry point |
10:55:38.826 (826535000)|DML_BEGIN|[39]|Op:Insert|Type:Invoice__c|Rows:1 10:55:38.829 (829252000)|CODE_UNIT_STARTED|[EXTERNAL]|01qS00000008dsp|InvoiceCreated on Invoice trigger event BeforeInsert for [new] 10:55:38.829 (829511000)|USER_DEBUG|[9]|DEBUG|>>>>>Project code: TEST 10:55:38.829 (829607000)|SOQL_EXECUTE_BEGIN|[10]|Aggregations:0|select ID from Opportunity where EPICOR_Project_Code__c = :PC 10:55:38.841 (841883000)|SOQL_EXECUTE_END|[10]|Rows:0 10:55:38.841 (841960000)|EXCEPTION_THROWN|[10]|System.QueryException: List has no rows for assignment to SObject 10:55:38.842 (842031000)|FATAL_ERROR|System.QueryException: List has no rows for assignment to SObject Trigger.InvoiceCreated: line 10, column 17 10:55:39.350 (842131000)|CUMULATIVE_LIMIT_USAGE 10:55:39.350|LIMIT_USAGE_FOR_NS|(default)| Number of SOQL queries: 6 out of 100 Number of query rows: 1 out of 50000 Number of SOSL queries: 0 out of 20 Number of DML statements: 3 out of 150 Number of DML rows: 3 out of 10000 Number of script statements: 77 out of 200000 Maximum heap size: 0 out of 3000000 Number of callouts: 0 out of 10 Number of Email Invocations: 0 out of 10 Number of fields describes: 0 out of 100 Number of record type describes: 0 out of 100 Number of child relationships describes: 0 out of 100 Number of picklist describes: 0 out of 100 Number of future calls: 0 out of 10 10:55:39.350|TOTAL_EMAIL_RECIPIENTS_QUEUED|0 10:55:39.350|STATIC_VARIABLE_LIST| InvoiceCreated:updateOpportunity:4 RenewalOpportunityClass:gd:0 RenewalOpportunityClass:renewalSetup:1002 RenewalOpportunityClass:testUtils:4 TestUtils:EMAIL_SUFFIX:12 TestUtils:URL_PREFIX:23 TestUtils:gd:8697 10:55:39.350 (842131000)|CUMULATIVE_LIMIT_USAGE_END 10:55:38.842 (842484000)|CODE_UNIT_FINISHED|InvoiceCreated on Invoice trigger event BeforeInsert for [new] 10:55:38.843 (843529000)|DML_END|[39] 10:55:38.843 (843644000)|EXCEPTION_THROWN|[39]|System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, InvoiceCreated: execution of BeforeInsert caused by: System.QueryException: List has no rows for assignment to SObject Trigger.InvoiceCreated: line 10, column 17: [] 10:55:38.846 (846422000)|FATAL_ERROR|System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, InvoiceCreated: execution of BeforeInsert caused by: System.QueryException: List has no rows for assignment to SObject Trigger.InvoiceCreated: line 10, column 17: [] Class.TestInvoice.myUnitTest: line 39, column 9 External entry point 10:55:39.355 (846470000)|CUMULATIVE_LIMIT_USAGE 10:55:39.355|LIMIT_USAGE_FOR_NS|(default)| Number of SOQL queries: 5 out of 100 Number of query rows: 1 out of 50000 Number of SOSL queries: 0 out of 20 Number of DML statements: 3 out of 150 Number of DML rows: 3 out of 10000 Number of script statements: 73 out of 200000 Maximum heap size: 0 out of 3000000 Number of callouts: 0 out of 10 Number of Email Invocations: 0 out of 10 Number of fields describes: 0 out of 100 Number of record type describes: 0 out of 100 Number of child relationships describes: 0 out of 100 Number of picklist describes: 0 out of 100 Number of future calls: 0 out of 10
Try This:
trigger InvoiceCreated on Invoice__c (before insert) { Opportunity[] updateOpportunity = new Opportunity[]{}; //Loop over every invoice passed in. for(Invoice__c inv : Trigger.new) { string PC = inv.EPICOR_Project_Code__c; System.debug('>>>>>Project code: '+PC); Opportunity opp = [select ID, InvoiceID__c from Opportunity where EPICOR_Project_Code__c = :PC]; System.debug('>>>>>Update Opp: '+opp); //if (inv.InvoiceID__c == null) //if (in.Status == 'Closed') //if (in.Survey_Sent__c == True) //{ //Add them to list to update. //Opportunity thisOpportunity = new Opportunity(ID = opp); ** Remove this Line if this works ** opp.InvoiceID__c = inv.ID; updateOpportunity.add(opp); // } } update updateOpportunity; }Also it is a best practice not to have a SOQL query in a for loop so that you can avoid governor limits. Let me know if you need/want advice on how to rewrite the query.
I don't see any thing wrong with your query but I do see one thing wrong in you trigger
thisOpportunity.InvoiceID__c = inv.ID;
In this statement inv.id will always be null as trigger.new on before insert will not have id
Now your original problem
One thing in my mind comes is your this field in EPICOR_Project_Code__c in opprtunity field is not 'Test' now as you have given it 'Test' your test method my suggestion to you is that please check is there any trigger or workflow rule on trigger which updated this field "EPICOR_Project_Code__c ".
you can verify it in test method as well like
@isTest private class TestInvoice { static testMethod void myUnitTest() { // TO DO: implement unit test Account account = new Account (Name = 'testa'); insert account; Opportunity opportunity = new Opportunity (name = 'testo', account = account, EPICOR_Project_Code__c = 'TEST', stagename = 'Renew', closedate = system.today()); insert Opportunity; Opportunity opportunityTest = [Select EPICOR_Project_Code__c From Opportunity where id =: Opportunity.id] system.debug(' Opportunity Object : '+ opportunityTest ); Invoice__c[] invoices = new Invoice__c[]{ new Invoice__c(name = 'testi', EPICOR_Project_Code__c = 'TEST', account__c = account.id ) }; //test InvoiceCreated trigger insert invoices; //cases[0].Survey_Sent__c = True; //cases[0].Status = 'Closed'; //Test.startTest(); //update cases; //Test.stopTest(); } }please Let me know with this debug result
Jake, Based on your recomendation I am not sure how the trigger knows what opportunity to update that is why I am trying to query the Opportunity object so I know what Opportunity to update.
Eric
Shashikant I inserted your debug but am not seeing it in the debug in the log after I run the test. inv.ID not existing was another concern of mine will I have to use an after insert in order for the ID to exist?
I tried add the log to the post but it is too big.
Thanks for your help
Eric
Shashikant I found out why the debug code was not showing up, dumb mistake... Here it is, the ID is found...
17:09:19.012 (1012813000)|SOQL_EXECUTE_BEGIN|[32]|Aggregations:0|Select EPICOR_Project_Code__c From Opportunity where id =: Opportunity.id 17:09:19.021 (1021854000)|SOQL_EXECUTE_END|[32]|Rows:1 17:09:19.021 (1021997000)|USER_DEBUG|[33]|DEBUG| Opportunity Object : Opportunity:{Id=006S0000005G52lIAC} 17:09:19.022 (1022131000)|DML_BEGIN|[43]|Op:Insert|Type:Invoice__c|Rows:1 17:09:19.248 (1248461000)|CODE_UNIT_STARTED|[EXTERNAL]|01qS00000008dsp|InvoiceCreated on Invoice trigger event AfterInsert for [a18S00000006CAx] 17:09:19.248 (1248662000)|USER_DEBUG|[9]|DEBUG|>>>>>Project code: TEST 17:09:19.248 (1248736000)|SOQL_EXECUTE_BEGIN|[10]|Aggregations:0|select ID from Opportunity where EPICOR_Project_Code__c = :PC 17:09:19.261 (1261064000)|SOQL_EXECUTE_END|[10]|Rows:0 17:09:19.261 (1261132000)|EXCEPTION_THROWN|[10]|System.QueryException: List has no rows for assignment to SObject 17:09:19.261 (1261192000)|FATAL_ERROR|System.QueryException: List has no rows for assignment to SObject Trigger.InvoiceCreated: line 10, column 17 17:09:19.634 (1261282000)|CUMULATIVE_LIMIT_USAGE 17:09:19.634|LIMIT_USAGE_FOR_NS|(default)| Number of SOQL queries: 7 out of 100 Number of query rows: 2 out of 50000 Number of SOSL queries: 0 out of 20 Number of DML statements: 3 out of 150 Number of DML rows: 3 out of 10000 Number of script statements: 79 out of 200000 Maximum heap size: 0 out of 3000000 Number of callouts: 0 out of 10 Number of Email Invocations: 0 out of 10 Number of fields describes: 0 out of 100 Number of record type describes: 0 out of 100 Number of child relationships describes: 0 out of 100 Number of picklist describes: 0 out of 100 Number of future calls: 0 out of 10 17:09:19.634|TOTAL_EMAIL_RECIPIENTS_QUEUED|0 17:09:19.634|STATIC_VARIABLE_LIST| InvoiceCreated:updateOpportunity:4 RenewalOpportunityClass:gd:0 RenewalOpportunityClass:renewalSetup:1002 RenewalOpportunityClass:testUtils:4 TestUtils:EMAIL_SUFFIX:12 TestUtils:URL_PREFIX:23 TestUtils:gd:8697 17:09:19.634 (1261282000)|CUMULATIVE_LIMIT_USAGE_END 17:09:19.261 (1261854000)|CODE_UNIT_FINISHED|InvoiceCreated on Invoice trigger event AfterInsert for [a18S00000006CAx] 17:09:19.262 (1262793000)|DML_END|[43] 17:09:19.262 (1262900000)|EXCEPTION_THROWN|[43]|System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, InvoiceCreated: execution of AfterInsert caused by: System.QueryException: List has no rows for assignment to SObject Trigger.InvoiceCreated: line 10, column 17: [] 17:09:19.266 (1266513000)|FATAL_ERROR|System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, InvoiceCreated: execution of AfterInsert caused by: System.QueryException: List has no rows for assignment to SObject Trigger.InvoiceCreated: line 10, column 17: [] Class.TestInvoice.myUnitTest: line 43, column 9 External entry point 17:09:19.639 (1266553000)|CUMULATIVE_LIMIT_USAGE 17:09:19.639|LIMIT_USAGE_FOR_NS|(default)| Number of SOQL queries: 6 out of 100 Number of query rows: 2 out of 50000 Number of SOSL queries: 0 out of 20 Number of DML statements: 3 out of 150 Number of DML rows: 3 out of 10000 Number of script statements: 75 out of 200000 Maximum heap size: 0 out of 3000000 Number of callouts: 0 out of 10 Number of Email Invocations: 0 out of 10 Number of fields describes: 0 out of 100 Number of record type describes: 0 out of 100 Number of child relationships describes: 0 out of 100 Number of picklist describes: 0 out of 100 Number of future calls: 0 out of 10 17:09:19.639|TOTAL_EMAIL_RECIPIENTS_QUEUED|0 17:09:19.639|STATIC_VARIABLE_LIST| RenewalOpportunityClass:gd:0 RenewalOpportunityClass:renewalSetup:1002 RenewalOpportunityClass:testUtils:4 TestUtils:EMAIL_SUFFIX:12 TestUtils:URL_PREFIX:23 TestUtils:gd:8697 17:09:19.639 (1266553000)|CUMULATIVE_LIMIT_USAGE_END 17:09:19.267 (1267001000)|CODE_UNIT_FINISHED|TestInvoice.myUnitTest 17:09:19.267 (1267017000)|EXECUTION_FINISHED 17:09:20.747|CUMULATIVE_PROFILING_BEGIN 17:09:20.747|CUMULATIVE_PROFILING|No profiling information for SOQL operations 17:09:20.747|CUMULATIVE_PROFILING|No profiling information for SOSL operations 17:09:20.747|CUMULATIVE_PROFILING|No profiling information for DML operations 17:09:20.747|CUMULATIVE_PROFILING|No profiling information for method invocations 17:09:20.747|CUMULATIVE_PROFILING_ENDEric,
Hey in the trigger I wrote, it still queries the Opportunity object. The difference is that instead of saving just the ID I am saving the entire Sobject and then accessing the ID on it later. In theory, both ways of quering the Sobject should work, I just wanted to see if it made any difference. Here is the line from my code that I am talking about:
Opportunity opp = [select ID, InvoiceID__c from Opportunity where EPICOR_Project_Code__c = :PC];
although in pulling it out I noticed that in changing your code around I forgot to add something to that line. It should look like:
Opportunity opp = new Opportunity ([select ID, InvoiceID__c from Opportunity where EPICOR_Project_Code__c = :PC LIMIT 1]);
Thanks Jake I understand now. I was not paying very good attention on Friday when I read your reply. Your query does work, I think my did too, but it looks like EPICOR_Project_Code__c is getting nulled out somewhere which is really causing the problem.
I would like some more info on how you would move the query outside of the for loop. I am guessing ou would put the results in a list then query the list but being a rookie at this I am not sure how. Also how would I know what Opportunities to add to the list befor I looped through the invoice records for the EPICOR_Project_Code__c?
Thanks for your help.
Eric
After some more tries I figured out the EPICOR_Project_Code__c is getting nulled out. Thanks for helping me figure that out!
Eric
I'm glad you figured that out. As for removing your query from inside the for loop you will basically have to reshape your trigger to do the following:
1. Loop through the list of Invoices and create a list of their IDs.
2. Query the opportunites based on this list
3. Create a nested loop structure that performs your field update.
4. Update the opportunities.
Here is some sample code to give you an idea what I am talking about:
trigger InvoiceCreated on Invoice__c (before insert) { List<ID> IDs = new List<ID>(); For (invoice__c i: Trigger.new(){ IDs.add(i.id); } List<Opportunity> updateOpportunity = new List<Opportunity>([SELECT ID, InvoiceID__c, EPICOR_Project_Code__c FROM Opportunity WHERE ID IN: IDs]); //Loop over every invoice passed in again. for(Invoice__c inv : Trigger.new){ for (Opportunity o: update Opportunity){ //These checks, if necessary, should be done in the upper for //loop so that there are less iterations in this loop //if (inv.InvoiceID__c == null) //if (in.Status == 'Closed') //if (in.Survey_Sent__c == True) if (inv.EPICOR_Project_Code__c == o.EPICOR_Project_Code__c) {o.InvoiceID__c = inv.ID;} } } update updateOpportunity; }I have not tested this, but it should give you the general idea of what you should do. Essentially, by taking the query out of the for loop, you ensure that it will only be ran one time for each execution, when if it was in the for loop it try to run 200 times on a bulk insert/update causing you to exceed the governor limits.
Jake thank you very much for your help. After getting the trigger working the requirements changed from updating the opp with the invoice id to the invoice being updated with the opp id. Using the code you provided as an example I moved the query outside the for loop but am getting an error when trying to save. "TypeSave error: Initial term of field expression must be a concrete SObject: LIST<Opportunity>" for this line invoice.Opportunity__c = opps.ID;
Thanks again for you help
Eric
trigger InvoiceCreated on Invoice__c (after insert) { /*Opportunity[] updateOpportunity = new Opportunity[]{}; //Loop over every invoice passed in. for(Invoice__c inv : Trigger.new) { string PC = inv.EPICOR_Project_Code__c; System.debug('>>>>>Project code: '+PC); ID opp = [select ID from Opportunity where EPICOR_Project_Code__c = :PC].id; System.debug('>>>>>Update Opp: '+opp); //Add them to list to update. Opportunity thisOpportunity = new Opportunity(ID = opp); thisOpportunity.InvoiceID__c = inv.ID; updateOpportunity.add(thisOpportunity); update updateOpportunity; }*/ //invoice list Invoice__c[] updateInvoices = new Invoice__c[]{}; //list to store all epicor project codes from invoices List <string> pcode = new List<string>(); //loop throgh invoices adding the projects codes to the list above For (Invoice__c inv : Trigger.new){ pcode.add(inv.EPICOR_Project_Code__c); } //list to store all the opportunitys with matching project codes List<Opportunity> opps = new List<Opportunity> ([select ID, EPICOR_Project_Code__c from Opportunity where EPICOR_Project_Code__c IN :pcode]); //Loop through invoices updating them with the opp id with matching project code For (Invoice__c invoice : Trigger.new){ if (invoice.EPICOR_Project_Code__c == opps.EPICOR_Project_Code__c) { invoice.Opportunity__c = opps.ID; updateInvoices.add(invoice); update updateInvoices; } } }Hey,
Your problem in that line is that opps is a list of sobjects so you need to select which one, essentially you need to use nested for loops:
for (Opportunity o: opps){ //for each opportunity
for Invoice__c invoice: trigger new{ //for each invoice
//Make sure you have the right opportunity for the invoice
if (opps.EPICOR_Project_Code__c ==invoice.EPICOR_Project_Code__c){
invoice.Opportunity__c = o.ID;
updateInvoices.add(invoice);
}//close if
}//close for
}//close for
update updateinvoices;
You will also notice that I moved the update statement outside of the loops as well. Since you are calling update on a collection (in this case a list), it will act on every member of the collection so you only need to call it once. This will help you stay under governor limits as well. Hope this helps, let me know if there are more problems.
Jake THANK YOU! I still need to run some more test but it looks to be working. It did not like the update statement at the end was giveing me an error about performing an dml statement on a trigger.new. Below is the final code. Thank you again as you can probaly tell by now I am no developer just an Admin with no budget that keeps getting in over his head. Thank you for taking the time to help me.
Eric
trigger InvoiceCreated on Invoice__c (before insert) { /*Opportunity[] updateOpportunity = new Opportunity[]{}; //Loop over every invoice passed in. for(Invoice__c inv : Trigger.new) { string PC = inv.EPICOR_Project_Code__c; System.debug('>>>>>Project code: '+PC); ID opp = [select ID from Opportunity where EPICOR_Project_Code__c = :PC].id; System.debug('>>>>>Update Opp: '+opp); //Add them to list to update. Opportunity thisOpportunity = new Opportunity(ID = opp); thisOpportunity.InvoiceID__c = inv.ID; updateOpportunity.add(thisOpportunity); update updateOpportunity; }*/ //invoice list Invoice__c[] updateInvoices = new Invoice__c[]{}; //list to store all epicor project codes from invoices List <string> pcode = new List<string>(); //loop throgh invoices adding the projects codes to the list above For (Invoice__c inv : Trigger.new){ pcode.add(inv.EPICOR_Project_Code__c); } //list to store all the opportunitys with matching project codes Opportunity[] opps = [select ID, EPICOR_Project_Code__c from Opportunity where EPICOR_Project_Code__c IN :pcode]; For (Opportunity o: opps){ //Loop through invoices updating them with the opp id with matching project code For (Invoice__c invoice : Trigger.new){ if (o.EPICOR_Project_Code__c == invoice.EPICOR_Project_Code__c ){ invoice.Opportunity__c = o.Id; updateInvoices.add(invoice); } } } //update updateInvoices; }You are most welcome. Just for your knowledge, the reason for the problem is that you switched it to a before insert trigger when on the one above you posted it as an after insert trigger. So essentially you are editing the data before you insert it the way you have it written, but the other way (after insert) you would insert the data, then update it, which is why you needed the DML statement at the end. That being said the way you have it posted should work fine. Good luck in the future.