Category Archives:

Dynamically populating custom HTML email template content in with custom dynamic data using Apex

The platform provides rich set of features to send emails through the apex code. It provides different templates like visualforce templates, HTML email templates and text templates so that the developers can create rich email experience. The beauty of these templates is that as a developer you can include the fields from an object, so that the emails will be completely customized for the given scenario. For e.g. if you want to send a nicely formatted HTML emails with the opportunity information to your sales group, then you can include the opportunity related fields such as the opportunity name, whether the opportunity is won or lost, etc. There are several articles that covers the basics of email services like this and this. By default, as a developer, you can only send the values of the object fields in the email and cannot include anything that is dynamically computed. This article will show you how to dynamically populate the content of a custom HTML email template with custom data. For e.g. adding dynamically calculated data such as number of days it took to won the opportunity, total number of sales by a sales person in a given month, etc. I’m also going to show you how you can write some generic code that can take varying dynamic parameters, populate the custom HTML email template with those dynamic parameters along with the standard/custom fields from standard/custom objects and send an email to the recipients. What is unique in this approach is that it is not only possible to populate the email body text with custom created dynamic data, but also possible to populate the email subject with custom created dynamic data.

For this demonstration, let us take the following use case. The requirement is to send an email notification on the Case object whenever a case is not closed and the case is still pending even after the set SLA period. For e.g. if a case is pending for 5 days since it was opened and if the SLA was set for 5 days, then on the 6th day, an email notification needs to be sent. Here is the screenshot of how the email will look alike when it is sent. The values highlighted in the red come from the standard fields, while the values highlighted in blue are calculated dynamically.


Here is the high level approach to accomplish this.

  • Create the custom HTML email template
  • Create the logic to populate the subject and the body text and send the email
  • Create a apex scheduler to send the email

1. Create the custom HTML email template

A. The following screenshots show the steps in creating the custom HTML email template.


B. In order to include the dynamically calculated data, follow the same convention of using the merge fields. For e.g to include the case status, include ‘{!Case.Status}. Please note that unlike in the standard merge field feature in the HTML templates, here you can use any format for the merge field syntax. For the custom data, I suggest to use the variable name that you used to calculate the dynamic data. In the following example, ‘{!numDays} indicate the number of days the case is open since it was created.


C. Specify the text for the plain text email. You can click the ‘Copy text from HTML version’ button to copy the content in the HTML template, unless you want to specify a different text for plain text email.


Click ‘Save’.

2. Create the logic to populate the subject and the body text and send the email

In the above example, the subject has two dynamic parameters and the body text has 6 dynamic parameters. Each requirement is different and we need a generic method to accommodate different number of parameters for both subject and the body text. To accomplish that, I have used two ‘maps’, one for subject and another for body text. This way, we can add any number of dynamic parameters and able to customize based on the requirements. I have created two apex classes to accommodate this logic:

  • EmailMessageWrapper – Wraps email message. Provides different constructors to capture email information.
  • UtilityClass – Provides method(s) to send emails. One of the method supports sending email using email templates and this is the method that we will use for this demo.

The following code snippet from EmailMessageWrapper shows the constructor that we will use in this demo.

   1: public EmailMessageWrapper(String fromAddr, Id toAddrId, String sub, Map<String, String> mapSubjectParams, Map<String, String> mapBodyParams) {

   2:     this(fromAddr, null, toAddrId, null, sub, mapSubjectParams, null, mapBodyParams );

   3: }


   5: public EmailMessageWrapper(String fromAddr, String toAddr, Id toAddrId, String bccAddr, String sub, Map<String, String>  mapSubjectParams, String body, Map<String, String> mapBodyParams) {

   6:     this.FromAddress = fromAddr;

   7:     this.ToAddress = toAddr;

   8:     this.ToAddressId = toAddrId;

   9:     this.BccAddress = bccAddr;

  10:     this.Subject = sub;

  11:     this.ParameterSubjectMap = mapSubjectParams;

  12:     this.Body = body;

  13:     this.ParameterBodyMap = mapBodyParams;

  14: }

The mapSubjectParams and the mapBodyParams parameters holds the dynamic data as a key/value pair. The key corresponds to the merge field that is defined in the custom HTML email template, while the ‘value’ is the data that will be merged into the merge field.

The following code snippet from UtilityClass shows the sendEmail method that takes two parameters. The first parameter is a list of EmailMessageWrapper and the second parameter is the email template name.

   1: public static void sendEmail(List<EmailMessageWrapper> listEmailMessageWrapper, String emailTemplateName) {

   2:     List<Messaging.SendEmailResult> listEmailResult = null;

   3:     List<Messaging.Singleemailmessage> listSingleEmailMessages = new List<Messaging.Singleemailmessage>();

   4:     EmailTemplate emailTemplate = [SELECT Id, Subject, HtmlValue, Body FROM EmailTemplate WHERE Name = :emailTemplateName];

   5:     for (EmailMessageWrapper emailMessageWrapper : listEmailMessageWrapper) {

   6:         Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();

   7:         mail.setSenderDisplayName('Case Age Notification');

   8:         if(emailMessageWrapper.FromAddress != null && emailMessageWrapper.FromAddress.length() > 0)

   9:             mail.setReplyTo(emailMessageWrapper.FromAddress);

  10:         if(emailMessageWrapper.ToAddress != null && emailMessageWrapper.ToAddress.length() > 0)

  11:             mail.setToAddresses(new String[] { emailMessageWrapper.ToAddress });

  12:         else

  13:             mail.setTargetObjectId(emailMessageWrapper.ToAddressId);

  14:         if(emailMessageWrapper.BccAddress != null && emailMessageWrapper.BccAddress.length() > 0)

  15:             mail.setBccAddresses(new String[] {emailMessageWrapper.BccAddress });

  16:         String subject = null;

  17:         if(emailMessageWrapper.Subject != null && emailMessageWrapper.Subject.length() > 0) {

  18:             mail.setSubject(emailMessageWrapper.Subject);

  19:             subject = emailMessageWrapper.Subject;

  20:         }

  21:         else

  22:             subject = emailTemplate.Subject;


  24:         for(String key: emailMessageWrapper.ParameterSubjectMap.keySet())

  25:             subject = subject.replace(key, (emailMessageWrapper.ParameterSubjectMap.get(key) == null ? '' : emailMessageWrapper.ParameterSubjectMap.get(key)));


  27:         mail.setSubject(subject);

  28:         String htmlBody = emailTemplate.HtmlValue;

  29:         String plainBody = emailTemplate.Body;

  30:         for (String key : emailMessageWrapper.ParameterBodyMap.keySet()) {

  31:             htmlBody = htmlBody.replace(key, (emailMessageWrapper.ParameterBodyMap.get(key) == null) ? '' : emailMessageWrapper.ParameterBodyMap.get(key));

  32:             plainBody = plainBody.replace(key, (emailMessageWrapper.ParameterBodyMap.get(key) == null) ? '' : emailMessageWrapper.ParameterBodyMap.get(key));

  33:         }


  35:         mail.setHtmlBody(htmlBody);

  36:         mail.setSaveAsActivity(false);

  37:         mail.setPlainTextBody(plainBody);

  38:         listSingleEmailMessages.add(mail);

  39:     }

  40:     if(!Test.isRunningTest())

  41:         listEmailResult = Messaging.sendEmail(listSingleEmailMessages);

  42: }

What you see in the above code is that we iterate through the maps to populate the dynamic data in the HTML email template content by replacing the merge field with the dynamic data from the map variable. Since it is a map, we can add any number of dynamic parameters as key/value pairs, where ‘key’ represents the merge field and the ‘value’ represents the data for the merge field.

3. Create a apex scheduler to send the email

The final piece of this demo is to create the apex scheduler to send the email. The following code snippet is from the CaseAgeNotificationExecute class which retrieves the eligible cases and sends an email notification.

   1: public static void processSchedules() {

   2:     Integer slaTime = 3;

   3:     List<EmailMessageWrapper> listEmailMessageWrapper = new List<EmailMessageWrapper>();


   5:     String slaTimeLabel = 'Days';

   6:     List<Case> listCases = [SELECT Id, CaseNumber, CreatedDate, OwnerId, Status FROM Case WHERE Status <> 'Closed'];

   7:     for(Case c : listCases) {

   8:         if( > c.CreatedDate.addDays(slaTime)) {

   9:             long numDays = ( / 1000 / 60 / 1440) - (c.CreatedDate.getTime() / 1000 / 60 / 1440);

  10:             Map<String, String> mapSubjectParams = new Map<String, String> {

  11:                 '{!Case.Status}' => c.Status,

  12:                 '{!numDays}' => String.valueOf(numDays)

  13:             };


  15:             Map<String, String> mapBodyParams = new Map<String, String> {

  16:                 '{!Case.CaseNumber}' => c.CaseNumber,

  17:                 '{!Case.Status}' => c.Status,

  18:                 '{!numDays}' => String.valueOf(numDays),

  19:                 '{!Case.CreatedDate}' =>,

  20:                 '{!slaTime}' => String.valueOf(slaTime),

  21:                 '{!slaTimeLabel}' => slaTimeLabel

  22:             };

  23:             listEmailMessageWrapper.add(new EmailMessageWrapper('', c.OwnerId, null, mapSubjectParams, mapBodyParams));

  24:         }

  25:     }

  26:     if(listEmailMessageWrapper.size() > 0)

  27:         UtilityClass.sendEmail(listEmailMessageWrapper, 'Case Aging Notification Email Template');

  28: }

To test this, you can call the CaseAgeNotificationExecute.processSchedules() method from either the developer console or using anonymous block from your eclipse IDE.

The code for entire set of classes referred in this article can be downloaded from here.


An architecture framework to handle triggers in the platform

Coding in Apex is similar to Java/C# in lot many ways, yet so different from them in few other ways. But one thing that is common is that the application of proper design patterns to solve the problem irrespective of platform or technological difference. This is very particular for the apex triggers, because in a typical Salesforce application, triggers play a pivotal role and a major chunk of the logic is driven through the triggers. Therefore a proper design of triggers is very essential, not only for successful implementations, but also for laying out a strong architectural foundation. There are several design patterns proposed like this, this and this. And some of these patterns have gotten mainstream support among the developers and the architects. This article series is going to propose one such design pattern for handling triggers. All these design patterns share a common trait – which is ‘One Trigger to rule them all’ – A phrase that Dan Appleman made it famous in his ‘Advanced Apex Programming‘ . In fact, the design pattern that is being published in this article series is heavily influenced by Dan Appleman’s original design pattern – many thanks to you, Dan. Another design pattern that influenced this implementation was published by Tony Scott and can be found here and I would like thank him as well for his wonderful contribution. I also borrowed couple of ideas from Adam Purkiss such as wrapping the trigger variables in a wrapper class and thank him as well. His training video on design patterns (available at pluralsight as a paid subscription) is great inspiration and anyone who is serious about taking their development skills on platform to the next level MUST watch them. That said, let’s dive into the details.

Now, why do we need a yet another design pattern for triggers, while we already have few. Couple of reasons – Firstly, though, the design pattern proposed by Dan and Tony provides a solid way of handling triggers, I feel that there is still room to get this better and provide a more elegant way of handling the triggers. Secondly, both these design patterns require touching the trigger framework code every time when a new trigger is added – the method that I propose eliminates it (Sure, this can be replicated with their design patterns as well, as I leverage the standard API). Thirdly, this trigger design pattern differentiates the dispatching and handling and provides full flexibility to the developers in designing their trigger handling logic. Last, but not least, this new framework, as the name suggests, it is not just a design pattern; it’s an architecture framework that provides complete control of handling triggers in more predictable and uniform way and helps to organize and structure the codebase in a very developer friendly way. Further, this design pattern takes complete care about dispatching and provides a framework for handling the events; thus the developers need to focus only on the building the logic to handle these events and not about dispatching.


The fundamental principles that this architecture framework promotes are

  • Order of execution
  • Separation of concerns
  • Control over reentrant code
  • Clear organization and structure

Order of execution

In the traditional way of implementing the triggers, a new trigger is defined (for the same object) as the requirements come in. Unfortunately, the problem with this type of implementation is that there is no control over the order of execution. This may not be a big problem for the smaller implementations, but definitely it’s a nightmare for medium to large implementations. In fact, this is the exact reason, many people have come with a trigger design pattern that promotes the idea of ‘One trigger to rule them all’. This architecture framework does support this and it achieves this principle by introducing the concept of ‘dispatchers’. More on this later.

Separation of concerns

The other design patterns that I referenced above pretty much promote the same idea of ‘One trigger to rule them all’. But the one thing that I see missing is the ‘Separation of concerns’. What I mean here is that, the trigger factory/dispatcher calls a method in a class which handles all the trigger event related code. Once again for smaller implementations, this might not be a problem, but medium to large implementations, very soon it will be difficult to maintain. As the requirements change or new requirements come in, these classes grow bigger and bigger. The new framework alleviates this by implementing the ‘handlers’ to address the ‘Separation of concerns’.

Control over reentrant code

Many times there will be situation where the trigger code might need to perform a DML operation on the same object, which might end up in invoking the same trigger once again. This can go recursive, but usually developers will introduce a condition variable (typically a static variable) to prevent that. But this is not a elegant solution because, this doesn’t guarantee an orderly fashion of reentrant code. The new architecture provides complete control to the developers such that the developers can either deny the reentrant or allow both the first time call and the reentrant, but in totally separate ways, so they don’t step on each other.

Clear organization and structure

As highlighted under the section ‘Separation of concerns’, with medium to larger implementations, the codebase grows with no order or structure. Very soon, the developers might find it difficult to maintain. The new framework provides complete control over organizing and structure the codebase based on the object and the event types.

UML Diagram

The following UML diagram captures all the pieces of this architecture framework.

Trigger Architecture Framework

Trigger Architecture Framework

Framework Components


The TriggerFactory is the entry point of this architecture framework and is the only line of code that resides in the trigger definition (you may have other code such as logging or something else, but as far as this architecture framework, this will be the only code required). The TriggerFactory class, as the name indicates, is a factory class that creates an instance of the dispatcher object that the caller (the trigger class) specifies and delegates the call to the appropriate event handler method (the trigger event such as ‘before insert’, ‘before update’, …)  that the dispatcher provides. The beauty of the TriggerFactory is that it automatically finds the correct dispatcher for the object that the trigger is associated as far as the dispatcher is named as per naming convention and this convention is very simple as specified in the following table.

Object Type Format Example Notes
Standard Objects <Object>TriggerDispatcher AccountTriggerDispatcher
Custom Objects <Object>TriggerDispatcher MyProductTriggerDispatcher Assuming that MyProduct__c is the custom object, then the dispatcher will be named without the ‘__c’.

It accomplishes this by using the new Type API. Using the Type API to construct the instances of the dispatchers helps to avoid touching the TriggerFactory class every time a new trigger dispatcher is added (ideally only one trigger dispatcher class is needed per object).


The dispatchers dispatch the trigger events to the appropriate event handlers to handle the trigger event(s). The framework provides the interface and a base class that provides virtual implementations of the interface methods, but the developers need to provide their own dispatcher class, which is derived from either the virtual base class for each object that they want to have this framework applied. Ideally, the developers want to inherit from the TriggerDispatcherBase, as it not only provides the virtual methods – giving the flexibility to the developers to implement the event handlers only that they are interested in their dispatcher class, but also the ability to provide reentrant feature to their logic.


As discussed above, the ITriggerDispatcher essentially contains the event handler method declarations. The trigger parameters are wrapped in a class named ‘TriggerParameters’.


The TriggerDispatcherBase class implements the interface ITriggerDispatcher, providing virtual implementations for those interface methods, so that the developers need not implement all the event handlers that they do not wish to use. The TriggerDispatcherBase also has one more important method named ‘execute’ which controls if a call has to be dispatched in reentrant fashion or not. It has a separate member variable for each event to hold the instance of the trigger handler for that particular event which the ‘execute’ method utilizes to control the reentrant feature.


The trigger dispatcher classes contains the methods to handle the trigger events and this is the place where the developers had to instantiate the appropriate trigger event handler classes. At the heart of the dispatcher lies the ITriggerDispatcher interface which provides an interface for the developers to implement the appropriate dispatcher for the objects. The interface provides definitions for all trigger events, which means that the trigger dispatcher that the developers implement should implement methods for all the events. However, since it may not necessary to provide implementations for all trigger events – the framework provides a base class named ‘TriggerDispatcherBase’ that provides default implementation (virtual methods) to handle all events. This allows developers to implement the methods for only the events  that they really have to, by deriving from TriggerDispatcherBase instead of implementing the ITriggerDispatcher interface, as the TriggerDispatcherBase implements this interface. One more reason that the developer wants to derive from TriggerDispatcherBase instead of ITriggerDispatcher is because the TriggerDispatcherBase.execute method provides the reentrant feature and the developer will not be able to leverage this feature if the trigger dispatcher for the objects do not derive from this class.

It is very important that the trigger dispatcher class to be named as per the naming convention described under the TriggerFactory section. If this naming convention is not followed, then the framework will not be able find the dispatcher and the trigger class would throw an exception.

Understanding the dispatcher is really critical to successfully implement this framework, as this is where the developer can precisely control the reentrant feature. This is achieved by the method named ‘execute’ in the TriggerDispatcherBase which the event handler methods call by passing an instance of the appropriate event handler class. The event handler methods sets a variable to track the reentrant feature and it is important to reset it after calling the ‘execute’ method. The following code shows a typical implementation of the event handler code for ‘after update’ trigger event on the Account object.

   1: private static Boolean isAfterUpdateProcessing = false;


   3: public virtual override void afterUpdate(TriggerParameters tp) {

   4:       if(!isAfterUpdateProcessing) {

   5:            isAfterUpdateProcessing = true;

   6:            execute(new AccountAfterUpdateTriggerHandler(), tp, TriggerParameters.TriggerEvent.afterUpdate);

   7:            isAfterUpdateProcessing = false;

   8:       }

   9:       else execute(null, tp, TriggerParameters.TriggerEvent.afterUpdate);

  10:  }

In this code, the variable ‘isAfterUpdateProcessing’ is the state variable and it is initialized to false when the trigger dispatcher is instantiated. Then, inside the event handler, a check is made sure that this method is not called already and the variable is then set to true, to indicate that a call to handle the after update event is in progress. Then we call the ‘execute’ method and then we are resetting the state variable. At the outset, this (resetting the state variable to false) may not seem very important, but failure to do so, will largely invalidate the framework and in fact in most cases you may not be able to deploy the application to production. Let me explain this – when a user does something with an object that has this framework implemented, for example, saving a record, the trigger gets invoked, the before trigger handlers are executed, the record is saved and the after trigger event handlers are executed and then either the page is refreshed or redirected to another page depending on the logic. All of this happens in one single context. So, it might look like the state variable such as ‘isAfterUpdateProcessing’ needs to be set to true inside the if condition.


Handlers contains the actual business logic that needs to be executed for a particular trigger event. Ideally, every trigger event will have an associated handler class to handle the logic for that particular event. This increases the number of classes to be written, but this provides a very clean organization and structure to the code base. This approach proves itself – as in the long run, the maintenance and enhancements are much easier as even a new developer would know exactly where to make the changes as far as he/she gets an understanding on how this framework works.

Another key functionality of the handlers is that the flexibility it gives to the developers to implement or ignore the reentrant functionality. The ‘mainEntry’ method is the gateway for the initial call. If this call makes a DML operation on the same object, then it will result in invoking this trigger again, but this time the framework knows that there is a call already in progress – hence instead of the ‘mainEntry’, this time, it will call the ‘inProgressEntry’ method. So if reentrant feature to be provided, then the developer need to place the code inside the ‘inProgressEntry’ method. The framework provides only the interface – the developers need to implement this interface for each event of an object. The developers can chose to ignore to implement the event handlers, if they are not going to handle the events.


The ITriggerHandler defines the interface to handle the trigger events in reentrant fashion or non-reentrant fashion.


The TriggerHandlerBase is an abstract class that implements the interface ITriggerHandler, providing virtual implementation for those interface methods, so that the developers need not implement all the methods, specifically, the ‘inProgressEntry’ and the ‘updateObjects’ methods, that they do not wish to use.


As discussed above, the developer need to define one class per event per object that implements the ITriggerHandler interface. While there is no strict requirement on the naming convention like the dispatcher, it is suggested to name as per the following convention.

Object Type Format Example Notes
Standard Objects <Object><Event>TriggerHandler AccountAfterInsertTriggerHandler,
AccountAfterUpdateTriggerHandler, etc.
Custom objects <Object><Event>TriggerHandler MyProductAfterInsertTriggerHandler Assuming that MyProduct__c is the custom object, then the handler will be named without the ‘__c’.

So, if we take the Account object, then we will have the following event handler classes to be implemented by the developer that maps to the corresponding trigger events.

Trigger Event Event Handler
Before Insert AccountBeforeInsertTriggerHandler
Before Update AccountBeforeUpdateTriggerHandler
Before Delete AccountBeforeDeleteTriggerHandler
After Insert AccountAfterInsertTriggerHandler
After Update AccountAfterUpdateTriggerHandler
After Delete AccountAfterDeleteTriggerHandler
After UnDelete AccountAfterUndeleteTriggerHandler

Note that NOT all the event handler classes, as defined in the above table, needs to be created.  If you are NOT going to handle a certain event, such as, ‘After Undelete’ for the Account object, then you do not need to define the ‘AccountAfterUnDeleteTriggerHandler’.


The TriggerParameters class encapsulates the trigger parameters that the platform provides during the invocation of a trigger. It is simply a convenience, as it avoid repeating all those parameters typing again and again in the event handler methods.


Often, there will be situations where you want to reuse the code from different event handlers such as sending email notifications. After all one of the fundamental principle of object oriented programming is code re-usability. In order to achieve that, this architecture framework proposes to place all the common code in a helper class so that not only the event handlers, but also the controllers, scheduled jobs, batch apex jobs can use the same methods, if necessary. But this approach is not without its caveats; for e.g. if the helper method slightly varies based on the event type, then how would you handle that? Do you pass the event type to the helper method, so that the helper method uses condition logic? There’s no right or wrong answer – but personally, I think it’s not a good idea to pass the event types to the helper methods; And for this example, you can just pass a flag and that can solve the issue. But for other types of situations, passing a flag may not be enough – you need to think little smarter and I’ll leave it to the reader as the situation is totally unique to their requirements.

Using the framework to handle updates

The framework comes with a default implementation to handle the updates. It achieves this by adding a Map variable to hold the objects to be updated and all that the developer needs to do is to just add the objects to be updated to this map in their event handlers. The TriggerHandlerBase abstract class has the default implementation to update the objects from this map variable which is called by the  ‘execute’ method in the TriggerDispatcherBase. Note that I chose to call the updateObjects method only for the ‘mainEntry’ and not for the ‘inProgressEntry’ simply because I didn’t have the time to test it.

Another thing to note is that since the framework will utilize the helper classes to get things done, sometimes the objects that you need to update may be handled in these helper classes. How would you handle that? I suggest design your helper methods to return those objects as a list and add them to the map from your event handler code, instead of passing the map variable to the helper class.

This framework can be easily extended to handle the inserts and deletes as well. To handle the insert, add a List variable to the TriggerHandlerBase and provide a virtual method named ‘insertObjects’ that will insert the list and call it from the ‘execute’ method on the TriggerHandlerBase. I’ll update the code when time permits and for the meanwhile, I’ll leave this to the reader to implement for their projects.

Note that it is not possible to do upsert in this way, because platform doesn’t support upserting generic objects and since our map uses the generic object (sObject), this is not possible. (Thanks to Adam Purkiss for pointing out this fact).


To illustrate this design pattern, the following diagram depicts how this architecture framework will apply for the account object.

Trigger Architecture Framework

Trigger Architecture Framework

The trigger architecture framework project is available as open source and hosted at google code.The source code for this entire framework, along with a dummy implementation which includes classes for handling trigger events on the Account object, is available as an app exchange package. If you need it as a zip file, then it can be downloaded from here. The code documentation is built using apexdoc and can be downloaded from here.


The new trigger architecture framework will provide a strong foundation on the platform to build applications that will provide multiple benefits as outlined previously. The framework does involve many parts and the business solutions built using this framework need to follow certain rules and conventions and the number of classes written will be little high – but the benefits from this approach will easily out-live the effort and the investments. It may be still overkill for very smaller implementations, but it will prove its usefulness for medium to larger implementations.

Integrating with Platform using DbAmp: Part-02: Basics

In the first part of the article series titled ‘Integrating with platform using DbAmp’, we got introduced to the DbAmp tool and its benefits. We also went through the fundamentals and learned how it works and components of the DbAmp. In this article, we’ll look at some basics of the DbAmp as this is very essential in developing the DbAmp Jobs to integrate with Salesforce. The official DbAmp documentation is an excellent source of information to learn about the basics and this article series will touch upon few topics that you can find in the documentation, but will not go into those topics in detail, as they have been already covered in that documentation.

Execution Types

When writing SQL queries to perform any CRUD operations against Salesforce, there are two ways a developer can choose to write them.

Direct Access:

The first method is to write the queries directly against the Salesforce. In this method, the developers use specific type of syntax to access the Salesforce objects and the queries are executed against the Salesforce in real-time. The format is described as follows:


For e.g. to select all records from the Account object, you simply execute the following command (assuming your linked server name is SFORCE)

   1: SELECT * FROM SFORCE...Account

This has few drawbacks; Firstly, the performance takes a big hit as it has to execute directly against the Salesforce. Secondly, this will have a big impact on the API call limit. The reason is if you use INSERT or UPDATE in this manner, every record that gets inserted or updated count against the API call.

Local Access:

With local access method, you first replicate the required objects and store it in your local SQL server database and you write your SQL query against this table for SELECT queries. For CUD queries, you create a load table (as explained in part-1). This method has couple of advantages: Firstly, the performance is much improved, because your SELECT queries run against local database and not against the Salesforce. Secondly, as a developer, you have the option of using bulk API. With bulk API, upto 10,000 records can be sent in one single call. Also, when using the SF_BULKOPS (without the bulk api option), DbAmp batches upto 200 records in one single call.

Object-Table representation

DbAmp supports both child to parent relationship queries and parent to child queries. When replicating the Salesforce objects from platform to the local SQL server database, DbAmp uses a special mechanism to flatten the Salesforce objects in the local SQL server database. Here is a list of things that happens when an object is replicated:

  • The DbAmp creates every table with a column titled ‘Id’ which is mapped to the Object Id. Note that Salesforce never displays the Id field in the object definition. You can check my earlier article on Object Id’s to learn more about it. DbAmp also makes this field as primary key.
  • For lookup fields, the columns are created with the same name and suffixed by ‘Id’. For e.g. the ‘CreatedBy’ field becomes ‘CreatedById’ column in the local table and DbAmp stores the record id of the record it is pointing to. For e.g when replicating Case object, the field ‘CreatedBy’ is created as ‘CreatedById’ column in the local Case object and if the case is created by the user ‘John Adams’ and if the ‘Id’ of the User object for this user is ‘005014A7CA434AC’, then the ‘CreatedById’ column in the local Case object will have the value of ‘005014A7CA434AC’.
  • When a parent object in a lookup or master-detail relationship is replicated, there will be one record for each of the record in the related list of the parent object. For e.g. If an Account object has 3 contacts, then there will be totally three account records. If there are multiple related related lists, then there will be one record for each of the record in each of the related list. For e.g. if an Account object has 2 contacts with 5 cases, then there will be totally seven account records.

Handling DateTime

The value for the datetime types are always represented as UTC in Salesforce. When the user sees a value for the DateTime field, the value is represented in the user’s timezone (configured in the user’s profile). When the DateTime fields in Salesforce are manipulated using DbAmp, it is converted into local timezone. This is handled by DbAmp automatically and users need not convert back and forth from UTC to local and local to UTC. This is a very important point to remember when dealing with DateTime fields.

Using Column Subset Views

The Salesforce API has a limit on 10,000 characters for the SELECT queries; hence objects with huge number of columns cannot be replicated or refreshed who cross this limit. The DbAmp documentation mentions that if field count is above 325, the API may return error. In my experience, we got this error when the field count went above 400. Anyways, DbAmp suggest that the object can be split and then queries and this is called as Column Subset Views. In this method, the developer writes a SELECT query with a specific form of syntax to split the objects. The syntax is as follows:

   1: SELECT * FROM <LinkedServerName>...<ObjectName>_ColumnSubset<StartingAlphabetPrefix><EndingAlphabetPrefix>

For e.g. to split the Case object into two tables, the following query can be issued.

   1: SELECT * FROM SFORCE...[Case]_ColumnSubsetAM


   3: SELECT * FROM SFORCE...[Case]_ColumnSubsetNZ


Note that the Case object is enclosed by square brackets. This is because, ‘Case’ is reserved keyword and any reserved keyword in the table/column names should be enclosed by square brackets. The above query will get all the records from the Case table and will create two separate tables, named ‘Case_SubsetAM’ which will contain all the columns which start with A to M, both inclusive and another table named ‘Case_SubsetNZ’ which will contain all the columns that starts with N to Z, both inclusive. The column subset view can be used with SF_REPLICATE stored procedure as well, as follows:

   1: EXEC SF_REPLICATE 'SFORCE', 'Case_ColumnSubsetAM'


   3: EXEC SF_REPLICATE 'SFORCE', 'Case_ColumnSubsetNZ'

Governer Limits

In order to work with Salesforce data, every tool in the market has to use the Salesforce API. There is no other shortcut or preferred treatment for certain vendors. To ensure fair usage, Salesforce restricts the resource usage by putting a limit on the API calls. The following list provides high level limits on the API:

  • SELECT/SF_REPLICATE/SF_REFRESH – 1000 records => 1 API call
  • UPDATE & INSERT – 1 record => 1 API call
  • SF_BULKOPS without bulkapi switch 1 API call for each batch of 200 records
  • SF_BULKOPS with bulkapi switch => 1 API call for each batch of 10,000 records


Understanding the fundamentals of DbAmp helps developer to write better queries against Salesforce. In this article we covered such fundamentals and in the next article we will explore how records can be retrieved from the Salesforce. For list of the articles in this article series, please check the first part of this article series.

Integrating with platform using DbAmp: Part-01: Introduction

The platform is becoming a ubiquitous platform of choice in the cloud for many organizations to move their Sales, Service and custom cloud applications. These applications or solutions cannot exist in vacuum and in almost all cases, they need to tightly integrate with the enterprise data that is housed local on-premise network or even with other cloud services such as Windows Azure or AWS. For e.g. if an organization implements a sales cloud, then they need their user information from their internal active directory to load in the Contact object, the customer lists from their SAP or other system for Account or Contact objects. Similarly custom applications might require data from the organization’s internal systems. Integration is a big concern for the organizations as they grow their presence in the cloud and it’s numerous tools and services are flooding the market to integrate with any systems.
This article series will cover one such tool called DbAmp from the The goal here is to demonstrate the capabilities of this tool and show how the common tasks such as Insert, Delete, Upsert, Update operations can be done using this tool to accomplish the integration between a SQL Server deployed in the on-premise network with the platform. All of these topics are discussed elaborately in the following articles:
  • Integrating with Platform using DbAmp: Part-01: Introduction
  • Integrating with Platform using DbAmp: Part-02: Basics
  • Integrating with Platform using DbAmp: Part-03: Insert
  • Integrating with Platform using DbAmp: Part-04: Update
  • Integrating with Platform using DbAmp: Part-05: Upsert
  • Integrating with Platform using DbAmp: Part-06: Delete
  • Integrating with Platform using DbAmp: Part-07: Using SSIS
  • Integrating with Platform using DbAmp: Part-08: SF_REPLICATE
  • Integrating with Platform using DbAmp: Part-09: SF_REFRESH
  • Integrating with Platform using DbAmp: Part-10: SF_BULKOPS
  • Integrating with Platform using DbAmp: Part-11: Under the hood
  • Integrating with Platform using DbAmp: Part-12: Common integration techniques
DbAmp is a simple, yet very powerful tool, that exposes the as another database to your SQL Server. It allows the developers to use their familiar SQL (and SOQL as part of Open Query) to do all the CRUD operations on the Salesforce objects. The data on the platform can be backed up completely to an on-premise SQL Server using DbAmp with very little programming.
Complete backup of data
DbAmp can be used to backup the data from the platform completely to an SQL Server database located on-premise. It is not limited to any specific Salesforce edition or application or objects; it can backup standard and custom objects and even custom settings for all types of cloud, such as Sales, Service, Marketing, Custom clouds, etc. However it is not a universal backup tool, meaning, it cannot backup other features such as profiles, permission sets, groups, roles, etc., because it is only a data backup tool. But it does support both full backup and incremental backup for the data. Full backups enable to completely backup all the objects with all the records, while incremental backups refreshes only the delta changes from to local on-premise database.
Build reports
DbAmp allows developers to build reports that can use data both from local repository and Salesforce in both real-time and offline fashion using simple SQL SELECT queries. For offline, DbAmp provides stored procedures (which will be covered shortly) to bring the data from platform to on-premise. Developers
Integrate local and cloud
DbAmp can be used to integrate local on-premise applications with data in both directions. That means that data can be inserted or upserted or deleted in from the on-premise SQL database.
How it works?
As outlined previously, DbAmp exposes as another database to your SQL Server. It does this by providing a OLE DB Provider to As you might know, the SQL Server can connect to other data sources (that can support OLE DB, such as another SQL Server or Oracle, and expose them as another database using Linked Server. The following diagram (taken from the above link) illustrates this idea:
This allows the developers to use the DbAmp OLE DB Provider to create a linked server that can connect to Once this linked server is created, now developers can use regular SQL queries to manipulate the data on the platform. Developers can also use SOQL directly with the SQL using the Open Query, but keep in mind that there are some limitations in using the SOQL with the Open Query. Check the DbAmp documentation for more details.  One important thing to highlight here is that DbAmp takes care of all governer limits, so a developer need not to worry about governer limit exceptions when he/she tries to upsert with 5000 rows/records. DbAmp even allows developer to specify the limit per operation so that the developer can precisely control how the data should be batched.
Now that we have understood how DbAmp works, let’s see the components of the DbAmp to better understand about how DbAmp works.
DbAmp OLE Db Provider
The DbAmp OLE Db Provider is used to connect to the by the linked server configuration. This is the engine of the DbAmp and takes care of all the background processes such as converting the SQL to appropriate web services API, sending the data from on-premise to and bringing the data back to on-premise from, etc.
DbAmp System Stored Procedures
The DbAmp system stored procedures does the bulk work to backup completely or incrementally. It also does the bulk inserts, deletes, updates and upserts. Here are those stored procedures.
  • SF_REPLICATE – The SF_Replicate stored procedure creates a new table which is an exact replica of a object and imports every single record from that object into the local, similarly named table. If this table already exists, it takes a temporary backup and drops and recreates this table.
  • SF_REFRESH – The SF_Refresh stored procedure synchronizes the local table with it’s corresponding Salesforce object. It deletes the records in the local table if these records got deleted in the Salesforce and gets any all the records that was added in Salesforce and doesn’t exist locally and the records whose fields got updated in the Salesforce.
  • SF_BULKOPS – The SF_Bulkops stored procedure is the key stored procedure that is used to send any new records or updated records in and even to delete any existing records.
There are few other stored procedures that ships with DbAmp, but these are the most widely used stored procedures. In a later article in this article series, we will explore these three stored procedures in depth to gain deep understanding about how it affects the way a developer designs/develops the integration job.
Anatomy of a DbAmp Job
Using DbAmp to integrate on-premise data with requires SQL skills, as the developers will write their integration entirely using SQL. The DbAmp integration can be either done using regular SQL jobs or SSIS packages. For the most part, this article series will be using only SQL jobs to implement the integration.
SQL Jobs
The anatomy of a SQL job that uses DbAmp slightly varies depending on the task involved and the time it runs. For uploads, we first refresh the local tables and populate a load table and then perform the upload operation (CUD). The load table is a temporary (physical, but temporary in nature) table that mimics the Salesforce object. The load table name should start with the name that is similar to the object that it deals; for e.g. to perform upload operation on ‘Case’ object, the load table name can be ‘Case_Load’ or ‘Case_BillingIssues_Load’, but it can’t be ‘ABC_Load’, etc. The load table need not have every column as in the Salesforce object and can have fields/columns that the job is going to affect. The only other column that is required is named ‘Error’ which will be used by the DbAmp to update the status information after the SF_BULKOPS call. The load table can be dropped or left as it is after the SF_BULKOPS call. In general, it’s a best practice to first check for its existence and create it if it doesn’t exist and to drop it just before the end of the logic in the SQL job.
At a high level the anatomy of a DbAmp SQL job look like as follows:
To upload data from local on-premise SQL database to platform for frequent jobs.
  • Refresh the local table(s) from the Salesforce objects using SF_REFRESH (or it’s other variants)
  • Create the load table
  • Populate the load table with the data that needs to be sent to Salesforce
  • Use SF_BULKOPS to upload the data. The upload operation can be ‘Insert’, ‘Update’, ‘Upsert’, ‘Delete’
  • Check ‘Error’ column in the load table to manage the error handling scenarios.
To upload data from local on-premise SQL database to platform for nightly jobs.
  • Replicate the local table(s) from the Salesforce objects using SF_REPLICATE (or it’s other variants)
  • Create the load table
  • Populate the load table with the data that needs to be sent to Salesforce
  • Use SF_BULKOPS to upload the data. The upload operation can be ‘Insert’, ‘Update’, ‘Upsert’, ‘Delete’.
  • Check ‘Error’ column in the load table to manage the error handling scenarios.
To upload data from platform to local on-premise SQL database for frequent jobs.
  • Refresh the local table from the Salesforce objects using SF_REFRESH (or it’s other variants)
  • Perform CUD (Create, Update, Delete) operation(s) on your other databases retrieving data from the refreshed table(s)
To upload data from local on-premise SQL database to platform for frequent jobs.
  • Replicate the local table(s) from the Salesforce objects using SF_REPLICATE (or it’s other variants)
  • Perform CUD operation(s) operation(s) on your other database retrieving data from the refreshed table(s).
This article introduced the DbAmp, a tool to integrate local on-premise databases with data and discussed about the benefits and the underlying mechanism of how it works. It also discussed the basic constructs of the DbAmp tool and the anatomy of a DbAmp SQL job. In the next part, we will explore how the data can be inserted from local SQL database to using a DbAmp SQL job.
Stay tuned.

Demystifying Object IDs in platform

The demystifying series will take a single concept, idea, technology, standard, feature and will dissect it and provide a thorough understanding about that particular thing.

In platform, the fundamental mechanism of storing data is through objects. Objects define the schema for storing related information for a particular ‘thing’. This ‘thing’ can be either standard objects such as Accounts, Contacts, Cases, Leads, Opportunities or custom objects that the users define to meet the business requirements. Objects can contain standard fields such as CreatedBy, LastModifiedBy and custom fields that the users define. A record is an instance of an object and every object can have zero or more records. Every record has a unique identifier represented by a standard field named ‘ID’. The subject of this blog post is all about this ‘ID’ and how it plays an important role in handling the objects and the relationships.

Note: I’m going to use ‘ID’ (upper case) to denote the ID type, while ‘Id’ (proper case) to denote that it is a value.


The ID field is defined automatically by the platform whenever an object is created. The ID field is indexed and it is never shown in the field definition, hence this field cannot be edited or deleted. The ID field acts like a primary key for each record, hence every record can be uniquely identified using an ID. The value of this field for a record cannot be updated either through UI or through the web services. The IDs are unique within an organization and they are represented as either 15 digits or 18 digits. The 15 digit ID is base-62 encoded and is case sensitive and contains alpha-numeric characters (0-9,a-z,A-Z). The 18 digit ID is base-62 encoded and is case-insensitive and contains alpha-numeric characters (0-9,a-z,A-Z). The 18 digit IDs are introduced since some applications like Microsoft Access/Visual Basic are case-insensitive and do not distinguish between 00B30000007Mlka and 00B30000007MLKA.

The first 3 characters of an ID indicates what type of entity it references. For e.g. the following table list the first 3 characters for various entities:

# Entity Type Starts With
1 Groups/Queues 00G
2 Profiles/Roles 00E
3 Permission Sets 0PS
4 Object Records

Starts with a alphanumeric such as 5003, 0010.

The Salesforce UI always uses 15 digit IDs when viewing/editing records of the objects, users, roles, groups, profiles and permission sets, while web services always use 18 digit IDs. To get the 15 digit IDs from 18 digit, simply get the first 15 digits and ignore the last 3 digits. Salesforce recommends to use 18 digit IDs as the web service API’s always use 18 digits.

IDs in Relationship

The IDs are used as references field types in relationship fields. This means that when a lookup or master-detail relationship field is defined, it uses the ID to reference to the lookup or the master object that it refers to. There are subtle differences how the Ids are used in the ID fields and the reference fields.

# ID Type Fields Reference Fields
1 The Ids are generated by the platform and the user/developer cannot update the ID field The reference fields can be updated with the Ids from other objects
2 The ID fields are not displayed in the Field definitions The reference fields are always displayed in the field definitions
3 The Id of a record is always unique within an object. For e.g. every case record will have a unique id. The Ids in the reference fields are not unique. For e.g. the Last Modified By field can have the same user id
4 An object cannot have a null Id The reference fields in an object can be null for lookup objects, but in master-detail objects, the Id cannot be null

Relationship fields such as lookup or master-detail relationship fields are always defined in the child. When the user creates a new record, the platform automatically provides a lookup icon next to the field to choose the value from the related object. When the user select a value from the lookup dialog window and saves it, the platform saves a ‘reference‘ in the child object to the related object. This ‘reference‘ is the ‘Id‘ from the related object. But when you view this record, you would see the lookup value which is the record name (defined by the ‘Record Name’ during the object creation) and not the related object’s id.

Let’s understand how this works with an example. I’m going to use DbAmp for this example. DbAmp is an integration tool that exposes Salesforce as another database in your SQL Server. The advantage with this approach is that you can use regular SQL (not SOQL) to to all your CRUD operations. I’m not going to show how to setup the DbAmp as this is not in scope and the assumption here is it is already setup and ready for use.

For this demonstration, I created a new object named ‘Position’. You can see that the Position object definition screen doesn’t have the ID field. Also, note the standard lookup fields that are created for this object. These fields such as ‘Created By’, ‘Last Modified By’ are lookup fields. We will cover these fields shortly.


Image 1: Position object definition

The following screenshot shows how the position record looks like when it is created. You can see that the ID field is never displayed in the UI (because it is not part of the field definition), but the URL has the Id which uniquely indicates this particular record. Actually, Salesforce always uses Id in the URLs to access the records for both viewing and editing. The highlighted fields indicate the lookup fields and in this case these fields are standard fields which gets created automatically when an object is created and populated by the platform every time a record is created.


Image 2: A position record seen through UI

Let’s switch back to SSMS to see how this record looks like.


Image 3: A position record seen through DbAmp

As previously said, DbAmp allows to manipulate records using regular SQL and the above command gets all records from the Position__c table (mapped to the Salesforce object Position__c). If you compare the image 1 and 3, you can find couple of differences. First, there is a difference in the field names; for e.g. Image 1 shows the field name as ‘Created By’ and ‘Last Modified By’, while the Image 2 shows it as ‘CreatedById’ and ‘LastModifiedById’. The reason is that every field has 2 fields to represent the field name;

  • Field Label – used in the page layouts
  • Field Name/API Name – used by the web services.

The standard fields use the ‘Field Name’ convention, while the custom fields use the ‘API Name’ convention. Nevertheless, the reason the Image 3 shows these fields as ‘CreatedById’ and ‘LastMofidiedById’ is because, DbAmp knows these fields are lookup fields, hence it appends the ‘Id’ to the column name.  Second, in the UI, the Id is 15 digits (which is displayed in the URL), but in DbAmp, the Id is 18 digits. The reason is that the web services always use 18 digit and DbAmp uses the web services behind the scenes to convert the SQL to web service API call to perform the CRUD operations. The point to note here is that the relationship fields use Ids as reference to link other objects and these Ids can be updated in the relationship fields, while the ID field cannot be updated.


IDs play a crucial role in the CRUD operations. In fact, most CRUD operations use IDs in one way or another. The following sections details how the CRUD operations use IDs.


As explained previously, whenever a record is created, the ‘ID’ field is auto-populated by the platform and the user has no way to update or delete. A record in Salesforce object cannot exist without an ID and it is always unique within the organization.

  • Create through Web Services: When the Create() method in the web service is called, it creates a new record and returns the 18 digit ID that the platform creates as part of the record creation.
  • Create through UI: When a new record is created through the Salesforce UI, it always displays the 15 digit ID.


Upsert either creates new record if it doesn’t exist or updates if it exists. The upsert() call can be used in custom objects only if the object has an external field. When upsert() method is called, it uses the ID field to see if a record already exists. If it exists, then it updates; otherwise, it creates a new record and returns the 18 digit Id from the newly created record.


Update can accept either the ID field or any other field in the condition list. But it cannot accept the ID field in the updateable list. If one or more Id is listed in the condition, the same number of records are updated, while if a non-Id is used in the condition list, it may update 0 or more number of records depending on how many records matches the condition.

  • Update through web services: Certain fields of an object (both in standard & custom objects) can never be updated such as ‘Created By’, ‘Last Modified By’, ‘Last Modified Date’, etc. The ID field is one such field. However, the reference (lookup & master-detail relationship) fields can be updated such as ‘OwnerId’.
  • Update through UI: Similar to web services, certain fields cannot be updated including the ID field, while the reference (lookup & master-detail relationship) fields can be updated.


Delete can accept either the ID filed or any other field in the condition list. If one or more Id is listed in the condition, the same number of records are deleted, while if a non-Id is used in the condition list, it may delete 0 or more number of records depending on how many records matches the condition.


The concept of ID is very simple, but not effectively understood many times. Understanding how the IDs are used in the platform helps developers to design and write applications in platform effectively.

%d bloggers like this: