Note: This article was originally published on developer.salesforce.com in 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 six-year-old platform feature itch. By the way, 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!

The reality is this gap is being filled through Apex developers writing specific Apex Trigger code for the desired objects and fields utilizing SOQL Aggregate queries (ideally) to grab and recalculate the summarized 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.

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 shared 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 six years? Well first of all, I’d like come clean and say, I doubt that you went through 200+ comments on that idea — all requirements would be possible with this package. However, so far, it’s 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.

So they say there is no escaping reality and sure enough, as per the current workaround 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 realized the same gap and provided a great Apex library for helping developers write Apex Trigger code to implement roll ups.

His library wraps the use of SOQL Aggregate queries to allow the Trigger developer to more easily express 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.

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 following SOQL. The method then returns the master (Account) records with the updated rolled up values within it. The caller is responsible for updating.

“So hold on a second!” I hear you say! Are we not back to coding here? What happened to “clicks, not code”? 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 is 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. It is basically a programmatic means of accomplishing most of what you see under the Setup menu and some things you can not, 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 utilized 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).

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 a byproduct 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: While this feels like a hack or some kind of bypass 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. 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 that Salesforce whitelist 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 it’s quite small. It would also allow developers to copy and 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.

The name of the Apex Trigger and Apex test class are prefixed with the package namespace, to avoid colliding with others that might exist 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.

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 that, 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 behavior 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 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 closest 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’s 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 its 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.

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.). 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

Let’s take a deeper look at what the one line Apex Trigger above is doing behind the scenes. Let’s see 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 utilizes Apex Enterprise Patterns, hence those familiar with these patterns will recognize service, domain and selector classes in the code and UML diagram below (generated by Apex UML) showing the main Apex classes in the solution.

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.

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

The next thing it does, before calling LREngine, is for Trigger.isUpdate events 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, albeit 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 dropdown 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, 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 inspire such other tools to be created. While thinking about your next “Declarative 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, and Visualforce pages, as well as update things like Picklists and Layouts.

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