Count large number of records (more than 50,000)

In a Salesforce org that has more than 50,000 records of an object, the following simple count() query will still hit the Salesforce governor limit: “System.LimitException: Too many query rows: 50001”

System.debug('total: ' + [select count() from Contact]);

Even though it seems that the count function does not need to traverse the whole Contact table, it still does, for specific reasons like checking sharing settings on records so that different users may get different number of records. So is there a way of retrieving the total number of records that are more than 50,000. There are a few, each of which has their cons and pros. Surprisingly, for any of these methods, such simple task would need more coding than we expected.

Method 1: Use Visualforce page with readOnly attribute set to true

Controller class:

public class StatsController {
    public Integer numberOfContacts {
        get {
            if (numberOfContacts == null) {
                numberOfContacts = [select count() from Contact];
            }
            return numberOfContacts;
        }
        private set;
    }
}

Visualforce page:

<apex:page controller="StatsController" readOnly="true">
    <p>Number of Contacts: {!numberOfContacts}</p>
</apex:page>

Then you will be able to see the result when you access this page in the browser. Note that the readOnly attribute has to be set to “true”, otherwise you will still get a Visualforce error complaining “Too many query rows: 50001 “. However, you won’t be able to create a simple controller and a page in Production orgs.

Method 2: Batchable class

public class ContactBatchable implements Database.Batchable<sObject>, Database.Stateful {
    Integer total = 0;

    public Database.QueryLocator start(Database.BatchableContext BC){
        return Database.getQueryLocator('select Id from Contact');
    }

    public void execute(
            Database.BatchableContext BC,
            List<sObject> scope){
        total += scope.size();
    }

    public void finish(Database.BatchableContext BC){
        System.debug('total: ' + total);
    }
}

And then execute the following statement in developer console.

Database.executeBatch(new ContactBatchable(), 2000);

This is going to be an asynchronous apex job so it can take time. When it is finished, you will be able to see a log with category “Batch Apex” at the top. The debug statement prints info into that log. You will also see a few logs with category “SerialBatchApexRangeChunkHandler” which is the log for each batch.

In the ContactBatchable the start method has to use the query locator, so that it can retrieve the records up to 50 million. (after 50 million? Ask Salesforce support). If you use an iterable in the batch class, the governor limit for the total number of records retrieved by SOQL queries (50,000 for the moment) is still enforced.

If the start method of the batch class returns a QueryLocator, the optional scope parameter of Database.executeBatch can have a maximum value of 2,000. If set to a higher value, Salesforce chunks the records returned by the QueryLocator into smaller batches of up to 2,000 records.

Still, this method cannot be applied to production orgs as you won’t be able to create an Apex class in Production org.

Method 3: Make a http request using the REST API

By far, this seems to be the simplest way of achieving this and it can be used in production orgs. In developer console, run the following statements:

HttpRequest req = new HttpRequest();
req.setEndpoint('https://'+URL.getSalesforceBaseUrl().getHost()+'/services/data/v20.0/query/?q=SELECT+Id+from+Contact');
req.setMethod('GET');

string autho = 'Bearer '+ userInfo.getsessionId();
req.setHeader('Authorization', autho);

Http http = new Http();
HTTPResponse res = http.send(req);
string response = res.getBody();
string total = response.substring(response.indexOf('totalSize":') + 11, response.indexOf(','));
system.debug('Total: '+ total);

You will need to add a remote site setting with URL set to your production org’s URL (say https://na11.salesforce.com) in the setup.

Custom sidebar: Pop up a modal dialog with content from static resource

Salesforce org’s sidebar can be customized by HTML so is often used for placing small components like a table or some hyperlinks to external web pages. Static resources can be used to host any type of files and easily referenced by Visualforce pages and apex classes. So this brings the idea of writing web widgets in pure HTML, CSS and JavaScript and popping them up from the org’s sidebar links. Here is an example and the HowTo:

We developed a utility tool in HTML5, CSS and JavaScript. This tool does not depend on any Salesforce technologies or resources. But we want to incorporate this tool in our Salesforce managed package so that any orgs installing our package can choose to use the tool. Since it is a utility tool, a good place to host it is sidebar component. Let’s call this tool AbcTool.

The AbcTool only has three source files: abc-tool.html, abc-tool.js, abc-tool.css. These files are  packaged in a zip file which is then uploaded as a Salesforce static resource called WebWidgets. The following code shows how to display the AbcTool in a dialog. The code is in the home page component’s custom HTML area. It uses JQueryUI to create a dialog and binds it to the onClick event of “Abc Tool” link.

<link rel="stylesheet" type="text/css"
	href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.10.1/themes/base/minified/jquery-ui.min.css">
<script type="text/javascript"
	src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
<script
	src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.10.3/jquery-ui.min.js"></script>
<table>
	<tbody>
		<tr>
			<td><a href="javascript:void(0)" id="abcTool">ABC
			Tool</a></td>
		</tr>
	</tbody>
</table>
<script type="text/javascript">
$j = jQuery.noConflict();
$j(document).ready(function() {
	var iframe_url = '/apex/cve__AbcTool';
	$j('#abcTool').click(function() {
		var j$modalDialog = $j('<div></div>')
			.html('<iframe id="iframeContentId" src="' + iframe_url + '" frameborder="0" height="100%" width="100%" marginheight="0" marginwidth="0" scrolling="no"/>')
			.dialog({
				autoOpen: false,
				title: 'ABC Tool',
				resizable: false,
				width: 550,
				height: 450,
				autoResize: true,
				modal: true,
				draggable: false});
		j$modalDialog.dialog('open');
	});
});
</script>

The actual content of the dialog is in Visualforce page AbcTool.page as you can see it is referenced by “/apex/cve__AbcTool”. This is the Visualforce page that bridges the sidebar component and static resource which is a zip file. It just redirects the request to the HTML page in the static resource. The following is the AbcTool page:

<apex:page showHeader="false"
    sidebar="false"
    standardStylesheets="false"
    action="{!URLFOR($Resource.WebWidgets, '/abc-tool.html')}" />

Gotcha: convertTimezone() must be used in SOQL Date functions dealing with Datetime

SOQL Date functions are pretty useful for grouping or filtering data by date fields. With a proper Date function used in the SOQL, the code can potentially limit the query result records a lot. e.g. Query all Tasks that are created today:

List<Task> = [
        select Id, WhatId, Subject, CreatedDate
        from Task
        where DAY_ONLY(CreatedDate) = :Date.today()];
The above code looks neat enough although the function DAY_ONLY is not that obviously named. The documentation states: "Returns a date representing the day portion of a dateTime field." so it should be safe enough. I used it in a few places and it worked very well. However recently I got a failing unit test while I was creating a managed package. The unit test was testing the logic that uses the above code. The test only fails when it runs after 5pm GMT-7 and before 12am GMT-7. The local time zone of the org is GMT-7.

I started debugging the issue in that precious 7-hour window period. I created a sample Task after 5pm GMT-7 and ran the above code immediately and it returned no records! I changed “Date.today()” to “Date.today() + 1” in the where clause, re-ran the code and it returned the Task I just created. What? Is this saying “Get me all Tasks created tomorrow”? Obviously not, the only explanation on this is that the DAY_ONLY() function takes Datetime parameter as GMT time. Any time between 5pm GMT-7 and 12am GMT-7 is already tomorrow’s time GMT.

All of a sudden I realized that all those Date functions like CALENDAR_YEAR, DAY_IN_MONTH etc. could be useless as they are all having the same timezone issue. That cannot be right. I got back to the documentation and found it has this statement:

“SOQL queries in a client application return dateTime field values as Coordinated Universal Time (UTC) values. To convert dateTime field values to your default time zone, see Converting Time Zones in Date Functions.”

On the “Converting Time Zones in Date Functions” web page, it says “You can use convertTimezone() in a date function to convert dateTime fields to the user’s time zone.”. What do you mean by “You CAN…”? In an org with a different timezone other than GMT, you HAVE to use this method to make it working! This is obviously a compromised solution of fixing the original timezone issue in those Date functions.

Generally speaking, the timezone issue is introduced because in Apex, Datetime and Date are not clearly differentiated and Salesforce badly handles these two types. See some other Datetime issues in this blog post: Danger: Date value can be assigned to a Datetime variable.

Anyway, the fix of the above code is:

List<Task> = [
        select Id, WhatId, Subject, CreatedDate
        from Task
        where DAY_ONLY(convertTimezone(CreatedDate)) = :Date.today()];

	

Apex controller class “without sharing” in practice

A general practice of defining a controller is to prepend “with sharing” key word to “class”. This is to enforce the sharing rules that apply to the current user as otherwise sharing rules aren’t taken into account during code execution. Recently in a project I came across a requirement in which “without sharing” plays a role.

We have a custom object called “Payment” and a Approval Process is defined on this object. The requirement is that system should allow any users other than the user who submitted Payments for approval to bulk approve or reject Payment approval requests. Originally we focused on implementation of bulk approve/reject logic. We created a custom controller and a Visualfore page to did all it needs. It felt so good that we could programmatically approve/reject approval requests in a bulk in Apex code. But we got a bug from the customer saying “It only allows the manager of the users who submitted Payments for approval to approve/reject the Payments”. When other users are approving/rejecting Payments, they get the following error:

Process failed. First exception on row 0; first error: INSUFFICIENT_ACCESS_ON_CROSS_REFERENCE_ENTITY,  insufficient access rights on cross-reference id: []

The debug log shows that this error is thrown when Approval.process() method is invoked. This actually makes sense as the controller class has “with sharing” declared. Normally if a user submitted an object for approval, only the user that is assigned to this approval request (usually the manger of the submitting user) has the access rights to corresponding Approval Process work items. Other users won’t even be able to see the actions links on the form. Since the approval requests are programmatically processed via a page and a “with sharing” controller, a random person will be able to run the code but does surely not have the access rights to Approval Process work items. This is proved by the Salesforce documentation on Using the “with sharing” or “without sharing” Keywords. Related statement:

Enforcing the current user’s sharing rules can impact:

  • SOQL and SOSL queries. A query may return fewer rows than it would operating in system context.
  • DML operations. An operation may fail because the current user doesn’t have the correct permissions. For example, if the user specifies a foreign key value that exists in the organization, but which the current user does not have access to.

So I got back to the controller, changed the “with” to “without” and it fixed the problem. However, all related Profile based security rules should be programmatically implemented. The profile setting on access to the corresponding Visualforce page can be used to do a basic protection on access to this “bulk approve/reject” functionality.

Visualforce expression on null equality comparisons

It is so natural these days to use the following Visualforce code to control the displaying of something wrapped in a pageBlockSectionItem.

<apex:pageBlockSectionItem rendered="{! clazz != null}">
    <apex:outputLabel value="Hellow World!">
</apex:pageBlockSectionItem>

Even though the syntax {! clazz != null} looks strange, it is easy to read once getting used to the convention that first exclamation point in the curly brackets means the expression starts from there. Clearly, what the above expression tries to do is to only render the “Hello World!” text when “clazz” property in the related controller is not null. However, this does not work correctly if the Visualforce page is at version earlier than API 26 (Winter ’13 Release). It seems to always return true (at least it always renders the text) in a page with API version earlier than 26. See Winter ’13 release notes Page 202 for more details. I recently spent 2 hours or so debugging a defect that used such expression and only got to resolve it by changing the version number of the Visualforce page. This is the type of defect that cannot be covered by unit testing on controllers only. Manual testing or automated testing like Selenium is necessary.

By the way, the following functions used in Visualforce page to evaluate null property works correctly all the time:

<apex:pageBlockSectionItem rendered="{! NOT(ISNULL(clazz))}">
    <apex:outputLabel value="Hellow World!">
</apex:pageBlockSectionItem>

Customization: Conditionally display a message on the standard detail page

Ever wonder dynamically displaying a warning or information message on an object’s detail page without affecting whatever layout is used? Here is a simple requirement: you have an object called MyObject. If you go to view any MyObject record that is created today, you will see an information message “Hello world!” at top of the detail page block. If the record you view is not created today, you will not see the message. The layout of the page should remain not affected including fields and related lists previously configured by drag-and-drop.

JavaScript is an obvious solution as the requirement sounds it only needs manipulations on the browser side. But firstly, how do you inject JavaScript on a standard detail page of an object? Secondly, what if the Created Date of the record is not shown on the page? Now here is a solution: Use controller extension and <apex:detail> Visualforce component. See the following sample code for MyObject controller extension:

public with sharing class MyObjectController {
    private MyObject__c obj;

    public MyObjectController(ApexPages.StandardController controller) {
        this.obj = (MyObject__c) controller.getRecord();
    }

    // Add an INFO message if the record to view is not created today.
    public PageReference checkOnLoad() {
        MyObject__c record = [
                select Id, CreatedDate
                from MyObject__c
                where Id = :this.obj.Id];

        // Not accurate. Just to illustrate the logic
        if (record.CreatedDate != Date.today()) {
            ApexPages.addMessage(new ApexPages.Message(
                    ApexPages.severity.INFO, 'Hello World!'));
        }
        return null;
    }
}

Note that in the checkOnLoad method of the controller, it queries the record. This is necessary as this method is going to be called before the standard View action of the controller is invoked, which means obj.CreatedDate value is not retrieved so far in this method. The method is specified in the action property of the <apex:page> component. See the following Visualforce page:

<apex:page standardController="MyObject__c" extensions="MyObjectController"
        action="{!checkOnLoad}">
    <apex:pageMessages />
    <apex:detail subject="{!MyObject__c.Id}"/>
</apex:page>

The Visualforce component <apex:detail> presents exactly the pre-defined layout used for the record. You can add optional attributes (relatedList, relatedListHover, inlineEdit, etc.) on the tag to specify if the detail page should include related list, related list hover or support inline editing, etc. The <apex:detail> is so neat and so easy to use that a four line code Visalforce page does exactly what you need to do. Moreover, you could now add any powerful JavaScript to this Visualforce page to do all sorts of client side UI changes!

The final step of making it work is to replace the standard View page with the above Visualforce page. Follow these steps: Setup -> Create -> Objects -> MyObject -> Standard Buttons and Links -> Click “Edit” link beside the View -> Override With -> Pick the name of the above Visualforce page -> Save.

Danger: Date value can be assigned to a Datetime variable

Surprisingly in Apex it allows a Date value to be assigned to a Datetime variable without explicit conversion. See an example as follows:

Date d = Date.today();
Datetime dt = d;

Firstly, this would cause a timezone problem. Date d is created as today’s date. The debug log shows that Datetime dt is created based on the timezone of the organization the code is running. The time part of Date d is 00:00:00 GMT. So if the timezone of the organization is GMT-5, Datetime dt will have a 19:00:00 yesterday local time. When dt.day() is called, it returns yesterday’s day of the month. The correct way of converting Date to Datetime avoiding the timezone issue is to use the following statement:

Datetime dt = Datetime.newInstance(d.year(), d.month(), d.day());

Secondly, the fact that “a Date value can be assigned to a Datetime variable” in Apex makes an error to be noticed only at our latest managed package installation time. We had a global method in a class in our managed package (prefix: cve): cve.BusinessHoursUtil.addDays(Datetime, Integer). However a few code references in another managed package (prefix: cvestp) call this method passing in a date value and we have not noticed this for long time as it can compile. We recently added a overloaded method cve.BusinessHoursUtil.addDays(Date, Integer). Since this is the accurate method, the referencing code in cvestp should be now looking for the new method. However the cve version number for the referencing classes are at a much earlier version which does not have the new method. It still passes compilation and we can create our cvestp managed package. It gives out the following “method is not visible” error when the customer installs our cvestp managed package:

Dependent class is invalid and needs recompilation: (cvestp)
cvestp.IncompleteClaimTasks: line 77, column 28: Package Visibility:
Method is not visible: cve.BusinessHoursUtil.addDays(Date, Integer)