SOQL Aggregate Functions

SOQL aggregate functions are extremely useful for summarizing sobject data in a helpful manner to end user business requirements.In this post I am discussing how you can use aggregate functions on your SOQL queries and in Apex your code.Before we move on  with the discussion first get familiar with available Aggregate Functions .


The SOQL queries which uses aggregate functions have few differences from typical soql queries without them.

• If you are using aggregate functions in your query  you can only use aggregate function(s) and group by fields in SELECT statement.
• SOQL will return a list of type aggregateresult  instead of sobject type of queried object.

example queries

 Integer recordCount = [Select count() from MyObject__c];  

in the case you use count() without group by  you will be returned a integer in other cases list of aggregateresult. For more details refer Count() vs Count(fieldname)

 AggregateResult[] aggr = [Select Category__c,count(Id) From MyObject__c Group By Category__c];  
 for(AggregateResult ar :aggr )  
 {  
   system.debug('count :- '+ar.get('expr0') +' category'+ar.get('Category__c '));  
 }  

when you use group by clause with a aggregate function you will get a aggregateresult type list. Size of this list will depend  the number of distinct values in queried object's group by field(s)

If you want to assign the returned result values to variables they have to be cast to the relevant type.

 eg:- integer count =(Integer) ar.get('expr0');  

Any aggregated field in a SELECT list that does not have an alias automatically gets an implied alias with a format expri, where i denotes the order of the aggregated fields with no explicit aliases. The value of i starts at 0 and increments for every aggregated field with no explicit alias.

Controlling Approval Proccesses From Apex

Salesforce Approval Processes are one of the greatest declarative features comes with the platform.It saves tons of work you have to do as developer if you had to implement them via code.Although they are very useful in their default form, you may come up against requirements that require more than declarative customizations provide. Programmatic access to Salesforce Approval process via Apex come can handy in such situations. This Post discusses how Approval Process Submit ,Approve and Reject actions can be triggered from Apex code

Submit 

 Approval.ProcessSubmitRequest req1 = new Approval.ProcessSubmitRequest();  
 req1.setComments('Submitting request for approval.');  
 req1.setObjectId(RecordId);  
 //RecordId is the id of the record that is submitted for Approval  
 Approval.ProcessResult result = Approval.process(req1);                                    

Approve

 //RecordId is the id of the record that is submitted for Approval 

List<ProcessInstance> PI = [Select Id,TargetObjectId FROM ProcessInstance WHERE TargetObjectId=:RecordId AND Status='Pending'];  
 if(PI.size() > 0)  
 {  
  List<ProcessInstanceWorkitem> PIWI = [Select Id ,ActorId FROM ProcessInstanceWorkitem WHERE ProcessInstanceId=:PI[0].Id];  
  if(PIWI.size() > 0)  
  {  
   Approval.ProcessWorkitemRequest req2 = new Approval.ProcessWorkitemRequest();  
   req2.setComments('Approving request.');  
   req2.setAction('Approve');  
   req2.setNextApproverIds(new Id[] {UserInfo.getUserId()});  
   req2.setWorkitemId(PIWI[0].Id);  
   Approval.ProcessResult result2 = Approval.process(req2);  
  }  
 }  

Reject

 //RecordId is the id of the record that is submitted for Approval 

List<ProcessInstance> PI = [Select Id,TargetObjectId FROM ProcessInstance WHERE TargetObjectId=:RecordId AND Status='Pending'];  
 if(PI.size() > 0)  
 {  
      List<ProcessInstanceWorkitem> PIWI = [Select Id ,ActorId FROM ProcessInstanceWorkitem WHERE ProcessInstanceId=:PI[0].Id];  
     if(PIWI.size() > 0)  
     {  
          Approval.ProcessWorkitemRequest req2 = new Approval.ProcessWorkitemRequest();  
         req2.setComments('Approving request.');  
         req2.setAction('Reject');  
         req2.setNextApproverIds(new Id[] {UserInfo.getUserId()});  
         req2.setWorkitemId(PIWI[0].Id);  
         Approval.ProcessResult result2 = Approval.process(req2);  
     }  
 }  

Salesforce Sending Mail Attachments

Sending attachments via mail has become a common occurrence in everyday life.So it is not much of a surprise if you get a requirement to send and attachment via system generated mail as I did. So in this article I am going to discuss how to send an Word document generated by Visualforce as a mail attachment.I am going to use the Visualforce  Word document generated  in my previous post Render Visualforce Page As A Word Document  as the attachment for the purpose of this article.

given below is the Apex code for sending Word Form as an mail attachment.

     List<Messaging.SingleEmailMessage> EmailMessages= new List<Messaging.SingleEmailMessage>();  
     List<User> loggedUser = [Select Id,Name,Email from User where Id=:UserInfo.getUserId()];  
      Messaging.SingleEmailMessage EmailMsg = new Messaging.SingleEmailMessage();  
      //define mail body content -begin (here i used html tags to format my mail body because I am setting htmbody  
      String EmailBody = '<html><body> Dear User,<br/><br/>';  
      EmailBody ='Please Fill the attached document and send us';  
      EmailBody += '<br/><br/>Thank you,<br/>'+loggedUser[0].Name+'<br/><br/>';  
      EmailBody += '</body></html>';  
      //define mail body content -end  
      EmailMsg.setHtmlBody(EmailBody); // set the html body contnet  
      EmailMsg.setToAddresses(new String[]{'mailaddreses seperated by commas'});//set recipient mail addresses  
      EmailMsg.setSubject('SalesforceCat Demo');  
      EmailMsg.setReplyTo(loggedUser[0].Email);// set reply to email address becuase I expect this mailed to be replied  
      EmailMsg.setUseSignature(true);  
      PageReference Word = new PageReference('/apex/WordDownloadExample'); // url of ur visualforce page rendered as a word  
      //Word.getParameters().put('Id','xxxx'); if any parameters need to be passed to the page 
      Word.setRedirect(true); //do a virtual redirect to the page so we can get page content using page reference
      Blob b;  
      b = Word.getContent();// get the content of the page and assignment to blob  
     
      
      Messaging.EmailFileAttachment attachment = new Messaging.EmailFileAttachment();// create an attachment instance  
      attachment.setBody(b); // set the blob as attachment content  
      attachment.setFileName('attach.doc'); // set the attachment name  
      EmailMsg.setFileAttachments(new Messaging.EmailFileAttachment[] { attachment}); // link the attachment to the mail  
      EmailMessages.add(EmailMsg);  
      if(!Test.isRunningTest())  
      {  
      Messaging.sendEmail(EmailMessages);   
      }   

 Mail with word form as an attachment

mail sent

Render Visualforce Page As a Word Document

Normally we use Visualforce pages to generate online forms and pages but lately I met with a requirement where Visualforce page should be rendered as an downloadable word document.So in the post I thought of sharing simpler version of my code as some of you may find it useful.

So lets get into work Although you may think rendering a page as a Word document may involve lot of  work all you have to do is add one attribute to <apex:page> tag. That attribute is contentType.

eg:contentType="application/msword#SalesforceCat.doc"

In above example

• application/msword# means page should be rendered as word document
• SalesforceCat.doc   means default name of the downloaded document would be SalesforceCat

When you are rendering Visualforce page as a word document please take below points also into consideration

• Some Visualforce  native page elements such as <apex:pageblock>, <apex:pageblockSection> doesn't have same styling effect on pages rendered as Word document. Therefore it is better you can use standard html tags and do the styling using inling css or  css classes defined within the page.
•  <Apex:form> or other child  apex form components do not render in document mode therefore use html form elements if your document need to include form components such as radio buttons and text fields..

Visualforce Page Rendered as  Word Document

   Code of the above given Visualforce Page

 <apex:page applyHtmlTag="true" showHeader="true" contentType="application/msword#SalesforceCat.doc"  >  
   <div>  
   <h3>Applicant Refree FeedBack From </h3>  
   </div>  
    <head>  
     <style>   
       body { font-family: 'Arial Unicode MS'; }  
       .caption {  
          background: #cfeef8;  
          height:30px;  
          font-size:15px;;  
          margin-left: -1px; /* Fix FF 1px issue */  
         }  
       .Question  
       {  
        height:30px;   
        background-color:#efefef;   
        border:1px solid #ccc;   
        padding:2px 2px; width:50%;  
       }  
       .Answere  
       {  
         height:30px;    
         border:1px solid #ccc;   
         padding:3px 2px 3px 4px;  
       }    
       .rowstyle {  
        height:50px;  
       }  
       .rowstyle td{width:30%;}  
     </style>  
   </head>  
   <apex:outputPanel >  
   <table border="0"  cellspacing="0" cellpadding="0" id="resultTable" width="100%">  
    <caption style="background: #cfeef8;height:23px;font-weight: bold;font-size: 16px; margin-left: -1px; text-align:left; padding:0 0 0 4px;">Feed Back Questions</caption>  
    <tr>  
    <th class="height:30px; background-color:#efefef; border:1px solid #ccc; padding:2px 2px; width:50%;" >Answere All the Questions</th>  
    </tr>  
    <tr >  
     <td class="Question">  
     <apex:outputLabel value="Radio Button Type Question? ">  
     </apex:outputLabel>  
     </td>  
     </tr>  
     <tr>  
     <td class="Answere">  
       <apex:outputPanel >  
        <input type="radio" name="group1" value="Poor"/> Poor  
        <input type="radio" name="group1" value="Average"/> Average  
        <input type="radio" name="group1" value="Good"/> Good  
        <input type="radio" name="group1" value="Excellent"/> Excellent  
       </apex:outputPanel>  
     </td>  
    </tr>  
    <tr>    
    <td width="400" class="Question">  
     <apex:outputLabel value="Check Box Type Question? ">  
    </apex:outputLabel>  
    </td>  
    </tr>  
     <tr>  
     <td style="height:30px;  border:1px solid #ccc; padding:3px 2px 3px 4px;">  
       <apex:outputPanel >  
         <input type="checkbox" name="vehicle" value="Bike">I have a bike </input>  
         <input type="checkbox" name="vehicle" value="Car">I have a car  </input>  
         <input type="checkbox" name="vehicle" value="Bike">I have a van </input>  
         <input type="checkbox" name="vehicle" value="Car">I have a lorry </input>  
       </apex:outputPanel>  
     </td>  
    </tr>   
    <tr>    
    <td width="400" class="Question" >  
    <apex:outputLabel value="Select List Type Question? ">  
    </apex:outputLabel>  
    </td>  
     </tr>  
     <tr>  
     <td style="height:30px; border:1px solid #ccc; padding:3px 2px 3px 4px;">  
       <apex:outputPanel >  
         <select value="select">  
         <option value="volvo">Volvo</option>  
         <option value="saab">Saab</option>  
         <option value="mercedes">Mercedes</option>  
         <option value="audi">Audi</option>  
        </select>  
       </apex:outputPanel>  
     </td>  
    </tr>    
   </table>  
   </apex:outputPanel>  
    <table width="100%">  
    <tr>  
     <td class="Question">  
      <b>Referee Name</b>  
     </td>  
     <td class="Question">  
      <input type="text" size="35" />  
     </td>  
    </tr>  
    <tr>  
     <td class="Question">  
     <b>Contact Number</b>   
     </td>  
    <td class="Question">  
      <input type="text" size="35" />  
     </td>  
    </tr>  
     <tr>  
     <td class="Question">  
     <b>Date</b>  
     </td>  
     <td class="Question">  
      <input type="text" value="(Enter in DD/MM/YYYY Format" size="35" />  
     </td>  
    </tr>  
    </table>  
 </apex:page>  

Salesforce Access All Object List Views From Visualforce

Salesforce standard list views are one of the most useful built in features comes with the platform.They serve well for users reporting and filtering needs.Although Salesforce standard list views are great to provide end users access to them you have to either create tab for each of your objects or built custom Visualforce pages with <apex:Listviews> tag for each of your objects.Although above solutions work fine for org with limited number of objects as the number of objects grow you may find it bit unpractical.

In this post I am going to describe you how you can access all the custom object list views via one Visualforce page using Salesforce meta data api.



For this purpose i have created a Visualforce page named  ListViewManager and an Apex controller named
ListviewController.

ListviewController

 public class ListviewController {  
   public List<SelectOPtion> Objectlist {get;set;}  
   public String Selectedview {get;set;}  
   public String objectName {get;set;}  
   Map<String,String> prefixtoName {get;set;}  
   public ListviewController()  
   {  
     Objectlist = new List<SelectOption>();  
     prefixtoName = new Map<String,String>();  
     Objectlist.add(new SelectOption('','Select'));  
     //get all object meta details using meta data api  
     Map<String, Schema.SObjectType> gd = Schema.getGlobalDescribe();  
     for(String sObj : gd.keySet()){  
       //get details of each object  
       Schema.DescribeSObjectResult r = gd.get(sObj).getDescribe();  
       //get only custom object with key perfix, there are some internal standard objects without key       //prefix. If you want standard objects also considered you must check them by name as all         //standard objects does't have list views  
       if( r.getKeyPrefix() != null && r.getKeyPrefix()!='' && r.getName().contains('__c'))  
       {  
        //Create a list of selectoptins with keyprefix as value and object label as label of selectoption  
        Objectlist.add(new SelectOption( r.getKeyPrefix(),r.getLabel()));  
        //maintain a map of keyprefix to object name  
        prefixtoName.put(r.getKeyPrefix(), r.getLabel());   
       }  
     }  
   }   
  public void callServer()  
  {  
   //get label of selected object  
   objectName = prefixtoName.get(Selectedview);  
  }  
 }  

ListViewManager

 <apex:page controller="ListviewController" >  
  <apex:Form >  
  <apex:SectionHeader title="ListView Manager"/>  
  <apex:pageblock id="ViewBlock" >  
   <apex:pageMessages ></apex:pageMessages>  
   <apex:pageblockSection title="List view Selector" >  
    <apex:pageblockSectionItem >  
    <apex:outputLabel value="Select Object"/>  
    <apex:SelectList value="{!Selectedview}" size="1">  
     <apex:SelectOptions value="{!Objectlist}"/>  
     <apex:actionSupport event="onchange" action="{!CallServer}" rerender="ViewBlock"/>  
    </apex:SelectList>  
    </apex:pageblockSectionItem>  
    <apex:outputLink value="/{!Selectedview}" target="_blank" rendered="{!objectName!= null && objectName!=''}">{!objectName}  List view </apex:outputLink>  
   </apex:pageblockSection>  
   </apex:pageblock>  
  </apex:Form>  
 </apex:page>  

.

• drop-down lists the all custom object names in your org

• Select the name of the object you want to access the list view of.

• Upon selection of the object ,page will provide you with a link to access the standard list view

Salesforce History Tracking :Access Field History From Apex Code

In my last post Salesforce History Tracking - Step by Step Guide I discussed in detail about enabling history tracking on a custom object and viewing those logs using Salesforce reports and History tracking related list.In this post I am going to discuss how field tracking logs can be accessed from Apex code.

Salesforce maintains separate internal object to maintain history logs for each history tracking enabled object.The naming of these tables follows a simple convention so should be easy to find. For standard objects the name of the history table follows the format: '<ObjectName>History' so for the Account object the history table is AccountHistory.

For custom objects the name of the convention simply replaces the 'c' on the end of the API name with 'History'. So, for a custom object call My_Object__c the history table is called My_Object_History.

All history tracking tables has following fields.

• ParentId : This is the Id  of record this history tracking log is related to (id of record field modification happend)
• Field      :  API name of the modified field.
• OldValue : Field value prior to the modification.
• NewValue : Field value after modification
• CreatedById : Id of the User who made the modification. 

History Tracking logs can be queried via SOQL as shown below

Query all field modification logs of a custom object called  My_Object__c

 Select Id,Field,CreatedById ,OldValue,NewValue,EditedBy From My_Object__History  

Query All field modification logs related to a field called My_Field__c of My_Object__c

 Select Id,Field,CreatedById ,OldValue,NewValue,EditedBy From Friend__History   
 Where Field ='My_Field__c'  

Query  All field modfication logs related to a particular record

 

 Select Id,Field,CreatedById ,OldValue,NewValue,EditedBy From Friend__History   
 Where ParentId=:RecordId   

Using SOQL to query history logs you can build your own Visualforce pages to display field modification details to the end users. 

Salesforce History Tracking - Step by Step Guide

One of the advantages of working on a cloud platform like Salesforce is some really cool and important functions comes inbuilt and only we have to do is setting up those according to your requirements.One such feature is Salesforce History Tracking.

History tracking is the Salesforce way of monitoring and auditing the changes done to data (object records) by the users. In any business monitoring and auditing data changes is important for security and administrative purposes.In this article I am going to discuss how to enable history tracking on a Custom object and how to monitor the changes done by the users to records of history tracking enabled object.

To learn more refer Salesforce History Tracking

1. Go to the object you want to track history - here my object is Friend.

 2. Click on the Edit button and scroll down to Optional Features section and tick  following options

• Track Field History
• Allow Reports

3. Now on top of your Custom Field List  you can see a new button Set History Tracking. Click on it and Select the Fields on the object you want monitored. You are only allowed to track maximum of 20 fields so choose wisely :)

Now we are done with Setting up the object for history tracking. This point  forward any changes made to the  tracking enable fields of the object will be logged by Salesforce. Our next task is to create a report on those logs. so we can view those tracking details.

4. Click on the plus (+) on your tab list .

5. Scroll to Reports link and click on it.

6. Click New Report

7. Then Select the Report Type .This should be  "< your object name>  History". Since my object is Friend , reporting Type is "Friend History".

• If reporting type is not available please check you have enabled Allow Reports option on your object
• Reporting Type is not created for Detail objects of Master - Detail relationship so please make sure your object (History Tracking enabled) is not a detail object of a master -Detail relationship.

8. Click on Create

  

Parent ID field appearing in the history records is the Salesforce ID of the actual record to which that history record is related.

9. Customize the report according to your requirements and click on Save  

• Give a name to your report
• Select the Folder you want to save the report based on who you want to allow to access the report

  Now we are done.

10. Then you can run the report to see the details

11.  Optionally you can add History tracking related list to your page layout (just click related lists and drag and drop <object Name> History  list to your layout and Save the layout).Since it will show the field tracking details to the all the users who has read access to the records it is not the best idea but it depends on your Application's requirement and data sensitivity.

important points to note

• History tracking starts from point you enable history tracking. No changes prior to enabling history tracking won't be available..
• Changes to Fields with more than 255 characters are tracked as Edited. Their old and new values are not recorded.
• Cannot create History report on Custom objects which has  master-Detail relationships.
• If you disable history tracking for custom object, you cannot report on its  field history.