3

Dear fellow SalesForce StackExchange,

I got the following SOQL query working in Workbench.

(Note: If any IDs look invalid, it's because I did some typing-over them to anonymize my code, and I may have made a typo.)

SELECT ContactId, count_distinct(OpportunityId)
FROM OpportunityContactRole
WHERE IsPrimary=true
AND ContactID='004670000073xcOWYR'
AND OpportunityId in (
    SELECT Id
    FROM Opportunity
    WHERE RecordTypeId='087C0000000KIVR'
)
GROUP BY ContactId

That is, the first column returned displays "004670000073xcOWYR" and the second column displays an integer showing how many "Admissions"-typed Opportunity records the Contact "004670000073xcOWYR" is a Primary Contact Role on.

I added a read-only integer field on Contact called "# of Admissions Opportunities" or "Adm_Opp_Count__c." (I work in higher ed, and ultimately this field will be used to drive a Formula Field that determines whether "Admissions" should be in the master list of "departments working with this Contact.")

Whenever an Opportunity is INSERTED/UPDATED/DELETED, I'd like to make sure that the "Adm_Opp_Count__c" field on any Contacts that exist as that Opportunity's "Primary Contact Role" (either in "old" or "new" versions of the Opportunity object being modified) gets recalculated & repopulated.

I don't yet want to ruin the fun of trying to write code, so at the moment, I'm only asking for help writing the test case. (So far, I've found it useful to write Test Case, Class, Trigger in that order.)

This code, which I borrowed from Jamurai at this site, is erroring out on me:

@isTest
private class TestCountAdmOppsForContact {
  static testMethod void test() {
    Account a = new account(Name='Fake Account');
    insert a;
    Contact c = new Contact(AccountId=a.id,LastName='Daikoku',Adm_Opp_Count__c=0); /* Set count to whatever I intend # to be in this test. */
    insert c;
    Opportunity o1 = new Opportunity(AccountId=a.id,RecordTypeId='087C0000000KIVR',Name='Graduate Admissions 1',CloseDate=System.today(),StageName='Lead');
    insert o1;
    OpportunityContactRole ocr1 = new OpportunityContactRole(opportunityid=o1.id,contactid=c.id,IsPrimary=true);
    insert ocr1;
    Test.startTest();
    delete o1;
    Test.stopTest();
    /* So far so good, up to here.  Then errors.  Need to quit & work on other work now. -3/4/15 10:25AM */
    gaoppcnt = [SELECT ContactId, count_distinct(OpportunityId) FROM OpportunityContactRole WHERE IsPrimary=true AND ContactID=:c.id AND OpportunityId in (SELECT Id FROM Opportunity WHERE RecordTypeId='087C0000000KIVR') GROUP BY ContactId];
    system.assertEquals(gaoppcnt.Unknown_Field__1,c.Adm_Opp_Count__c,'Adm Opportunity Count did not populate correctly');
  }
}

I know it's not a complete test, but I'm trying to start small to make sure I understand why I'm writing what I'm writing and slay one error at a time.

The error I get is:

Error: Compile Error: Variable does not exist: gaoppcnt at line 16 column 5

I can tell that I obviously need to declare gaoppcnt before using it (Jamurai's code did some re-using of an Opportunity-typed "o" variable that I don't really understand, and doesn't quite seem to make sense in my case), but I can't seem to come up with anything that doesn't give me one of these two errors:

Error: Compile Error: Illegal assignment from List<AggregateResult> to Integer at line 16 column 5

or

Error: Compile Error: Initial term of field expression must be a concrete SObject: List<AggregateResult> at line 17 column 25

Could someone help me fix up my Test Class so that I can do an assertion that checks whether the appropriate Contact.Adm_Opp_Count__c contains the same value that doing a SOQL query against that Contact returns?

Thank you!!

Edit: Here is the working test class - thanks for your help! (Edited the end and removed the Adm_Opp_Count__c=0 in the construction of Contact c since it was a composition crutch.)

@isTest
private class TestCountAdmOppsForContact {
  static testMethod void test() {
    Account a = new account(Name='Fake Account');
    insert a;
    Contact c = new Contact(AccountId=a.id,LastName='Daikoku');
    insert c;
    Opportunity o1 = new Opportunity(AccountId=a.id,RecordTypeId='087C0000000KIVR',Name='Graduate Admissions 1',CloseDate=System.today(),StageName='Lead');
    insert o1;
    OpportunityContactRole ocr1 = new OpportunityContactRole(opportunityid=o1.id,contactid=c.id,IsPrimary=true);
    insert ocr1;
    Test.startTest();
    delete o1;
    Test.stopTest();
    List<AggregateResult> expectedResult = [SELECT count_distinct(OpportunityId) gacount FROM OpportunityContactRole WHERE IsPrimary=true AND ContactID=:c.id AND OpportunityId in (SELECT Id FROM Opportunity WHERE RecordTypeId='087C0000000KIVR') GROUP BY ContactId];
    Integer aoppcnt;
    if(expectedResult.size() > 0) {
        aoppcnt = (Integer)expectedResult[0].get('expr0');
    } else {
        aoppcnt = 0;
    }
    system.assertEquals(aoppcnt,c.Adm_Opp_Count__c,'Adm Opportunity Count did not populate correctly');
  }
}
  • What happens if you change the line to: List<AggregateResult> goappcnt = [SELECT ContactId, count_distinct(OpportunityId) FROM OpportunityContactRole WHERE IsPrimary=true AND ContactID=:c.id AND OpportunityId in (SELECT Id FROM Opportunity WHERE RecordTypeId='087C0000000KIVR') GROUP BY ContactId]; – Eric Mar 6 '15 at 18:43
  • 1
    And line 17 will then need to be modified to work with aggregate results. You cannot use dot notation to get field values. You will need to use the get method (i.e. goappcnt.get('ContactId')) – Eric Mar 6 '15 at 18:45
  • Hey @Eric, adding List<AggregateResult> gives me the "Error: Compile Error: Initial term of field expression must be a concrete SObject: List<AggregateResult> at line 17 column 25" error. Is this what you think using the get() method will fix? – k.. Mar 6 '15 at 22:39
  • The result is a LIST thus you need to get the index of the result you want either via a loop or directly accessing using the index. Once you extract the single result then you can use the get to obtain the specific field values – Eric Mar 7 '15 at 0:44
  • Thank you so much, @Eric. I followed up with an acquaintance at a developer training session, and he also added clarification that any SOQL query that includes an aggregation must come back as an AggregateResult (if it only returns one row) or as a List<AggregateResult> (if it returns more than one.) Would you like to repost your original post as an Answer? I'll upvote it and accept it as the answer to my question. :-) – k.. Mar 13 '15 at 20:19
2

Comments posted as answer per request

change the line to:

List<AggregateResult> goappcnt = [SELECT ContactId, count_distinct(OpportunityId) FROM OpportunityContactRole WHERE IsPrimary=true AND ContactID=:c.id AND OpportunityId in (SELECT Id FROM Opportunity WHERE RecordTypeId='087C0000000KIVR') GROUP BY ContactId];

And line 17 will then need to be modified to work with aggregate results. You cannot use dot notation to get field values. You will need to use the get method (i.e. goappcnt.get('ContactId'))

The result is a LIST thus you need to get the index of the result you want either via a loop or directly accessing using the index. Once you extract the single result then you can use the get to obtain the specific field values

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.