Category Archives: Salesforce

Aggregate Functions in SOQL

In database management an aggregate function is a function where the values of multiple rows are grouped together to form a single value. In Salesforce SOQL aggregate functions are same as SQL aggregate function.

The following aggregate functions are provided by SOQL:

  • AVG()
  • COUNT() and COUNT(fieldName)
  • COUNT_DISTINCT()
  • MIN()
  • MAX()
  • SUM()

AVG(): Returns the average value of a numeric field.

Example:

SELECT CampaignId, AVG(Amount)
FROM Opportunity
GROUP BY CampaignId

COUNT() and COUNT(fieldName): Returns the number of rows matching the query criteria. COUNT(Id) in SOQL is equivalent to COUNT(*) in SQL. COUNT(fieldName) available in API version 18.0 and later. If you are using a GROUP BY clause, use COUNT(fieldName) instead of COUNT().

Example using COUNT():

SELECT COUNT()
FROM Account
WHERE Name LIKE 'a%'

Example using COUNT(fieldName):

SELECT COUNT(Id)
FROM Account
WHERE Name LIKE 'a%'

COUNT_DISTINCT(): Returns the number of distinct non-null field values matching the query criteria. COUNT_DISTINCT(fieldName) in SOQL is equivalent to COUNT(DISTINCT fieldName) in SQL. To query for all the distinct values, including null, for an object, see GROUP BY. Available in API version 18.0 and later.

Example:

SELECT COUNT_DISTINCT(Company)
FROM Lead

MIN(): Returns the minimum value of a field. If you use the MIN() or MAX() functions on a picklist field, the function uses the sort order of the picklist values instead of alphabetical order. Available in API version 18.0 and later.

Example:

SELECT MIN(CreatedDate), FirstName, LastName
FROM Contact
GROUP BY FirstName, LastName

MAX(): Returns the maximum value of a field.
Example:

SELECT Name, MAX(BudgetedCost)
FROM Campaign
GROUP BY Name

SUM(): Returns the total sum of a numeric field.
Example:

SELECT SUM(Amount)
FROM Opportunity
WHERE IsClosed = false AND Probability > 60

Note: You can’t use a LIMIT clause in a query that uses an aggregate function. The following query is invalid:

SELECT MAX(CreatedDate)
FROM Account LIMIT 1

The aggregate functions COUNT(fieldname), COUNT_DISTINCT(), SUM(), AVG(), MIN() and MAX() in SOQL return an AggregateResult object or a List of AggregateResult objects. You can use aggregate functions result in apex by using AggregateResult object.

Example:

List<AggregateResult> result = [SELECT COUNT(Id) Total, AccountId FROM Contact WHERE AccountId != null GROUP BY AccountId];  

Undelete Records in Salesforce Using Batch Apex

Sample Code:

global class AccountUndeleteBatchApex implements Database.Batchable<sObject>{
    
    global Database.queryLocator start(Database.BatchableContext bc){
        String query = 'SELECT Id, Name FROM Account WHERE IsDeleted = True ALL ROWS';
        return Database.getQueryLocator(query);
    }
    
    global void execute(Database.BatchableContext bc, List<Account> scope){
        List<Account> accList = new List<Account>();
        for(Account s:scope){
            //Write your logic
            accList.add(s);
        }
        Undelete accList;
    }
    
    global void finish(Database.BatchableContext bc){
        
    }
}

Bulkify Apex Trigger in Salesforce

Bulkifying Apex Trigger refers to the concept of making sure the code properly handles more than one record at a time. When a batch of records initiates Apex, a single instance of that Apex code is executed, but it needs to handle all of the records in that given batch.

For example, a trigger could be invoked by an Force.com SOAP API call that inserted a batch of records. So if a batch of records invokes the same Apex trigger, all of those records need to be processed as a bulk, in order to write scalable code and avoid hitting governor limits.

Here is a simple example of some Apex code both bulkified, and not bulkified.

Not Bulkified:

trigger AccountTriggr on Account (before update) {
    
    //This only handles the first record in the Trigger.new collection
    //But if more than one Account initiated this trigger, those additional records
    //will not be processed
    Account acc = Trigger.new[0];
    List<Contact> conList = [SELECT Id, FirstName, LastName, Email, Phone FROM Contact WHERE AccountId = :acc.Id];
}

In above apex trigger the issue is that only one Account record is handled because the code explicitly accesses only the first record in the Trigger.new collection by using Trigger.new[0]. Instead, the trigger should properly handle the entire collection of Accounts in the Trigger.new collection.

Bulkified:

trigger AccountTriggr on Account (before update) {
    
    //Loop through all records in the Trigger.new collection
    for(Account a: Trigger.new){
        //Write your logic
    }
}

In above bulkified apex trigger the code iterates across the entire Trigger.new collection with a for loop. Now if this trigger is invoked with a single Account or up to 200 Accounts, all records are properly processed.

Using Custom Controller in Visualforce Email Template

To show some records or render customized content on a visualforce email template, we need to include a custom component in a Visualforce email template that uses the custom controller.

Here is an example of visualforce email template with list of contacts of one account record.

Apex Controller:

public class AccountEmailTemplate
{
    public Id accountId {get;set;}
    public List<Contact> getContactList()
    {
        List<Contact> conList;
        conList = [SELECT FirstName, LastName, Email, Phone FROM Contact WHERE AccountId =: accountId];
        return conList;
    }
}

Visualforce Component(ContactList):

<apex:component controller="AccountEmailTemplate" access="global">
    <apex:attribute name="AccId" type="Id" description="Id of the account" assignTo="{!accountId}"/>
    <table border = "2" cellspacing = "5">
        <tr>
            <td>First Name</td>
            <td>Last Name</td>    
            <td>Email</td>    
            <td>Phone</td>                
        </tr>
        <apex:repeat value="{!ContactList}" var="con">
            <tr>
                <td>{!con.FirstName}</td>
                <td>{!con.LastName}</td>     
                <td>{!con.Email}</td>     
                <td>{!con.Phone}</td>     
            </tr>
        </apex:repeat>        
    </table>
</apex:component> 

Visualforce Email Template:

<messaging:emailTemplate subject="List of Contacts" recipientType="User" relatedToType="Account">
    <messaging:htmlEmailBody >
        Hi,<br/>
        Below is the list of contacts for account {!relatedTo.Name}.<br/><br/>
        <!--Embedded Visualforce component here -->
        <c:ContactList AccId="{!relatedTo.Id}" /><br/><br/>
        <b>Regards,</b><br/>
        {!recipient.FirstName}
    </messaging:htmlEmailBody>
</messaging:emailTemplate>

Output: