A Declarative Rollup Summary Tool for Force.com Lookup Relationships

by Andrew Fawcett - May, 2014


I’ve recently been asked to share the motivation and vision that emerged around an open source package I’ve been developing over the last year or so. It all started with a bunch of code and an API looking for a problem to solve to be honest! Here I will take you through the journey that led to the Declarative Rollup Summary Tool being born and ended up helping to fill a 6 year old platform feature itch. Relating to the desire to roll up values between records related via standard Lookup fields and not just Master-Detail (where platform support already exists), the related Salesforce Idea has over 20k upvotes btw!

The reality is this gap is being filled through Apex Developers writing specific Apex Trigger code for the desired objects and fields utilising SOQL Aggregate queries (ideally) to grab and recalculate the summarised values as the child records are updated. Then cracking open the same Apex Trigger code to repeat the process for other objects and/or making subsequent changes to the fields or criteria as requirements change. So the traditional approach to this problem carries some overhead in terms of skills and process and does not allow for easy adaption of the implementation once its in place, compared to the way editing a standard roll up summary would declaratively.

Figure1 rollup.png

As I have discovered through FinancialForce.com’s utilization of its ClickLink package, putting more declarative power into the hands of not just developers, but business process owners, solution architects, admins and consultants makes for a better collaboration and resulting solution. Such an approach empowers more people to not only define the requirements, but be part of creating the solutions! The idea of applications that enable this beyond the declarative capabilities of the platform, is something i’ve share before here, Declarative Thinking: Apps to build Apps.

So you're probably wondering at this point, how did I manage to do something declaratively that Salesforce has not done so far for for 6 years? Well first of all I’d like come clean and say, I doubt if you went through 200+ comments on that idea, all requires would be possible with this package, however so far, its had a pretty good success rate! So functional caveats aside, I must now let you into a secret as to how it’s done behind the scenes…. Behind the Scenes

So they say their is no escaping reality! And sure enough, as per the current work around to this platform gap, code is indeed involved! So the little secret here, is that clicks not code requires code! In this case code that leverages an excellent open source library by fellow MVP Abhinav Gupta, who back in 2012, had realised the same gap as and provided a great Apex library for helping developers write Apex Trigger code to implement rollups.

His library wraps the use of SOQL Aggregate queries to allow the Trigger developer to more easily expressed a rollup requirement in a similar way as to the standard roll ups available on master-detail relationships, but of course in code. Since learning about this i had been itching to give the LREngine (Lookup Relationship Engine) library a go. The following is an example that rolls up Opportunity Amount into Annual Revenue on Account.

Opportunity[] objects = Trigger.isDelete ? Trigger.old : Trigger.new;   
LREngine.Context ctx = 
  new LREngine.Context(
    Account.SobjectType, // parent object
    Opportunity.SobjectType,  // child object
    Schema.SObjectType.Opportunity.fields.AccountId); // relationship field name    
ctx.add(
  new LREngine.RollupSummaryField(
    Schema.SObjectType.Account.fields.AnnualRevenue,
    Schema.SObjectType.Opportunity.fields.Amount,
    LREngine.RollupOperation.Sum)); 
Sobject[] masters = LREngine.rollUp(ctx, objects);
update masters; 

What happens in the LREngine.rollup method is the use of Dynamic SOQL, or more specifically Dynamic SOQL Aggregate queries. The above code results in the creation and execution of the the following SOQL, the method then returns the master (Account) records with the updated rolled up values within it, the caller is responsible for updating.

SELECT AccountId, Sum(Amount) lre0 FROM Opportunity WHERE AccountId in :masterIds  GROUP BY AccountId

“So hold on a second!” i hear you say! Are we not back to coding here? What happened to ClicksNotCode? The above requires a developer to make use of this Apex library, write the Apex Trigger code and Apex Test code then deploy everything using Changesets, Eclipse, Ant etc... into my production organization? Then do the same next time i have a new requirement? How can we turn this around to something that be made more generic and accessible via clicks?

Dynamic Code Deployment

The magic moment for me was when i started to break ground on another open source project, the Apex Metadata API. For those of you not familiar, there exists a Salesforce provided SOAP API called the Metadata API, that is basically a programmatic means of accomplishing most of what you see under the Setup menu and some things you cannot, such as deploying code into a Production org. It is used by a number of tools such as Force.com IDE and Migration Toolkit (Ant). It is normally not available via Apex, however I had managed to get it working reliably from Apex with some added bits and pieces.

So by making the Metadata API available through Apex it could be utilised by a Visualforce page controller in the package through which the required Apex Trigger code and accompanying Apex Test code could be automatically generated and deployed for the user without any need for a sandbox, change sets or development tools, it’s one click and they are done! To remove the Apex Trigger the same page is used and the option to remove is displayed (of course it can also be removed using conventional means as well). This page is accessed via a Custom Button on a custom object used to configure the rollups (more on this later).

Figure2 rollup.png


You can view the full source code of the Visualforce page mangetrigger and controller RollupController from the repository to understand how this is accomplished, as well as more general demonstration of using the Metadata API deploy operation from Apex in the README for the library. As by-product of developing this i also created a native Zip and Unzip Visualforce Component (since the deploy operation takes the metadata to deploy in the form of a zip file), see apex-zip.

Note: That while this feels like a hack or some kind of by pass to get code into a production environment, it is not. It uses the sample API, with the same constraints, checks and security the development tools use. It’s true though its utilization in my view has to be more carefully considered in terms of disclosure to the user what’s going on though. If you also wondering if this would pass the Salesforce Security Review, the answer is also yes, and why would it not, it’s an Apex HTTP callout to a robust API from Salesforce! Post installation does also require the usual Remote Site setting configuration to permit ironically a callout which results in a return trip back to the same Salesforce servers (must raise an Idea to suggest Salesforce white list these!).

Generic Apex Triggers

Before calling the Metadata API’s deploy operation to push the code into the org, the Manage Child Trigger button displays a confirmation page, which previews the generated code. I felt this was appropriate as even though its being generated it is important for the user to see what is going into their production org, even though its quite small. It would also allow developers to copy paste it if they wanted to edit it or add it to existing Apex Triggers (a mode the package also supports). Once the user is happy they just press Deploy and wait for the confirmation the code has been deployed!

Figure3 rollup.png

The name of the Apex Trigger and Apex test class are prefixed with the package namespace, to avoid colliding with others that might existing in the org already. The other thing you will notice in the above screenshot is the Apex Trigger is very small, covers all the events and passes no parameters to the triggerHandler method!?!

/**
 * Auto Generated and Deployed by the Declarative Lookup Rollup Summaries Tool
 * package (dlrs)
 **/
trigger dlrs_OpportunityTrigger on Opportunity
    (before delete, before insert, before update, after delete, 
     after insert, after undelete, after update)
{
    dlrs.RollupService.triggerHandler();
}

Yet if you review the code required to invoke the LREngine library it requires passing the configuration of the roll-up, the parent and child objects, the relationship fields and the fields to aggregate etc. This is more than one line of code, still small and very elegant actually, but more than i wanted to include in each and every Apex Trigger deployed through this page. Plus I had another place for this rollup configuration in mind!

The generated Apex Trigger code immediately delegates to the dlrs.RollupService.triggerHandler Apex method included in the package. This packaged code does not need to worry about code coverage or tests within the production org, as this has already been dealt with during package creation and upload. Finally note that the generated Apex Test is most definitely best practice, but does the trick in terms of permitting the trigger to be deployed, as per the comment included there is no real behaviour to be tested in the generated trigger anyway.

Storing Configuration in Custom Objects

While Abhinav has done a great job at making the LREngine library easy to configure, it still requires coding skills. So once i’d confirmed Apex Triggers could be automated and deployed, i set about expressing the configuration of the roll up in the form of a Custom Object, which is the nearest i could get to in terms of Setup style configuration. Conceptually this is not your typical record data as in Accounts, Invoices or Opportunities, it a kind of “custom metadata” stored in Custom Objects. What would be ideal at this stage is a way for us to create custom metadata on the platform, maybe one day…

The screenshot below shows the Lookup Rollup Summary custom object and it’s various fields that allow the user to create a given rollup between a parent object and a child object. There are required fields to define the relationship field, the field to aggregate and target field on the parent to store the calculated value. Optionally it offers a field to express criteria to filter the rollup calculation based on child object fields. The original blog announcing the tool goes into these fields and small video demo in more detail.

Figure4 rollup.png

Note: The above fields require the user to have knowledge of the object and field API names (not the labels). One thing i had decided at this point was that i was accepting that this approach may cause some friction to users not familiar with API names vs field labels (a gap that is now being filled by community contributor, more on this later!).

The benefit of storing the rollup configuration in a Custom Object record like this, is not only is it more accessible than editing Apex code or some kind of other text based expression. It also doesn’t require the Apex Trigger to be touched ever again after initial deployment nor as new improvements are made to the underlying engine within the package, making upgrades a simple matter of installing a new version of the package. Basically i wanted to keep the Apex Trigger as dumb as possible and i think i succeeded!

NOTE: I did consider using Custom Settings to store the rollup information, however i wanted to apply validation rules to the fields entered (checking the API names existing etc.). And Custom Settings do not permit Apex Triggers. Otherwise their ability to be cached and read free of charge would make them ideal “custom metadata” storage!

The result is the user is free now to effectively edit or create as many roll ups around the child object as they like and have the results take effect immediately. Note that they can only Activate the rollup if the related Apex Trigger has been deployed (via the Manage Child Trigger button as described above).

Wrapping the LREngine Library

Lets take a deeper look at what the one line Apex Trigger above is doing behind the scenes. How it resolves the appropriate rollup contexts that need to be passed to the LREngine and some of the additional smarts used to group multiple rollups into a single call and also analyze the records and fields being changed to avoid needles calls and SOQL queries.

This tool utilises Apex Enterprise Patterns, hence those familiar with these patterns will recognise service, domain and selector classes in the code and UML diagram below (generated by Apex UML) showing the main Apex classes in the solution.

Figure5 rollup.png

The RollupService is the heart of the tool and is wrapping the LREngine, the service class exposes some entry points permitting Apex Triggers and the tools various Batch Apex and Schedule jobs to invoke the engine also. The first thing the triggerHandler entry point does is use the Id.getSObjectType method on the first entry in applicable Trigger.old or Trigger.new lists.

List<SObject> childRecords = Trigger.isDelete ? Trigger.old : Trigger.new;
SObjectType childObjectType = childRecords[0].Id.getSObjectType();

Once it knows the SObjectType it proceeds to query the Lookup Rollup Summary object for one or more rollups defined by the user. Thats right, it will handle multiple rollups from one Trigger, no need to deploy multiple Apex Triggers for each roll up.

Schema.DescribeSObjectResult childRecordDescribe = childObjectType.getDescribe();
List<LookupRollupSummary__c> lookups =
  new RollupSummariesSelector().selectActiveByChildObject(
     new List<RollupSummaries.CalculationMode> {
        RollupSummaries.CalculationMode.Realtime,
        RollupSummaries.CalculationMode.Scheduled }, 
     new Set<String> { childRecordDescribe.getName() });

The next thing it does, before calling LREngine, is for Trigger.isUpdate events, is to scan the childRecords list to determine if any of the fields being aggregated or one of the fields used in the lookup criteria have actually changed? If not, there is no point calling the LREngine and wasting CPU and SOQL query resources, so it exits immediately!

Once it has been determined there has been a change to the child records that requires the rollup to be calculated, the LREngine contexts are created and the engine called. As final optimization the code will try to minimize the number of calls to the LREngine, for example if two rollup summary definitions have been found and both have the same filter criteria (or none) one call will be made, resulting in a single SOQL Aggregate query. However if the filter criteria differs, this effectively means a different WHERE clause, and as such separate engine invocations are needed and thus separate SOQL Aggregate queries.

The result is the most efficient use of Aggregate SOQL possible! Though at the end of the day the Apex 50k limit of rows still applies, which is shared between multiple rollup executions if you have multiple ones configured and are not sharing the same SOQL Aggregate query, as above. For this reason and query performance in some cases, i introduced a Scheduled mode to help process each rollup with the maximum amount of resources possible, all be it at the expense of real-time mode. If you are interested in learning more about some of the other features of this tool, such as the Scheduled or Developer modes it supports, take a look at the documentation section of README file.

Social Coding in Action!

This project is a great example of the compound benefits of open source and social coding. Taking an existing library, repurposing it and combining it within another open source initiative. I have also made some improvements to the LREngine, which are available in the tools repo and which i have now submitted back to the original repo.

Lately I was very pleased to see another community member engage with the project and provide a usability boost to the creation and editing of the rollup definitions, through an excellent Visualforce page that allows the user to pick objects and fields based on their label name from pre-filtered drop down boxes! Wes has also started sharing his ideas on improving the usability of the roll up filter criteria (currently a text box!) here. I’m personally very proud to be part of such a community and cannot wait to see where things go next!

Summary

Apex, SOQL and SOSL are powerful tools and while not all use cases they are involved in can be generalized in the way I’ve done with this tool. I hope this article has helped give some inspiration for other such tools to be created. While thinking about your next “Declarate Salesforce App” keep in mind the Metadata API provides a lot more automation and management potential than just Apex code, it can create Custom Objects, Fields, Visualforce pages, as well as update things like Picklists and Layouts.

So talk to the admins in the community and find out what areas of the platform they are feeling pains in, if it can and/or has already been solved by Apex code, and if it could be generalized maybe you can create a more declarative solution for them!