Monthly Archives: June 2014

Object access from SOQL results

I was reading Dan Appleman’s 2nd edition on Advanced Apex Programming and looking at the code samples. In one sample, Dan showed me something I didn’t know you could do in Apex – something that had eluded me in 6 years – and so basic. I was so chagrined but, with shame, comes resolution to help others avoid the same fate.

Let’s look a typical SOQL on Opportunity that references both parent and child objects:

    Opportunity o = [select account.id, account.name,
                            id, name,
                            (select id, quantity
                              from OpportunityLineItems)
                      from Opportunity where id = :someId];

Well, we know we can reference the child objects easily with:

   List<OpportunityLineItem> oliList = o.opportunityLineItems;

But here is what I did not know – you can also reference the Account object as well

   Account a = o.account;  //sheesh

This is handy if you need to update the lookup object. Thus you avoid unnecessary SOQL to fetch it.

Batchable class – test data – callouts: How to test?

The short hand title for this post might also be System.CalloutException: You have uncommitted work pending. Please commit or rollback before calling out

Here’s the problem I recently faced:

  1. Batchable class that does callouts in the start() method.
  2. Testmethod needs to setup some test data before the batchable class executes

As you probably know if you are reading this ..

  • Testmethods can’t do callouts, you have to mock out the callout response using Test.setMock(..)
  • Async tasks like Database.executeBatch() or @future methods don’t execute in a test context until the Test.stopTest() method.

Naively, I constructed this basic testmethod:

insert new Account(name = '00test'); // create test data here
Test.startTest();
  Test.setMock(HttpCalloutMock.class, myMultiMockObject); // setup of multiMockObject omitted for clarity
  MyBatchableClass bClass = new MyBatchableClass();
  Database.executeBatch(bClass,2);  // scope is 2
Test.stopTest();  // SFDC executes the async task here
System.assert(...) //verify results

When you execute this test, you get System.CalloutException: You have uncommitted work pending. Please commit or rollback before calling out

I then tried to setup the test data in a different context like this:

System.runAs(someUserOtherThanRunningUser) {
   insert new Account(name = '00test'); // create test data here
}
Test.startTest();
  Test.setMock(HttpCalloutMock.class, myMultiMockObject); // setup of multiMockObject omitted for clarity
  MyBatchableClass bClass = new MyBatchableClass();
  Database.executeBatch(bClass,2);  // scope is 2
Test.stopTest();  // SFDC executes the async task here
System.assert(...) //verify results

When you execute this test, you get System.CalloutException: You have uncommitted work pending. Please commit or rollback before calling out

Aargh. SFDC doc as of V30 wasn’t much help here. Much Googling ensued. Thanks to tomlogic for the solution essence.

Here is what you have to do –avoid the use of Database.executeBatch() in the testmethod if one of the batchable class’s start(), execute() or finish() methods does a callout. Thus, you have to invoke the start(), execute(), finish() methods explicitly to verify your batchable class logic and get code coverage.

insert new Account(name = '00test'); // create test data here
Test.startTest();
  Test.setMock(HttpCalloutMock.class, myMultiMockObject); // setup of multiMockObject omitted for clarity
  Database.BatchableContext 	bc;
  MyBatchableClass bClass = new MyBatchableClass();
  
  // we execute the start() and prepare results for execute()
  // in my use case, start() does the callout; 
  // thus the testmethod mocks the results of the callout (assumed here to be accounts)
  // setup of custom Iterable and Iterator not shown
  MyIterable  itrbl = (MyIterable)bclass.start();  //start() returns an iterable, in my case, a custom iterable. Note the casting
  MyIterator  itrator = (MyIterator) itrbl.iterator();  // continue simulation of start() by constructing the iterator
  List<Account> aScopeList = new List<Account> ();      // create scope for execute() by iterating against the result of the mocked callout
  while (itrator.hasNext()) {
     aScopeList.add(itrator.next());

  // Now invoke execute() w/ Account list built via mocked callout invoked by start()
  bClass.execute(bc,aScopeList);

  // Finally, invoke finish()
  bClass.finish(bc);
Test.stopTest();  // SFDC executes the async task here
System.assert(...) //verify results

To recap, this approach accomplishes the following:

  1. It will test code coverage as start(), execute(), and finish() are all invoked as if SFDC were invoking them by Database.executeBatch().
  2. It avoids the System.CalloutException: You have uncommitted work pending. Please commit or rollback before calling out
  3. It allows you to test against the returned data from the mocked callout(s) thus isolating your testmethod to predictable responses.
  4. It tests your custom iterator, if you are using one
  5. It only requires a bit more setup to individually invoke start(), execute(), and finish() plus manual construction of what execute() would get if database.executeBatch() were called

Conga Composer – Excel – Array Formulas – aargh

We’re a steady user of Conga Composer to generate Excel reports for our users where we can’t get SFDC to format a report the way the users want. This week, an interesting problem arose.

Business problem
Display a count of distinct values in a set of rows matching some condition; some rows may have null values

Background
Without getting into too much detail, we needed to count unique accounts for various conditions in the Conga-generated, merged Excel template. I couldn’t coerce the well-known ‘Power of One’ solution to work for us in the actual SFDC report that serves as the Conga source data so the decision was made to do the counting in Excel using new cell ranges below the generated Conga output.

How do you count distinct values in Excel?
If you go to the Microsoft Excel Help, you get this page.

For example, one method is to use this excel formula: =SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))

Now, if you coded up this formula in the Conga template, you couldn’t directly use the cell value B10 because Conga will generate a variable number of rows based on your source data. So, you would code this in Excel as:
=SUM(IF(FREQUENCY(MATCH(B2:B2,B2:B2,0),MATCH(B2:B2,B2:B2,0))>0,1)) and rely on Conga+Excel to expand the outer range of each cell range expression to be B13 or B24 or B65 or whatever, depending on how many rows of generated data (where column B represents Excel value to be counted and rows 2…n are what Conga inserts into the merged template. I’m presuming you understand Conga merge templates.

Unfortunately, this does not work as the formula =SUM(IF(FREQUENCY(MATCH(B2:B2,B2:B2,0),MATCH(B2:B2,B2:B2,0))>0,1)) has to be entered into Excel as an array formula and Conga won’t expand/Excel array formulas by adjusting the outer range and B2:B2 expression

So, now the problem is how to count distinct values without using array formulas.

The solution that worked for us (with limitations)
Some Google searching yielded this count distinct formula that does not rely on Excel array formulas:
=SUMPRODUCT(1/COUNTIF(List1, List1))

Geez, how does this work? I’m used to the second argument of COUNTIF being some expression that is tested against the elements of the first argument but here we are comparing the list to itself. The MSFT documentation of COUNTIF doesn’t have such an example.

Well, I turned to the handy Excel formula expression evaluator in my Windows Excel 2010. Take a look at the picture below (click to see full size so you can read it):
Count distinct formula evaluation

As you can see from the picture and successive evaluations, the COUNTIF(list1,list1) looks at each element in the list and sees how many times it appears in itself, returning an array of the same size as List1. Then the reciprocal is taken of each resulting array element.

So, if a value X appears twice in the range, the resulting array will be {2,..,2,…} and thus the reciprocal array is {.5,..,.5,…}. If also a value Y appears in the range three times, the resulting array will be {2,3,3,…2,…}. Positions are of course arbitrary as there is no assumption made about sorting. And the reciprocal array would be {.5,.333,.333,…5,…}

Finally, SUMPRODUCT, because it has only one array, doesn’t multiply anything but does add up the values in the reciprocal array. Adding .5 twice is 1; Adding .33 three times is 1. Summing 1 and 1 is 2 – two unique elements

As you can see from the picture, the result ‘4’ is the number of unique Account Names. pretty cool and no array formula required.

But what happens if you have nulls in the range of Account Names?
The expression will return #DIV/0. Sigh.

The solution is to recognize that if the comparison array in COUNTIF is guaranteed to have a non-null value, then the counts will work. Look at this picture:

Count distinct with null formula evaluation

Note the concatenation operator ‘&’. This appends a zero length string to the second list in the COUNTIF. Thus your final result will include a count of distinct Account names plus 1 for the null cells. If you are guaranteed there will be null cells (and we were due to Conga subtotal lines inserted), you just subtract one from the final result of the =SUMPRODUCT(…) formula.