Apex Code Development You need to sign in to do that
Don't have an account?
TeraMc INVALID_CROSS_REFERENCE_KEY when testing Opportunity Insert
I am learning to code APEX as I go and piecing together how to test and deploy as well. Through trial and error I was able to write a trigger that chooses the pricebook of an opportunity based upon what is selected in the drop down Lead Source:
trigger setPriceBook on Opportunity (before insert) {
ID PRICEBOOK_YOUCAN = '01s300000002kelAAA';
ID PRICEBOOK_QBPP = '01s300000002kZ2AAI';
ID PRICEBOOK_B2B = '01s300000002kZCAAY';
ID PRICEBOOK_DATAREF = '01s300000002kZ7AAI';
ID PRICEBOOK_STANDARD = '01s300000006i5xAAA' ;
ID PRICEBOOK_WINBACK = '01s300000002kegAAA' ;
String SOURCE_YOUCAN = 'YouCan';
String SOURCE_QBPP = 'QBPP';
String SOURCE_B2B = 'B2B';
String SOURCE_DATAREF = 'Data Referral';
String SOURCE_WINBACK = 'WinBack' ;
for( Opportunity oppty : trigger.new ) {
if ( oppty.LeadSource == SOURCE_YOUCAN ) {
oppty.Pricebook2Id = PRICEBOOK_YOUCAN;
}
else if ( oppty.LeadSource == SOURCE_QBPP ) {
oppty.Pricebook2Id = PRICEBOOK_QBPP;
}
else if ( oppty.LeadSource == SOURCE_B2B ) {
oppty.Pricebook2ID = PRICEBOOK_B2B;
}
else if (oppty.LeadSource == SOURCE_DATAREF ) {
oppty.Pricebook2ID = PRICEBOOK_DATAREF;
}
else if (oppty.LeadSource == SOURCE_WINBACK ) {
oppty.PriceBook2ID = PRICEBOOK_WINBACK;
}
else {
oppty.Pricebook2ID = PRICEBOOK_STANDARD;
}
}
}
Probably not the cleanest code ever...but it worked.
My problem is when I wrote my test class, which is even dirtier. It initially compiled and tested clean with 80% coverage. I thought I was ready to go until I realised that I had left out a variable in the trigger. When that got saved it took me to under 75% so I had to add more test cases in. I have no idea how I got so messed up, but as a result of all my trial and error, I can't even figure out what originally worked:
@isTEST
private class setPriceBookTest {
static testMethod void testsetPriceBookYouCan () {
//Create new YouCan Opportunity
Opportunity oppYouCan = new Opportunity ();
oppYouCan.Description = 'TestYouCan';
oppYouCan.LeadSource = 'YouCan';
oppYouCan.StageName = 'New';
oppYouCan.Name = 'YouCanTest2';
oppYouCan.CloseDate = System.Today();
insert oppYouCan;
//Select the pricebook
Opportunity YouCanPriceBook = [SELECT Pricebook2ID
FROM Opportunity
WHERE Name = 'YouCanTest2'
LIMIT 1];
ID PriceBookYouCan = '01sQ00000008bamIAA';
//Verify Price book
System.assertNotEquals(null, YouCanPriceBook);
//Create new QBPP Opportunity
Opportunity oppQBPP = new Opportunity () ;
oppQBPP.Description = 'TestQBPP';
oppQBPP.LeadSource = 'QBPP';
oppQBPP.StageName = 'New';
oppQBPP.Name = 'QBPPTest2';
oppQBPP.CloseDate = System.Today() ;
Database.insert (oppQBPP);
//Select the pricebook
Opportunity QBPPPriceBook = [SELECT Pricebook2ID
FROM Opportunity
WHERE Name = 'QBPPTest2'
LIMIT 1];
ID PriceBookQBPP = '01sQ00000008bahIAA';
//Verify Price book
System.assertNotEquals (null, QBPPPriceBook);
//Create new WinBack Opportunity
Opportunity oppWinBack = new Opportunity () ;
oppWinBack.Description = 'TestWinBack';
oppWinBack.LeadSource = 'WinBack';
oppWinBack.StageName = 'New';
oppWinBack.Name = 'WimBackTest2';
oppWinBack.CloseDate = System.Today() ;
Database.insert (oppWinBack);
//Select the pricebook
Opportunity WinBackPriceBook = [SELECT Pricebook2ID
FROM Opportunity
WHERE Name = 'WinBackTest2'
LIMIT 1];
//Verify Price book
System.assertNotEquals (null, WinBackPriceBook);
}
}
The error that I currently get when I run the test is:
System.DmlException: Insert failed. First exception on row 0; first error: INVALID_CROSS_REFERENCE_KEY, invalid cross reference id: []
I have read quite a few pages that make it sound like this is a permissions problem, however I believe I am running this test as a System Administrator who would have access to any accounts.
Any help would be appreciated as I have been at this for over a week and still feel like I am going in circles.
Hi TeraMc:
You're going in the right direction! Try changing your assert:
All Answers
TeraMc:
The error means that when you are inserting an opportunity, the pricebook ID is invalid. I see a couple of design issues:
1. As a general rule, you should never 'hard code' ID values in Apex code. The ID values will be different when using the sandbox versus production orgs.
2. You should consider redesigning this logic to use a custom object that has two fields, a Lead Source string and a lookup field to the pricebook. Then change your trigger to use the custom object as a translation table to simply lookup the pricebook ID based on the LeadSource. Then your app is more configurable and you will not need specific logic for every variation as you do now.
Let me know if this helps.
John,
I found your response to TeraMc's query very interesting because I'd recently been pondering using custom objects as reference tables for translation for a variety of situations I come across. What I'm wondering though is how you create a custom object and then add a lookup field to the pricebook.
If I create newObject__c and then go to add a custom field which is a lookup relationship, "Pricebook" isn't a choice I've ever seen for the "Related To" choice.
Is this what you meant or is there another way?
Thanks.
InBlightly:
You're right, it looks like Pricebook2 is not a top level object. So to build a translation table, you would need to use a de-coupled type of relationship. For example, in the case above, create a custom object that has the LeadSource and Pricebook name as two fields, then use the Leadsource to get the pricebook name, then use the pricebook name to lookup the pricebook ID when needed. This is not ideal, but much better design thatn hardcoding the ID in the APEX script. As long as the translation table is fairly small, you could load the whole table into a map collection to be more efficient.
Thanks for the advice jhenning. I have updated my trigger as follows:
trigger setPriceBook on Opportunity (before insert) {
Pricebook2 PRICEBOOK_YOUCAN = [SELECT Id from Pricebook2 WHERE Name = 'YouCan'];
Pricebook2 PRICEBOOK_QBPP = [SELECT Id from Pricebook2 WHERE Name = 'QBPP'];
etc. so that I don't have to hard code the id. I know I am still hard coding the names, but it is about as far as I can go with coding right now and that fact that it compiles still amazes me.
I have updated my test class as well to remove the hard coded ids, but now I am having trouble getting my validation to go through. This is the part of code that is throwing the error:
Opportunity oppYouCanPriceBook = [SELECT Pricebook2ID FROM Opportunity WHERE Name = 'YouCanTest2' LIMIT 1];
Pricebook2 pbYouCan = [SELECT Id FROM Pricebook2 WHERE Name = 'YouCan' LIMIT 1];
//Verify Price book
System.assertEquals(oppYouCanPriceBook.id, pbYouCan.id);
The System.assertEquals call is failing as the ids do not match. I have used the sForce connector to validate that when I manually add opportunities, the pricebook fields are matching up.
And thank you for the advice on the custom object, but it is a little out of my wheelhouse right now to go any farther. At this point I just want to get this trigger written and deployed so I can be done. An APEX coding class is definitely in my future to learn how to do this correctly.
Hi TeraMc:
You're going in the right direction! Try changing your assert:
John-
You have just made my list of favorite people. That took care of it and I was able to clean all my code up, test and deploy it.
Thank you so much for your help.
Tera
awesome, congrats!