NEW RELEASE: SPRING’14 RELEASE NOTES
Here is a tool I’ve been working on for the past two weekends that helps address a current platform limitation around rollup summaries. Specifically the inability to do rollup summaries between lookup relationships. This is possible between master detail relationships using the declarative mode of Force.com, but not between lookup relationships.
A while back I came across a rollup Apex library (LREngine) written by a fellow Force.com MVP (Abhinav Gupta). Which helps reduce the coding effort significantly and uses SOQL Aggregate queries internally. However it requires some developer skills to use the library and deploy the necessary triggers to your org, as well as repeat of this to make changes in the future. Not very declarative I thought.
So if you’ve been following my blog you’ll know that I’ve been doing things in Apex with the Metadata API (used for, amongst other things, deploying code!). So in order to make the LREngine library more accessible to admins without access to developers or coding skills. I decided to build this tool around the library. Which automates the generation of the required ‘very small’ Apex Trigger (and test code) to allow you to deploy it direct from your org, without any change sets, developer orgs or Eclipse install insight!
The tool revolves around the use of a single object, Lookup Rollup Summary. This objects lets the admin declaratively define the rollup definitions! The following rollup definition updates the Annual Revenue on the Account, each time an Opportunity related to it is inserted, updated or deleted. As an optional feature, a SOQL WHERE clause can be specified that acts as a kind of Rollup filter criteria should you need it. In this case only Opportunities greater than 200 are included in the rollup.
Activating the Rollups
This release of the tool supports the Realtime calculation mode, as the users manipulate the child records the tool automatically updates the rollup fields on the parent object. To activate rollups using this mode you must click the Manage Child Trigger button.
This will present a UI that will allow you to easily deploy or undeploy the required Apex Trigger directly from the page, the page also shows the minimal code (most of the generic logic is in the managed package) for confirmation purposes.
Note: In a future release a Scheduled calculation mode will be supported, here no triggers need to be managed.
Installing the Tool
The tool is available via a managed package to install into your production environment (sandbox testing always recommended however). I’ve also shared the whole code for it in my Github account here, should you want to fork it and/or combine it in other solutions directly. These are the steps to install the current package.
- Install the packaged version by reviewing the release details here.
- Add a Remote Site setting for the Metadata API callouts the tool makes,
- Setup > Security > Remote Site Settings
- Use the following URL, https://dlrs.eu2.visual.force.com (adjusting the instance part accordingly).
- Note this URL endpoint points back to your Salesforce server, as even callouts out and back into Salesforce servers needed to be defined here.
- Locate the Lookup Rollup Summaries tab and create a record as above (don’t check Active yet)
- Click the Manage Child Trigger button and click Deploy.
- Edit the record you created in step 3 and check the Active field
- Provide feedback via the Github Issue tracking facility here.
Whats next… upcoming Features!
I’ve got the following features in the pipeline for the tool, if anybody wants to help me please reach out!
- Schedule calculation mode, if you don’t need realtime updates of your rollups, a schedule job will periodically update them for you. This calculation mode does not even require a trigger to be deployed.
- Calculate button, if you add a rollup for existing child records or make some changes, this button will bring your rollup values up to date on the parent objects.
- API, by providing an API, then developers who may already be writing triggers on the related objects, can call out to the engine and still leave the admin the ability to configure the rollups declaratively.
- Further robustness improvements, better run time checking of fields defined on the rollup definition (in the case where these get renamed or deleted after the rollup was defined).
April 15, 2021 at 1:22 pm
Is there a resource list of Relationship Criteria options that can be used (as it relates to Relative dates)?
I need to Sum invoice totals from the last 12 months. We use the rollup to do YTD already and the Relationship Criteria is: Posting_Date__c = THIS_YEAR. What can I use so we can get LAST 12 MONTHS?
(I would even settle for Last 365 Days, if needed)
April 16, 2021 at 2:28 pm
Try this https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_sosl_where.htm – also it’s worth posting on the community channel for this tool – listed on the readme page on the repo
April 19, 2021 at 11:40 am
Thanks Andy for the reply. I used your link and navigated to the SOQL option instead of SOSL that was linked and found the answer here:
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm
(Figured I’d link it in case anyone else runs into the issue.) I needed to use the Relationship Criteria of POSTING DATE__c = LAST_n_MONTHS:12
Appreciated you pointing me in the right direction!
October 28, 2022 at 8:51 am
Hi – our organization is have issues with the DLRS tool not calculating correctly and on many occasions not at all, at which point we have to click the calculate button manually to get it to calculate.
I did not see any sort of support page, so I am hoping you can reach out and help us resolve this. This internal case has become high priority.
December 8, 2022 at 6:37 pm
Support is community driven as this is an open source initiative. Check out the chatter group link in the readme file of the repo