Updating Products on Opportunity Close Date Change

I was browsing the Dreamforce App last week and came across a question in the ChallengeUs group from Maria Belli (@msbelli on twitter) :

Dreamforce Question

A few responses had already indicated she would need a trigger and I offered to assist if necessary. I worked with Maria and whipped up a trigger that suited her needs. Since this is functionality that could be applicable to any org I felt it would be worth sharing with my (small) audience.

One item to pay attention to that this trigger only works for certain record types.  I am leaving this in as it is fairly easy to remove the code for this and also provides a good case to learn from.

In this trigger we are looking to set the ServiceDate on the Opportunty Products to the CloseDate of the Opportunity for certain record types when the CloseDate changes.

The first thing we do is get the record type Ids and add them to a Set. Next we loop through the opportunities in trigger.new and add any with a match to the record types in question to another Set. We are also checking to see whether the CloseDate has changed before adding the Opportunity to the Set.

Once you have establish the opportunities that match our record types you can query for those opportunities and their associated Products, which will be a list. You can then loop through the Opportunities and process each list of Products tied to it, setting the Product Service Date to the Opportunity Close Date.

The Trigger:

trigger Opportunity_CloseDate_Update on Opportunity (after update) {
    
    //get the Ids of our two record types and add them to a set
    Set<Id> recTypeIds = new Set<Id>();
    for(RecordType r : [select Id, Name from RecordType where (Name='StandAlone Opportunity' OR Name='Subsidiary Opportunity') AND SobjectType='Opportunity']){
        recTypeIds.add(r.id);
    }
    
    //check the opprtunites in the trigger to see if the Close Date change AND any are of the RecordTypes above. 
    //if so add them to a Set
    Set<Id> StandAloneAndSubsidiaryOppIds = new Set<Id>();
    for(Integer i=0;i<trigger.new.size();i++){
        if(trigger.new[i].CloseDate <> trigger.old[i].CloseDate && recTypeIds.contains(trigger.new[i].RecordTypeId)){
            StandAloneAndSubsidiaryOppIds.add(Trigger.new[i].id);
        }
    }
    
    //Process any opps that have changed
    if(StandAloneAndSubsidiaryOppIds.size() > 0){
	    //get all of the opportunity Line items tied to opportunities in this trigger
	    List<Opportunity> oliList = [select Id, Name, CloseDate, (select Id, ServiceDate, OpportunityId from OpportunityLineItems) from Opportunity where Id IN :StandAloneAndSubsidiaryOppIds];
		
		//create list to hold opportunity line items that need updating.
	    List<OpportunityLineItem> oliUpdateList = new List<OpportunityLineItem>();
	    
	    //loop through opportunites and update all of the opportunity line items. add line items to udpat list.
	    for(Opportunity o : oliList){
	        for(OpportunityLineItem oli : o.OpportunityLineItems){
	            oli.ServiceDate = o.CloseDate;
	            oliUpdateList.add(oli);
	        }
	    }
	    
	    //update the line items
	    if(!oliUpdateList.isEmpty()){
	    	update oliUpdateList;
	    }
    }
}

The Test Class:

@isTest
private class Test_Opportunity_CloseDate_Update {

    static testMethod void myUnitTest() {
        //create a dummy account
        Account a = new Account(Name='Test Account');
        insert a;
                
        //Get the Standard PriceBook and make sure it is active
        PriceBook2 pb = [select Id from PriceBook2 where IsStandard=True];
        pb.IsActive=true;
        update pb;
        
        //create two products 
        List<Product2> prodList = new List<Product2>();
        prodList.add(new Product2(Name='Prod1'));
        prodList.add(new Product2(Name='Prod1'));
        insert prodList;
        
        //Create pricebook entries for the products
        List<PricebookEntry> pbEntryList = new List<PricebookEntry>();
        pbEntryList.add(new PricebookEntry(Pricebook2Id=pb.Id, Product2Id=prodList[0].id, UnitPrice=100.00, IsActive=true));
        pbEntryList.add(new PricebookEntry(Pricebook2Id=pb.Id, Product2Id=prodList[1].id, UnitPrice=200.00, IsActive=true));
        insert pbEntryList;
        
        //Get the two record types we care about plus 1 other one.
        List<RecordType> recTypeList = [select Id, Name from RecordType where (Name='StandAlone Opportunity' OR Name='Subsidiary Opportunity' OR Name='Primary Opportunity' OR Name='Secondary Opportunity') AND SobjectType='Opportunity'];
        
        //Create 200 new opportunities.
        List<Opportunity> newOppList = new List<Opportunity>();
        //  ***MARIA*** be sure to check StageName... set to something you use.
        for(Integer i=1; i<=200; i++){
        	Opportunity o = new Opportunity(Name='Test Oppty ' + i, CloseDate=Date.Today()+1, AccountId=a.id, StageName='asdfasdf',Probability=0.5);
        	//We grabbed four record types above, so set 1/4 of opportunities to each record type. 
        	o.RecordTypeId=recTypeList[Math.mod(i,4)].Id;
        	newOppList.add(o);
        }
        
        //insert our new opportunties
        insert newOppList;
        
        //Now add both products to all of the opportunities
        List<OpportunityLineItem> oliList = new List<OpportunityLineItem>();
        for(Opportunity o : newOppList){
        	oliList.add(new OpportunityLineItem(OpportunityId=o.id, PriceBookEntryId=pbEntryList[0].id,Quantity=1, ServiceDate=Date.Today()+1,UnitPrice=100.00));
        	oliList.add(new OpportunityLineItem(OpportunityId=o.id, PriceBookEntryId=pbEntryList[1].id,Quantity=1, ServiceDate=Date.Today()+1,UnitPrice=200.00));
        }
        
        //insert the opportunity line items
        insert oliList;
        
        //Now that everything has been set up, we can begin the actual test
        Test.startTest();
        //change the close date for all 198 opportunities
        for(Opportunity o : newOppList){
        	o.CloseDate = Date.Today()+7;
        }
        
        //Update the opportuntiies
        update newOppList;
                 
        //get all of the products tied to the opportunites, there should be 396 total
        oliList.clear();
        oliList = [select Id, ServiceDate from OpportunityLineItem where OpportunityId IN :newOppList];
        System.assertEquals(400,oliList.size());
        
        //Now check that the number of Opportunity Line items with service date of Today+7 is 200
        //since half the opportunities should have fired product service date updates
        Integer plus7Counter = 0;
        Integer plus1Counter = 0;
        for(OpportunityLineItem oli : oliList){
        	if(oli.ServiceDate == Date.Today()+7){
        		//increase the count by 1
        		plus7Counter++;        		
        	}
        	if(oli.ServiceDate == Date.Today()+1){
        		plus1Counter++;
        	}
        }
        //make sure the counts are as expected
        System.AssertEquals(200,plus7Counter);
        System.AssertEquals(200,plus1Counter);
        Test.stopTest();
    }
}

Email from Apex : A Breakdown of my CloudSpokes Chatter to Picasa Entry

I thought someone might find it useful to see how I approached my winning CloudSpokes Email Upload Chatter Pictures to Picasa entry a while back, specifically around how to send an email in Apex. I am posting this with permission from the CloudSpokes organizers  and this has been cross posted on their blog which you can find here. I have been asked to refrain from giving the full code so you won’t get the whole solution here, but it is available on this CloudSpokes GitHub repo.

So the idea was to email a photo to a Picasa web album leveraging the “Upload photos by email” functionality whenever a keyword was found in the post. I won’t go into this as Picasa appears to be getting engulfed by Google+ and I don’t know how the setup will work going forward. Regardless, I leveraged two triggers, the first was on the FeedItem object and the second was on the FeedComment object. In each trigger I essentially looked for a keyword in the chatter post/comment, defined during setup, and if found, grabbed the picture from the post and emailed it to Picasa.

Jeff Douglas did a wonderful job of explaining this all, so I’ll hand this over to him:

The important part that I want to share is how the email is sent. This is all handled inside a class which is called from either of the triggers. This is the definition of the SendEmail method. Not all of the parameters are needed to send an email, though they were necessary for this particular application.

public void SendEmail(FeedItem f,
                      String description,
                      String emailAddr,
                      String keyword,
                      Boolean AllowAlbumLoad){
    ....
}

First, you want to set up the basic message, setting the to address (which was passed in the emailAddr parameter), replyTo, subject (which comes from the post’s title), BccSender(sends email to the person sending the email) and PlainTextBody (which isn’t really used here).

    Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
    //Set email address
    String[] toAddresses = new String[] {emailAddr};
    mail.setToAddresses(toAddresses);
    mail.setReplyTo('noreply@salesforce.com'); //the reply address doesn't matter
    mail.setSubject(f.Title);
    mail.setBccSender(false);  //we don't want to Bcc ourselves on this
    mail.setPlainTextBody('Body Text - This will not be utilized, picasa will not use it.');

Once the basic email is set up, you need to create the attachment, which in this case was pulled from the feedItem passed in.

    Messaging.EmailFileAttachment efa = new Messaging.EmailFileAttachment(); //create the attachment
    efa.setBody(f.ContentData); //set the body of the attachment to the Content Data
    efa.setFileName(f.ContentFileName); //set the file name appropriately
    mail.setFileAttachments(new Messaging.EmailFileAttachment[] {efa});  //attach to the email

At this point you have an email and just need to send it. You do this by leveraging the Messaging class.

    Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail }); //send the email

Refactoring and Improving…Fixing Roll-Up Summaries

After I posted this: http://wp.me/p1xleL-1w, Daniel Llewellyn (@Kenji776) commented that this could also be done using sub-queries. I recognized immediately that I he was right and my code was not as efficient as it could be. I finally found some time to refactor and what resulted is simpler and more efficient code.

The biggest change to the previous post is selecting the opportunities in a sub-select while querying the accounts:

select Id, Name, Total_Number_of_Opportunities__c,
Num_of_Won_Opportunities__c, Num_of_Closed_Opportunities__c,
(select Id, IsWon, IsClosed from Opportunities)
from Account where Id IN :accountIdSet

The main idea to take away from this is that we are selecting the accounts and ALL of the associated opportunities in a single select statement rather than selecting accounts, then selecting Opportunities. Also beyond reducing the number of SOQL to just one, we also eliminate the need to create a map of Account Ids to lists of opportunities.

I also want to point out that in the sub-select query we are querying “from Opportunities” not  “from Opportunity”. By querying Opportunities we are telling Salesforce.com to pull from just the opportunities tied to each account (and to group them by each account). For custom objects you will need to determine the child relationship name and then query that. For example, if you have a lookup field from the “Part” object to the “Car” object the Child Relationship Name (found on the lookup field detail) might be “Parts”. This means if you wanted to select all carss and the associated parts your query might look like this:

List<Car> carList = [select Id, Name, (select Id, Name from Parts__r) from Car];
for(Car c : carList){
  for(Part p : c.Parts__r){
    ...do stuff here...
  }
}

While this doesn’t greatly increase the  speed of a single opportunity update , imagine doing a bulk update all of your opportunities where you have an average of 25 opportunities per account. Since we no longer create a mapping of every account to its opportunities we greatly reduce the number of script statements need to get the job done.

So here is the updated code to perform roll-up summaries (And yes, I know this can be done using roll-up summary fields, but since I posted this example already, I am sticking with it.):

trigger updateAccountOpportunityCounts on Opportunity (after insert, after update, after delete, after undelete) {

    /*Create a Set to hold Acct Ids of opps in triger
      and a List to hold accts which will be updated*/
    Set<Id> accountIdSet = new Set<Id>();
    List<Account> accountUpdateList = new List<Account>();

    /*Create ths set of Account IDs.
      If this is a delete trigger, pull them the trigger.old,
      otherwise from trigger.new*/
    if(Trigger.isDelete)
        for(Opportunity o : trigger.old)
            accountIdSet.add(o.AccountId);
    else
        for(Opportunity o : trigger.new)
            accountIdSet.add(o.AccountId);

    /*Query for all of the accounts assoicated with the
      opportunities in the trigger using the above created set of Ids*/
    List<Account> acctOpptyList = [select Id, Name, Total_Number_of_Opportunities__c,
    			Num_of_Won_Opportunities__c, Num_of_Closed_Opportunities__c,
                (select Id, IsWon, IsClosed from Opportunities)
                from Account where Id IN :accountIdSet];

	/*loop through all of the accounts in the map and get
	  the counts for total number of opps,
	  count of closed and count of won opps.*/
    for(Account a : acctOpptyList){
        //initialize our counters
        Integer wonOpps = 0;
        Integer closedOpps = 0;

        //loop through the associated opportunities and count up the opportunities
        for(Opportunity o : a.Opportunities){
            if(o.IsClosed){
            	closedOpps++;
            	/*can't be won without being closed, reduce script statements
            	  by checking this inside IsClosed check*/
            	if(o.IsWon) wonOpps++;
            }
        }

        //set the counts
        a.Total_Number_of_Opportunities__c = a.Opportunities.size();
        a.Num_of_Won_Opportunities__c = wonOpps;
        a.Num_of_Closed_Opportunities__c = closedOpps;

        //add the account to the update list
        accountUpdateList.add(a);
    }

    //update all of the accounts if the list isn't empty
	if(!accountUpdateList.IsEmpty()) update accountUpdateList;
}

Quickly Creating Sandbox Dummy Data

I am not fortunate enough to work with the Unlimited Edition of Salesforce.com and purchasing a Full Sandbox is a pricy proposition, so when I do a sandbox refresh all I get is an empty shell of our org with no data. Since I always find myself creating accounts and opportunities right away, I decided to build a tool to do it a bit more quickly. The data isn’t fancy, but it is just test data which give me a head start when testing other areas of functionality.

I wanted to make sure that this tool didn’t run on prod (that would not be good) so you will notice the getOnSandbox method which helps figure out whether the current environment is on a sandbox or not. Note, for those of you who have a sandbox on tapp*, when you create a visualforce page it appears to run on c.cs* so checking for the presence of “cs” in the host name should still work for you.

The other trick here is allowing us to deploy this to production. This tool is useless if it it gets wiped out after a refresh, so we want to deploy it to production. That is where Test.isRunningTest() comes in useful. Checking for that will allow us to create the data when we are running a test, allowing the proper code coverage to be attained.

So here is what I have put together. Remember that your environment might be different. I actually updated this code to work on a standard developer environment, but you may want to add other data that gets created; just use this as a base.

When you first visit the page there will be two buttons. Their functions are obvious. There is Create and Delete. The image below show what happens when the create button is first clicked. Notice that it is greyed out so we don’t clicking it twice accidentally.

Once the data has been created, Names and Ids are displayed.

And if you try to access this from a production environment, it won’t work.

Hopefully somebody finds this useful. [Update: be sure to check out the comments to see how Wes Nolte accomplished this in a very cool way.]

The Class

public with sharing class createSandboxDummyData {

    private List accountList = new List();
    private List opportunityList = new List();

    //indicates the number or records to create for EACH Account Record Type
    public Integer MAXRECORDS=5;

    //these bool's will be used to help render the page
    public Boolean dataAdd {get;private set;}
    public Boolean dataDelete {get;private set;}

    public createSandboxDummyData(){
        dataAdd = false;
        dataDelete = false;
    }

    //are we on a sandbox?
    public boolean getOnSandbox(){
        return System.URL.getSalesforceBaseURL().getHost().contains('cs');
    }

    //next two methods return the record lists
    public List getAccounts(){
        return accountList;
    }

    public List getOpportunities(){
        return opportunityList;
    }

    //Now time for the real work.
    public void createData(){
        //Make sure we are on a dev server or running a test.
        //We DO NOT want to create dummy data on a Prod Server
        if( getOnSandbox() || Test.isRunningTest()){
            //update the type of action
            dataAdd=true;
            dataDelete=false;

            //Let's Create some Accounts
            accountList = new List();
            for(Integer i=1;i                accountList.add( new Account(Name='Test Account xyz ' + i));
            }
            insert accountList;

            //Great, we have some accounts, how about some opportunities
            opportunityList = new List();

            //Use this counter to give unique names to opportunities
            Integer oppCounter=1;
            for(Account a : accountList){
                opportunityList.add(
                    new Opportunity(Name='Test Opportunity xyz '+oppCounter,
                        AccountId=a.id,
                        CloseDate=Date.Today().addDays(40+oppCounter),
                        StageName='Trial Period',
                        Probability=0.4
                   )
                );
                oppCounter++;
            }
            insert  opportunityList;
        }
    }

    //Just in case we don't want all that data that we created,
    //we can delete it
    public void deleteData(){

        if(getOnSandbox() || Test.isRunningTest()){
            //update the type of action
            dataDelete=true;
            dataAdd=false;
            //update the lists of accounts and opportunities.
            accountList = [select Id, Name from Account where name like '%Test Account xyz%'];
            opportunityList = [select Id, Name from Opportunity where name like '%Test Opportunity xyz%'];
            delete accountList;
            //we don't need to delete the opportunity list here because
            //all opps are deleted when the accounts are deleted

        }
    }
}

The Page (be sure to only give access to admins)

<apex:page sidebar="false" controller="createSandboxDummyData" tabStyle="Account">
    <apex:outputText rendered="{!NOT(onSandbox)}" ><br/>&nbsp;Whoa Tiger! This is a production environment. Nothing to see here.</apex:outputText> 

    <apex:outputPanel rendered="{!onSandbox}">
        <apex:form id="theForm">
            <apex:pageBlock >             
            
                <!-------------------------------------------------- Create and Delete buttons ------------------------------------------>
                <apex:actionStatus id="creatingStatus">
                    <apex:facet name="stop">
                        <apex:commandButton action="{!createData}" status="creatingStatus" value="Create Dummy Data" disabled="false" reRender="theForm"/>
                    </apex:facet>
                    <apex:facet name="start">
                        <apex:commandButton status="creatingStatus" value="Creating..." disabled="true"/>
                    </apex:facet>
                </apex:actionStatus> 
                
                <apex:actionStatus id="deletingStatus">
                    <apex:facet name="stop">
                        <apex:commandButton action="{!deleteData}" status="deletingStatus" value="Delete Dummy Data" disabled="false" reRender="theForm"/>
                    </apex:facet>
                    <apex:facet name="start">
                        <apex:commandButton status="creatingStatus" value="Deleting..." disabled="true"/>
                    </apex:facet>
                </apex:actionStatus>
                
                <!---------------------------------------- output for created or deleted accounts and opportunities -------------------------------->
                <apex:panelGrid columns="2" width="100%">                   
                    <apex:outputPanel >
                        <br/> <!-----This helps align the tables in the output-->
                        <apex:pageBlockSection title="{!IF(dataAdd,'Accounts Added','Accounts Deleted')}" rendered="{!dataAdd || dataDelete}">                                                         
                            <div width="100%">
                                <apex:pageBlockTable value="{!Accounts}" var="a" >                                
                                    <apex:column value="{!a.Name}" headerValue="Account Name"/>
                                    <apex:column value="{!a.Id}" headerValue="Account Id"/>                                
                                </apex:pageBlockTable>
                            </div>
                        </apex:pageBlockSection>                                                
                    </apex:outputPanel>
                    
                    <apex:outputPanel >
                        <apex:pageBlockSection title="{!IF(dataAdd,'Opportunities Added','Opportunities Deleted')}" rendered="{!dataAdd || dataDelete}">
                            <div width="100%">
                                <apex:pageBlockTable value="{!Opportunities}" var="o" >
                                    <apex:column value="{!o.Name}"/>
                                    <apex:column value="{!o.Id}"/>
                                </apex:pageBlockTable>
                            </div>
                        </apex:pageBlockSection>                                                
                    </apex:outputPanel>
                    
                </apex:panelGrid>
            </apex:pageBlock>                    
        </apex:form>
    </apex:outputPanel>
</apex:page>

The Test Class

@isTest
private class testCreateSandboxDummyData {

    static testMethod void myUnitTest() {
        DateTime startTime = DateTime.Now();

        //instantiate controller
        createSandboxDummyData csdd = new createSandboxDummyData();
        List aList = [select Id, Name from Account where name like '%Test Account xyz%' AND CreatedDate >= :startTime];
        System.assertEquals(0, aList.size());
        List oList = [select Id, Name from Opportunity where name like '%Test Opportunity xyz%'AND CreatedDate >= :startTime];
        System.assertEquals(0, oList.size());

        //test the create data button
        csdd.createData();
        aList = [select Id, Name from Account where name like '%Test Account xyz%' AND CreatedDate >= :startTime];
        System.assertEquals(csdd.MAXRECORDS , aList.size());
        oList = [select Id, Name from Opportunity where name like '%Test Opportunity xyz%'AND CreatedDate >= :startTime];
        System.assertEquals(csdd.MAXRECORDS , oList.size());

        //test the helper methods
        System.AssertEquals(csdd.MAXRECORDS,csdd.getAccounts().size());
        System.AssertEquals(csdd.MAXRECORDS,csdd.getOpportunities().size());

        //test the funtionality to determine if we are on a sandbox or not
        if(System.URL.getSalesforceBaseURL().getHost().subString(0,2).startsWith('cs'))
            System.assert(csdd.getOnSandBox());
        else
            System.assert(!csdd.getOnSandBox());

        //test the delete data button
        csdd.deleteData();
        aList = [select Id, Name from Account where name like '%Test Account xyz%' AND CreatedDate >= :startTime];
        System.assertEquals(0 , aList.size());
        oList = [select Id, Name from Opportunity where name like '%Test Opportunity xyz%'AND CreatedDate >= :startTime];
        System.assertEquals(0 , oList.size());
    }
}

Creating Rollup Summaries for Standard to Standard Objects

Update: @hammnick pointed out that you can in fact do this using roll-up summaries. I swear I checked this before hand and it wasn’t working. Regardless, I will keep this post up as I think it provides a useful learning tool. This would be more useful in a situation where you do not have a Master-Detail relationship and still want to gather counts. I am glad I didn’t spend too much time in this!

I was reading through @nikpanter‘s blog a little while ago and came across this post about learning Apex: http://www.xlerate.ca/nik/?p=165. In that post Nik mentioned writing a trigger to count the number of opportunities tied to an account as well as the number of won and number of closed opportunities. I reached out to Nik and confirmed that he hadn’t written this trigger and then proceeded to write it myself.

Since we are keeping track of opportunity counts on the account, we need to remember to update the account whenever an opportunity is inserted, updated, deleted or undeleted. We can calculate the stats after each of these actions are done so this will be an ‘After’ trigger for each of those events on the opportunity object.

We will need three custom fields on the Account to hold the values. I created the following fields:

  • Total # of Opportunities (Total_Number_of_Opportunities__c)
  • # of Closed Opportunities (Num_of_Closed_Opportunities__c)
  • # of Won Opportunities (Num_of_Won_Opportunities__c)
My approach was to first get all of the accounts tied to the opportunities in the trigger.  Once I had those, I could then query for ALL of the opportunities tied to each of those accounts and then count up each of our stats. I added the accounts to a map with a list of the opportunities so that we could check the size of each list tied to an opportunity in order to get the opportunity stats.
Since this is a trigger it will only take effect once an action is taken on an opportunity. You will want to perform an update on all of your opportunities to prompt the update of the account stats. You can use the data loader to export just the Id of all opportunities and then use that file to ‘update’ the opportunities, causing the trigger to fire and your accounts to be updated.
Trigger
trigger updateAccountOpportunityCounts on Opportunity (after insert, after update, after delete, after undelete) {

    ////////////////////////////////////////////////////
    //Set up Map, a Set and a List
    //Map will hold accounts and all opportunities tied to it
    //Set will hold Ids of accounts for the opportunities in the trigger
    //List to hold accounts which we will be updating
    ////////////////////////////////////////////////////
    Map<Account, List<Opportunity>> totalCountMap = new Map<Account, List<Opportunity>>();
    Set<Id> accountIdSet = new Set<Id>();
    List<Account> accountUpdateList = new List<Account>();

    ////////////////////////////////////////////////////
    //Create the set of Account IDs.
    //If this is a delete trigger, pull them the trigger.old, otherwise from trigger.new
    ////////////////////////////////////////////////////
    if(Trigger.isDelete)
        for(Opportunity o : trigger.old)
            accountIdSet.add(o.AccountId);
    else
        for(Opportunity o : trigger.new)
            accountIdSet.add(o.AccountId);

    //Query for all of the accounts assoicated with the opportunities in the trigger using the above created set of Ids
    Map<Id, Account> accountMap = new Map<Id, Account>([select Id, Name, Total_Number_of_Opportunities__c, Num_of_Won_Opportunities__c, Num_of_Closed_Opportunities__c  from Account where Id IN :accountIdSet]);

    ////////////////////////////////////////////////////
    //loop through each of the opportunities associated with the above accounts
    //add them to lists in the totalCountMap
    //**Note that in a delete trigger (since this is after delete), any deleted opportunities will NOT appear in the query
    ////////////////////////////////////////////////////
    for(Opportunity o: [select Id, AccountId, IsWon, IsClosed from Opportunity where AccountId IN :accountMap.keySet()]){

        //temporarily list to either get existing list from Map or insert new list to map
        List<Opportunity> tempList = new List<Opportunity>();

        //if this account already exists, get the list of opportunities so we can add this opportunity to it.
        if(totalCountMap.containsKey(accountMap.get(o.AccountId)))
            tempList = totalCountMap.get(accountMap.get(o.AccountId));

        //add the opportunity to the list (whether it is new or existing)
        tempList.add(o);

        //Add the Account and list of opportunites into the Map. This will replace a map entry if it already exists
        totalCountMap.put(accountMap.get(o.AccountId),tempList);
    }

    ////////////////////////////////////////////////////
    //loop through all of the accounts in the map and get the counts for total number of opps,
    //count of closed and count of won opps.
    ////////////////////////////////////////////////////
    for(Account a : totalCountMap.keySet()){
        //initialize our counters
        Integer wonOpps = 0;
        Integer closedOpps = 0;

        //loop through the associated opportunities and count up the opportunities
        for(Opportunity o : totalCountMap.get(a)){
            if(o.IsClosed){
                closedOpps++;
                if(o.IsWon) wonOpps++; //can't be won without being closed, reduce script statments by checking this here
            }
        }

        //set the counts
        a.Total_Number_of_Opportunities__c = totalCountMap.get(a).size();
        a.Num_of_Won_Opportunities__c = wonOpps;
        a.Num_of_Closed_Opportunities__c = closedOpps;

        //add the account to the list
        accountUpdateList.add(a);
    }

    //update all of the accounts if the list isn't empty
    if(!accountUpdateList.IsEmpty()) update accountUpdateList;
}
Of course we need to test the trigger as well. My test class creates 200 opportunities and adds them to an account. It then updates deletes and undeletes opportunities confirming the proper number of accounts each time:
Test Class
@isTest
private class testUpdateAccountOpportunityCounts {

    static testMethod void myUnitTest() {
        //Create an account
        Account a = new Account(Name='My Freakin Awesome Test Account');
        insert a;

        //create, say, 200 opportunites attached to said account
        List<Opportunity> oList = new List<Opportunity>();
        for(Integer i=1; i<201; i++){
          oList.add(new Opportunity(Name='Opportunity ' + i,
                        AccountId=a.id,
                        StageName='Qualification',
                        closeDate=Date.newInstance(2100,01,01)
                        )
         );
        }
        insert oList;

        //Make sure we actually have 200 opportunities and none of them are closed or closed won.
        a = [select Total_Number_of_Opportunities__c, Num_of_Won_Opportunities__c, Num_of_Closed_Opportunities__c from Account where Id = :a.id limit 1];
        System.assertEquals(200, a.Total_Number_of_Opportunities__c);
        System.assertEquals(0, a.Num_of_Won_Opportunities__c);
        System.assertEquals(0, a.Num_of_Closed_Opportunities__c);

        //Now let's close half of the opportunities, 50 of those won, other 50 lost
        for(Integer i=0; i<100; i++){
          if(i<50)
            oList[i].StageName='Closed Won';
          else
            oList[i].StageName='Closed Lost';
        }

        update oList;
        a = [select Total_Number_of_Opportunities__c, Num_of_Won_Opportunities__c, Num_of_Closed_Opportunities__c from Account where Id = :a.id limit 1];
        System.assertEquals(200, a.Total_Number_of_Opportunities__c);
        System.assertEquals(50, a.Num_of_Won_Opportunities__c);
        System.assertEquals(100, a.Num_of_Closed_Opportunities__c);

        //Let's delete some opportunities now (going to delete every other in the list)
        List<Opportunity> deleteList = new List<Opportunity>();
        for(Integer i=0; i<200; i++){
          deleteList.add(oList[i]);
          i++; //to skip every other
        }

        delete deleteList;
        a = [select Total_Number_of_Opportunities__c, Num_of_Won_Opportunities__c, Num_of_Closed_Opportunities__c from Account where Id = :a.id limit 1];
        System.assertEquals(100, a.Total_Number_of_Opportunities__c);
        System.assertEquals(25, a.Num_of_Won_Opportunities__c);
        System.assertEquals(50, a.Num_of_Closed_Opportunities__c);

        //Finally, let's undelete the deleted opportunities
        undelete deleteList;
        a = [select Total_Number_of_Opportunities__c, Num_of_Won_Opportunities__c, Num_of_Closed_Opportunities__c from Account where Id = :a.id limit 1];
        System.assertEquals(200, a.Total_Number_of_Opportunities__c);
        System.assertEquals(50, a.Num_of_Won_Opportunities__c);
        System.assertEquals(100, a.Num_of_Closed_Opportunities__c);

    }
}

You can install this trigger and class along with the above three fields. You will need to add the fields to your page layouts if you choose too.
https://login.salesforce.com/packaging/installPackage.apexp?p0=04tE00000000Rnx

Oops! I Didn’t Want to Dismiss All

The infamous Matt Brown threw out a question the other day

After some back and forth with Matt it turned out there are many questions on the Salesforce.com Answers forums related to getting reminders back after a user clicks ‘Dismiss All’ on the reminders pop up. I decided to give this a stab and the following is the result.

I created a Visualforce Page that simply has a button on it along with a disclaimer of what the button will do.

<apex:page controller="resetReminderController" sidebar="false">
    <apex:form >
        <apex:panelGrid columns="2">
            <apex:outputPanel >
                <apex:outputText >
                    <br/><br/>
                    To reset all of your <b>open</b> task and <b>future</b> event reminders click the button.<br/>
                    Any open tasks and any future events will have the reminders turned on. <br/><br/>
                </apex:outputText>
            </apex:outputPanel>
            <apex:commandButton value="Reset Reminders" action="{!processItems}"/>
        </apex:panelGrid>
    </apex:form>

    <apex:outputText value="No tasks or events processed" rendered="{!noRecords}" style="color:red"/>

    <apex:pageBlock title="Updated Tasks" tabStyle="Task" rendered="{!RenderTasks}" id="theTasks">
        <apex:pageBlockTable value="{!updateTaskList}" var="ut">
            <apex:column value="{!ut.Subject}"/>
            <apex:column value="{!ut.ActivityDate}"/>
            <apex:column value="{!ut.IsReminderSet}"/>
            <apex:column value="{!ut.ReminderDateTime}"/>
            <apex:column value="{!ut.whoId}"/>
            <apex:column value="{!ut.whatId}"/>
        </apex:pageBlockTable>
    </apex:pageBlock>

    <apex:pageBlock title="Updated Events" tabStyle="Event" rendered="{!RenderEvents}" id="theEvents">
        <apex:pageBlockTable value="{!updateEventList}" var="ue">
            <apex:column value="{!ue.Subject}"/>
            <apex:column value="{!ue.ActivityDate}"/>
            <apex:column value="{!ue.IsReminderSet}"/>
            <apex:column value="{!ue.ReminderDateTime}"/>
            <apex:column value="{!ue.whoId}"/>
            <apex:column value="{!ue.whatId}"/>
        </apex:pageBlockTable>
    </apex:pageBlock>
</apex:page>

The main purpose of the controller is to query for all open tasks and future events owned by the current user and set the reminder flag to true, allowing them to show up in the reminder pop-up when logging in. The controller will not set the reminder flag for any Tasks that never had a reminder set.  We know this because the Reminder Date is null.  Had it been set previously, it would still be set; the reminder flag was just unchecked.

public with sharing class resetReminderController {

    public List&lt;Task&gt; updateTaskList {get;set;}
    public List&lt;Event&gt; updateEventList {get;set;}
    public Boolean noRecords {get;set;}

    //This is our constructor. initialize the lists and noRecords
    public resetReminderController(){
        updateTaskList = new List&lt;Task&gt;();
        updateEventList = new List&lt;Event&gt;();
        noRecords=false;
    }

    public boolean getRenderTasks(){
        return !updateTaskList.IsEmpty();
    }

    public boolean getRenderEvents(){
        return !updateEventList.IsEmpty();
    }

    public void processItems(){
        //make sure the lists are clear
        updateTaskList.clear();
        updateEventList.clear();

        //if there are no records, we want to display that message to the user
        noRecords=true;

        //loop though users open tasks and update any task with no reminder.  if reminder date is null, then reminder was never set in the first place
        for(Task t : [select Id, Subject, ActivityDate, ReminderDateTime, IsReminderSet, whoId, whatId from Task where OwnerId = :UserInfo.getUserId() and IsClosed=false]){
            if(!t.IsReminderSet &amp;&amp; t.ReminderDateTime &lt;&gt; null){
                t.IsReminderSet = true;
                updateTaskList.add(t);
            }
        }

       //loop through all future events and turn on the reminder.  since reminder for events is a picklist, it won't be null. no need to account for that
       for(Event e : [select Id, Subject, ActivityDate, ReminderDateTime, IsReminderSet, whoId, whatId from Event where OwnerId = :UserInfo.getUserId() AND (ActivityDateTime &gt;= :DateTime.now() OR ActivityDate &gt;= :Date.Today()) ]){
            if(!e.IsReminderSet){
                e.IsReminderSet = true;
                updateEventList.add(e);
            }
        }

        //update any tasks that were changed
        if(!updateTaskList.IsEmpty()){
            update updateTaskList;
            noRecords=false;
        }

        //update any events that were changed
        if(!updateEventList.IsEmpty()){
            update updateEventList;
            noRecords=false;
        }
    }
}

Finally, I created a very simply home page component with a link to the visualforce page. The component name is “Reset Reminders For My Open Tasks and Event” and the html (be sure to check the ‘show html’ checkbox) is:

&lt;a href=&quot;/apex/resetReminders&quot;&gt;Click here&lt;/a&gt; to reset the reminders for tasks and events.&lt;div&gt;&lt;br&gt;&lt;/div&gt;

I figured the easiest way to show the functionality was with a quick demo.

Finally, you can install an unmanaged package of the home page component (don’t forget to add it to a page layout), visualforce page, controller and test method with this link:
https://login.salesforce.com/packaging/installPackage.apexp?p0=04tE00000000RkA
*Remember, you can change the path to https://test.salesforce.com&#8230; to install to a sandbox.

Overcoming Visualforce Standard List Controller Issue

I ran into an interesting issue while using a standard list controller on a visualforce page today.  This page has been working fine for a while but recently encountered an error due to a new trigger.  The trigger was “bulkified” so I found it odd that the following error was being thrown:

System.LimitException: Too many SOQL queries: 101

Let me set this up for you.  The visualforce page is a simple page (edited here to be even simpler) that is invoked from a list of opportunities.  The user can select any opportunities in the list view, click Edit Selected Opportunities and quickly update basic info on the opportunities (there are more fields on the actual page, but I will just include Name and Stage for brevity).

List View:

VisualforcePage:

Visualforce Code:

<apex:page standardController="Opportunity" recordSetVar="opps" sidebar="false">
    <apex:form id="theForm">
    <apex:pageBlock >
        <apex:pageMessages />

        <apex:pageBlockButtons >
            <apex:commandButton action="{!save}" value="Save All and Close"/>
            <apex:commandButton action="{!cancel}" value="Cancel"/>
        </apex:pageBlockButtons>

        <apex:pageBlockTable width="100%" value="{!selected}" var="o" id="theUSList" rendered="{!$User.US_User__c}">
            <apex:column value="{!o.AccountId}"/>

            <apex:column headerValue="Opportunity Name">
                <apex:inputField value="{!o.Name}"/>
            </apex:column>

            <apex:column HeaderValue="Stage">
                <apex:inputField value="{!o.StageName}"/>
            </apex:column>

        </apex:pageBlockTable>
    </apex:pageBlock>
    </apex:form>
</apex:page>

Problem

One of my users was using the tool after a new trigger was implemented and ran into the above mentioned error. It turned out that he was working with 53 records, which should work fine.

After some investigation it turned out that the standard save functionality was saving each record individually. These individual saves caused the trigger to fire for each record, rather than working on the set in bulk. Not cool salesforce.com, not cool at all. The problem was, there are 2 SOQL statements in the trigger so anything more than 50 records exceeded the limit of 100 SOQL queries. So to counteract this issue I wrote a controller extension which simply grabs the selected opportunities and saves them when the button is clicked.

Note, I had to change two lines in the above VF code

1. The page tag was changed to :


<apex:page standardController="Opportunity" <strong>extensions="selectedOpportunityEditorExtension"</strong> recordSetVar="opps" sidebar="false">

2. The save button was chaged to:


<apex:commandButton action="{!save<strong>Records</strong>}" value="Save All and Close"/>

Apex Class


public with sharing class selectedOpportunityEditorExtension {

    //Our list of opportunities to work on
    private final List<Opportunity> opps;

    //set the list to the selected opportunites. Same list that the page is working on
    public selectedOpportunityEditorExtension(ApexPages.StandardSetController controller) {
        this.opps = controller.getSelected();
    }

    //save the opportunities and return a page refernce to the retURL
    public PageReference SaveRecords(){
    	update opps;
    	PageReference pageRef = new PageReference(ApexPages.currentPage().getParameters().get('retURL'));
    	return pageRef;
    }
}

Test Method

@isTest
private class testSelectedOpportunityEditorExtension {

    static testMethod void myUnitTest() {
        //set up the page reference to the selectedOpportunityEditor and add retURL param
        PageReference pageRef = new PageReference('SelectedOpportunityEditor');
        //add the return URL to the list view
        pageRef.getParameters().put('retURL','/006');
        Test.setCurrentPageReference(pageRef);

        List<Opportunity> oppsList = new List<Opportunity>();
        //Use the current user as owner of new opps
        Id owner = UserInfo.getUserId();
        //Create 101 new opportunties to test with
        for(Integer i=1; i<102; i++){
        	oppsList.add(new Opportunity(Name='Opportunity ' + i,
        								 OwnerId = owner,
        								 Net_Gain__c = 100,
        								 StageName = 'Some Stage',
        								 Probability = 0.2,
        								 CloseDate=Date.newInstance(2100,01,01),
        								 Description='A description',
        								 Sales_Manager__c=owner

        	));
        }
        insert oppsList;

        //set up the set controller then select all opps
        ApexPages.StandardSetController ssc = new ApexPages.StandardSetController(oppsList);
        ssc.setSelected(oppsList);

        //initialize the extension
        selectedOpportunityEditorExtension sOEE = new selectedOpportunityEditorExtension(ssc);

        //Start the test
        Test.startTest();
        //get selected opportunites and change the Net gain for each
        List<Opportunity> loopList = ssc.getSelected();
        for(Opportunity o : loopList){
        	o.Net_Gain__c = 200;
        }

        //call the save records method and make sure it returns the right pagereference
        system.assertEquals('/006', sOEE.SaveRecords().getUrl() );

        //Check that all opportunities were in fact updated
        loopList = ssc.getSelected();
        for(Opportunity o : loopList){
        	System.AssertEquals(200, o.Net_Gain__c);
        }

        Test.stopTest();
    }
}

I hope that this proves useful for somebody. Please note the above code may be altered for this post and is specific to my companies org so it won’t work for you. It is just meant to serve as an example.

A special thanks to Alex Berg for helping me work though this issue.