Andy in the Cloud

From BBC Basic to Force.com and beyond…

New Release: Spring’14 Declarative Rollup Summary Tool

88 Comments

blog_chart1Well it has been under development for few weekends now, but I’m finally announcing the latest release of this tool. But before I go into the details of installing it and whats in it, i want to thank everyone who has shared feedback and encouragement with me via tweets, GitHub and comments on the original post, all of which has been a huge motivation for me!

 

This release completes the feature set i set out in my original post as well as some bug fixes.

  • New Feature: Scheduled calculation mode (alternative to the existing Realtime mode)
  • New Feature: Calculate button to run a full rollup calculation on existing data
  • New Feature: Developer API, for embedding calculations in existing Apex Triggers
  • Bug Fix: Realtime mode, improved optimisation to monitor rollup criteria fields and re-parenting

The tool now also has its own Application in the drop down containing the original tab and a new one.

RollupToolSpring14

New Feature : Scheduled Calculation Mode

For those that prefer to differ the lookup calculations, perhaps for performance or governor reasons (see below), you can now choose Scheduled from the Calculation Mode field. This requires a small Apex Trigger as per the previous release, it’s super easy, no developers required, just click the Manage Child Trigger button.  Then simply schedule the job using the standard Schedule Apex button under Setup then Apex Classes, the Apex Class is RollupJob.

Once this mode is enabled, you will see the new related list, Lookup Rollup Summary Schedule Items (as shown above) will fill up as child records are added, deleted or updated (only when lookup referenced fields are modified) with a list of parent records that are effectively queued up ready for the scheduled job to process. Once processed successfully they will be removed automatically.

If there are any errors (other triggers, validation rules etc preventing the master records being updated) you will see these in the new tab Lookup Rollup Summary Logs. Once you correct these you can remove the log entry or wait for the next scheduled execution and the log entry will automatically be removed for the effected parent record.

ScheduledErrors

Tip: Add a Workflow Email Action to this object if you want to monitor log entries against certain objects.

New Feature: Calculation of existing Records

If you implement this tool and there are existing records in your objects, you can now use the Calculate button to start a background job to retrospectively calculate the rollups (time this accordingly to your user activity). Its also useful if your changing the rollup criteria or have deactivated and are now reactivating your rollup for some reason.

RunCalculateThe button will start a job to select ALL of the records in the parent object (due to platform restrictions applying the child filter criteria), however the rollup criteria will still be applied as the rollups are calculated. If there are any issues during this process the above Lookup Rollup Summary Logs tab will list these errors, as with the scheduled mode, correct the errors and delete the log entries manually or rerun the calculate process.

New Feature: Developer API

RollupAPIIf you have developers writing Apex Triggers or other Apex processes where you would like the rollups recalculated, you can have them call the Developer API. Simply select Developer from the Calculation Mode field and any lookups that are related to the child records passed into the API will be calculated automatically (the developer does not need to know or ask you for the rollups themselves, meaning you can continue to declaratively add or update them afterwards).

The main API, is the rollup method as shown below. Though there is also API’s to invoke the Calculate and Schedule jobs. The triggerHandler method is really for use by the Apex Triggers generated by the Manage Child Triggers button. Though if your in a Apex Trigger context it will work equally well from your triggers also.

List<Opportunity> opportunities =
    [select AccountId from Opportunity where Id in :myOpportunityIds];
List<SObject> parents = (List<SObject>) dlrs.RollupService.rollup(opportunities);
update parents;

NOTE: As minimum the relationship fields must have been included in the query before passing the children to the API. Also keep in mind if you have multiple parent rollups the list of SObject’s returned will be a mix of SObject types.

Advanced Relationship Criteria Handling

CriteriaFieldsAs an optimisation the tool will only attempt to recalculate rollups if relevant fields (such as those in the Field to Aggregate or Relationship Field fields) have been modified by the user (or if a child record is added or deleted). In this new release if references to other fields are made in the Relationship Criteria field, such as the example shown here, you must also ensure to specify the field/s in the Relationship Criteria Fields field. So the tool can also monitor these fields as being changed by the user. In the example used in this blog it means that if the AccountId, Amount or the StageName fields are changed on the Opportunity by the user, the Account rollup will be recalculated (this applies to Realtime and Scheduled modes).

Installation and Upgrade

As before the full source code for the tool is open source and available via the GitHub project, however if you have installed via the previous managed package you can now upgrade to obtain the new features above. All installation links and release history can be found here. There are some post installation steps to enable some new components. If your installing for the first time you can skip these.

  1. Add the Calculate button to the layout
  2. Add the Relationship Criteria Fields and Calculate Job Id fields to the layout (as shown above)
  3. Add the Lookup Rollup Summary Schedule Items related list to the layout (as shown above, remove the New button and make Parent Id and Parent Record fields visible)
  4. Depending on the security options you took during install, you may need to enable the Declarative Lookup Rollup Summaries application and Lookup Rollup Summaries Logs tab on relevant profiles / permission sets.

 

Dealing with the 50k Aggregate Query Limit

If you hit this limit its likely due to the fact that you have a LOT of child records related to  some or an average most of your parent records your rolling up on. Another reason, especially if your hitting this in Realtime mode, it maybe because you have to many rollups defined to one parent (though the tool attempts to merge rollup calculations, its not always possible). In this case switching to Scheduled mode for those effected lookups is the next thing to try.

If your already running in Scheduled mode or having a governor issue with the Calculate facility, you can try reducing the number of parent records processed at a time by these jobs. For this you will find under Custom Settings, the Declarative Rollup Summary Tool settings, the default for both the scope size settings is 100 parent records, try adjusting it downwards, the jobs may take longer (more chunks to process) as a result but they will stand a better chance of completing within the current platform query governor.

Feedback and Issues

Feedback, ideas etc via comments below most welcome, please report any issues via the GitHub issues page.

88 thoughts on “New Release: Spring’14 Declarative Rollup Summary Tool

  1. Pingback: New Tool : Declarative Rollups for Lookups! | Andy in the Cloud

  2. One question: Will this tool allow you to create rollups selecting objects with Master Detail as well?
    The reason for the question is SF limits the number of rollup summaries on Master Detail relationships and I’m wondering if using this tool could be a good workaround for this. Also are there limits to the number of rollup fields we can create on lookup relationships?

    Thanks a million for this tool. I’ve spread the word about it in several SF LinkedIn groups.

    AFB

  3. Hi Andrew,

    I just used your application to update 42,887 case records and it work flawlessly! thanks for sharing your knowledge and code. I’m going to put lookup rollup helper to use in other places as well.

  4. Amazing work! Thanks again for your contributions and being so responsive to the comments here/github. A fine example of the way social coding is supposed to work!

  5. Hi,
    For unknown reason, I cannot deploy to install the Apex Trigger and Apex Class. Getting this error:
    Visualforce Error
    System.CalloutException: IO Exception: Unauthorized endpoint, please check Setup->Security->Remote site settings. endpoint = https://contexte–dlrs.na10.visual.force.com/services/Soap/m/28.0
    Erreur dans l’expression ‘{!deployZip}’ dans page dlrs:managetrigger

    I double checked, and do have the remote site settings authorized for my instance.

  6. Do you have any tips or tricks to calculate for example the number of opportunities in the complete hierarchy of an account? Or is this not possible?

    • I’ve seen others do a rollup from Account to Account by using ParentId as the relationship field. This roles up to each level. You could then add a rollup from Account to Opportunity.

      • Hi Andy

        I assume given that you can do rollups from Account to Account, you could similarly do rollups from child opportunities to a parent opportunity? If so, would the lookup relationship field be Parent_Opportunity? Am trying that but doesn’t seem to be working. Thanks!

        –Alex S.

      • Yes hierarchies can be made to work, check out my blog post on accoutnt hierarchy as a basis

  7. Thanks! I will give it a try

  8. Hi Andy,

    Is it possible to use this tool to do a Distinct Count based on a field on the child record? Was a post on the reports group in the success community this week that was looking to do this, which would need a lot of ROS fields otherwise.

    The use case was opps and products, and a custom field on products essentially functioning as a product family. They wanted a distinct count of the products in each family on the opp.

    thx!

  9. Hi Andy,

    Since I activated the rollup on contacts, my users aren’t able to add new contacts to accounts they don’t own.
    See this message: http://i.imgur.com/5KXGTp1.png

    When I deactivate the rollup they can add new contacts to accounts they don’t own.

  10. Andrew, love the tool! Real expediter for me. Question I have looked and been unable to find an answer for –
    one the Relationship Criteria – how would I enter a Contains type filter for a multi-picklist?
    thanks!

  11. Hi Andrew,

    Thanks a million for this powerful tool.

    Juste one question : can we create 2 rollup to the same couple of object/relation ?

    If we have a Parent Object ‘Case’ and a Child Object ‘object__c’,
    We can create aggregate a number of object__c to case : how much records of object are open…
    AND can we create a another aggregate to a number of records close?

    That’s possible?

  12. How do you list multiple criteria in the Relationship Criteria? Such as CallType=’a’ or CallType=’b’. I get this error: Error: Invalid Data.
    Review all error messages below to correct your data.
    Apex trigger dlrs_TaskTrigger caused an unexpected exception, contact your administrator: dlrs_TaskTrigger: execution of AfterInsert caused by: System.QueryException: unexpected token: OR: (dlrs)

  13. Is there a way to rollup data from child to parent account, and then on up to the next level parent in the account hierarchy?

  14. Hi Andy,

    Thanks for the tool. Received this when installing:

    Deployment Complete
    Failures:
    permissionsets/LookupRollupSummariesFull.permissionset(LookupRollupSummariesFull):Unknown user permission: AllowUniversalSearch
    permissionsets/LookupRollupSummariesReadOnly.permissionset(LookupRollupSummariesReadOnly):Unknown user permission: AllowUniversalSearch

    Will this cause an issue with my use of the tool?

    • Are you install via the ‘Deploy to Salesforce’ link (aka unmanaged) or via the ‘Package Install URL’ (aka managed)?

      • I had the same errors installing it today into prod by clicking on the ‘Deploy to Salesforce’ (Git to SF)

      • Yes I think there is a some issue with some orgs and the raw Permission Sets when using the Deploy to Salesforce option. Is there any reason why you are not using the manage package? This should avoid these problems.

      • Meanwhile I’ve added a note to the bottom of the README file for this issue.

      • There isn’t any reason, I just clicked the button 🙂 I installed the managed package into sandbox with no problem.

        Here is what log on the page was showing:

        Status: InProgress
        Status: Completed
        Deployment Complete
        Failures:
        permissionsets/LookupRollupSummariesReadOnly.permissionset(LookupRollupSummariesReadOnly):Unknown user permission: AllowUniversalSearch
        permissionsets/LookupRollupSummariesFull.permissionset(LookupRollupSummariesFull):Unknown user permission: AllowUniversalSearch

        You might want to correct the status when errors occurred to state that Deployment wasn’t successful.

        And thank you for the great code! We are using Rollup Helper, but are going to extended the list of rollup fields and trying to do not spend money on the paid version (free edition is limited to up to 3 rollups, no real time)

  15. Hi Andrew, this is a great tool. One question; is there a way to simply count the number of related records that fit a criteria similar to how the native Salesforce roll-up works? I notice that you can only use date/number fields in fields to aggregate and it is a required field

    • You can count records yes, currently you do need to provide a field to aggregate, which i want to remove the need for, as it doesn’t make much sense to have this validation in the count use case. Simply for now provide a numeric field (or formula) you know will be populated.

      • Hi Andy,

        Thanks I tried a formula (number) field and it wouldn’t accept anything but Date or standard Number field type so I used a date field and it seems to be working fine. Thanks for the feedback

        Simon

  16. Great stuff Andrew. I’ve been using/paying Rollup Helper for 1 year, and your tool is almost as good (but free). If you’d only develop a UI for SOQL criteria, I would really consider not renewing Rollup Helper. Are you intending to eventually sell this great App? Thanks!

    • Thanks for the nice comment, no current plans to sell it no, it depends on community contribution, there has been some discussion on a criteria UI, but no movement on coding it as yet. I myself have been quite busy writing my book most of this year, now that is out, i can focus more of my time on this tool, so maybe i might get to it sometime soon, i know its high on the list of new features for sure! Thanks again!

  17. Mr. Fawcett,

    I thank you for your tool. I installed it on a sandbox and the rollups worked flawlessly. However, I duplicated in our production environment to find that one of the two roll ups I had re-created from my sandbox would not update.

    To make it stranger, if I used the manual calculate button, it would recalculate my master record field just fine. So I think my roll up definition is correct. It’s just this one ‘sum’ rollup is not working realtime.

    I wish to be respectful of your time. but if you are able to give some gentle guidance for this great tool, let me know. I did capture a debug log that seemed to indicate the trigger did execute successful. I even saw the SOQL statement.

    It is also worth noting that I installed v .12 on my sandbox and it looks like you just recently released .13 –which I installed in production.

    Thank you!

    Brian Schlottmann

    • I have a quick update. First sorry I posted here. After a little research I realized that you had an issues board on the Github website where I found this issue

      https://github.com/afawcett/declarative-lookup-rollup-summaries/issues/85

      This was my exact issue. I was count’ing and sum’ing on the ‘Amount’ field in Opportunities. So i change the count rollup to count the CloseDate field and it is working fine.

      I might suggest a simple count record based on a WHERE clause? Not sure if that helps.

      Feel free to disregard these comments on the blog post.

      Thanks again.

      • Ah yes, the count problem, that took me a while to spot, its deep in the docs where it states null values on records for which the field your counting does not increment the count.

        When you say “I might suggest a simple count record based on a WHERE clause?” can you elaborate, i just want to make sure i understand this statement?

      • Well, I’m not too familiar with SOQL. I’m more of a sql guy myself. But in excel you have countif() function that counts a row if a condition is met. That might be over kill. Or perhaps you can already do that with the conditional Where as clause you have. But I am thinking that if you could just count against the record ID that would might solve it?

        I also am having another issue–if you are able to point me in the right direction hopefully I can solve it. We have a shopping cart product that serves as our store front. What is nice about it is that as you are shopping in the store, it is creates an opportunity record and product line items LIVE as the cart is being filled. Think of the shopping cart as a glorified extension of the salesforce UI. It uses Force.com pages and is 100% native.

        However, once I add the first item to the cart and the opportunity is initialized, I get a strange permissions error. It says:

        dlrs_OpportunityTrigger: execution of AfterInsert caused by: dlrs.SObjectDomain.DomainException: Permission to access an dlrs__LookupRollupSummary__c dennied. (dlrs) Trigger.dlrs_OpportunityTrigger: line 7, column 1

        Best I can tell, the shopping cart force pages do not have proper permissions to access the trigger. I am not quite sure how to fix that. Is that something in your managed package? Or something I can easily fix by tweaking something somewhere?

        Thanks again. If you have time to point me in the right direction, I can probably get it figured out. Really enjoying your work!

      • Sounds like you need to add access to the custom LookupRollupSummary__c field to the site guest user profile. To get to this is a little convoluted, but i believe if you go to the site in setuo and click public access settings you will see a way to manage it. I’m on mobile so can’t look it up exactly.

      • RE: Count, yep, count by Id is likely to be what we will default to when using the Pilot UI Wes wrote a while back, since it always has a value! 🙂

        RE: Permission error, yeah this is indeed down to lack of permissions to the DLRS objects. I ship some Permission Sets with the package you can assign to applicable users. Both start with LookupSummary if your looking for them in your orgs Permission Set list. If you cannot assign these for whatever reason, simply view them to see what permissions they give and apply to your own Permission Set or Profile.

    • Sorry for missing this comment until now, wondered if you had a chance to try the latest releases of the packages to see if the problem still exists?

  18. Hi Andrew! Thanks for this tool. I have been using it without any issues until today. I am getting this error when try to add a new lead that the country is in Saudi Arabia. Hope you can help!

    Error: Invalid Data.
    Review all error messages below to correct your data.
    Apex trigger dlrs_LeadTrigger caused an unexpected exception, contact your administrator: dlrs_LeadTrigger: execution of AfterInsert caused by: System.DmlException: Update failed. First exception on row 0 with id 0015000000GkLCiAAN; first error: FIELD_INTEGRITY_EXCEPTION, There’s a problem with this state, even though it may appear correct. Please select a state from the list of valid states.: Billing State/Province: [BillingState]: (dlrs)

  19. Hi Andy
    Using this amazing tool with a number of small nonprofits that I am working with. As I’m not a developer. This has been absolutely brilliant. However , have just begun creating some roll ups for an organisation that I had previously installed a trigger in.
    I get the following error when I try and deploy the dlrs trigger.
    TestDonationMainContactUpdate.myUnitTest System.DmlException: Insert failed. First exception on row 0; first error: REQUIRED_FIELD_MISSING, Required fields are missing: [Name]: [Name] Class.TestDonationMainContactUpdate.myUnitTest: line 32, column 1
    I know its something to do with my trigger but not sure where to look.
    Any suggestions.

    • Hi andy
      Solved issue with some behind the scenes smoke and mirrors.

    • Thanks appreciate the kind words! If you open the test class referenced in the error, near line 32 it will be creating a record and likely not populating the Name field. Did you write the test or another developer?

  20. Love this tool and have been using it for a while. Question — I have a scheduled job that is running nightly and completing with no errors (3/8/2015 5:00 AM Batch Apex Completed 1 1 0 3/8/2015 5:03 AM RollupJob 7071300001LJCqE). However, it’s not calculating a Lookup Rollup Summary that I have set to Active with Calculation Mode: Scheduled. When I click ‘Calculate’ and manually run a calculate job it works but the scheduled job isn’t working. Is there something I’m missing?

    • The scheduled job only works in response to child record updates which are logged in ‘Lookup Rollup Summary Schedule Items’ related list when you look at the rollup. These entries are only created if the tool notices the value of the ‘Field to Aggregate’ field or any of the fields in ‘Relationship Criteria Fields’ have been changed.

  21. Hmm…. any suggestions of what I could do if I’m looking to rollup a formula field? Essentially I have dated contracts as a child object and there’s a formula (1 if Active, 0 if not (Based on expiration_date > today) and my hope was that by running this job each morning I could get an accurate count of how many contracts were active for an Account.

    • Hmmm yeah likely the tool won’t see this as a change due to when the formulas on the platform are calculated, you could try a workflow field update to copy it over to a another field and monitor that for changes, of course the users would never see this field.

  22. Hello, I have been using the tool for some time with great success! Thanks so much for your work. However as of today, I am having an issue with a new rollup I am working on. When I go to deploy, it goes through the “Deploying…..” process and then says “Deployment Complete” but also shows “Click Deploy to Install the Apex Trigger and Apeex Class for this child object”, the Deploy & Cancel buttons still showing at the bottom of the page & when I try to Activate the rollup, it gives me an error saying it has not been deployed. Clearly it is not actually deploying despite the message yet I have no error or method of troubleshooting. Any ideas? Thanks!

  23. Thanks for the prompt. I was not on the latest version. I did install and now actually get a more informative null: The changes you requested require salesforce.com to temporarily lock your organization’s administration setup. However, the administration setup has already been locked by another change. Please wait for the previous action to finish, then try again later.

  24. I have v 1.9. With the spring 15 release 1.19, is this something I should upgrade? I recently have experienced my roll up not working. I should be seeing a total of 2 and it remains at 0. How do I begin to troubleshoot, since it used to work? I have NOT ran an upgrade.

    • Nothing to upgrade if you had a working rollup before, can you raise an issue on the github issue list and include steps and a screenshot of your Rollup please.

  25. Hi Andy, great tool by the way. I was wondering if anyone could help with a SOQL statement. I want to query records for a certain week in the future. I can use THIS_WEEK and NEXT_WEEK to query records for the next two weeks individually but what if I wanted to query records for week 3 or week 4.

    • Thanks for the kind words! Can I ask if it would be ok to post this question in the chatter community group? There is a link to this group on the Readme file for the tool.

  26. Hi – we had a field that was calculating a subset of transactions….the recalculate feature did not seem to be working….turns out we were on version 1.22, so I upgraded to 2.3…but the rollup summary field is not recalculating after pressing “calculate.” the field is set to realtime.

    in addition to updating the installed app, are there other updates we need to make?
    this app was installed by a consultant who is no longer with us…i’m trying to catch up and figure everything out! thanks in advance.

    • Are their any errors in the Apex Jobs screen? Also in terms of realtime edits or inserts etc to child records, if that is not working, check if your using formulas as part of the rollup, there is some additional considerations I detailed in a blog I wrote recently, see blog links in the doc section of the readme file

  27. When it comes to the realtime trigger logic, does the 20 query limit imposed on trigger execution by the platform impact processing in any way? I’ve done some study of the code on Github and have not been able to determine if this is the case. My assumption is this, if the 20 query limit is a factor, I suspect this would mean that each object would be hard limited to no more than roughly 18 – 20 rollup summary definitions. Though, this may be getting bypassed in some way, if so, how is this being achieved?

    Thanks!

    • The number of SOQL queries that can be made in one execution context / request invoked by the user is 100 per managed package namespace (within the DLRS code). If you had 20 rollup definitions the maximum they would take up is 21 SOQL queries (1 to query the rollups if your not using custom metadata mode). That said however depending on if they share some configuration the tool will attempt to merge rollups into a single query, so this number could come down. I’m currently pondering some kind of profiling tool to better illustrate this actually. The real issue though with having to many rollups active, is the processing time and number of rows, especially if they are all Realtime. Your likely to hit issues like 50k or CPU Timeout before hitting the number of SOQL queries limit of 100. When designing your rollups try to consider which you need Realtime and which can be Schedule mode (there is also a daily Scheduled recalc mode which requires no trigger logic at all). Note also that the batch jobs can also hit governors, though you can use the custom settings to flex the scope size to address this. Hope this helps.

      • Yep, that cleared things up for me, I had read in the Apex docs somewhere that triggers will hit the SOQL query limit at 20 or more executed queries (from my initially expected 100), and seeing that the entry point for the realtime execution is in a trigger, that is what lead me down that road of thought.

        Thanks for the info!

  28. When it comes to the realtime trigger logic, does the 20 query limit imposed on trigger execution by the platform impact processing in any way? I’ve done some study of the code on Github and have not been able to determine if this is the case. My assumption is this, if the 20 query limit is a factor, I suspect this would mean that each object would be hard limited to no more than roughly 18 – 20 rollup summary definitions. Though, this may be getting bypassed in some way, if so, how is this being achieved?

    Thanks!

  29. Hi Andy. I am using a custom metadata rule to count the number of child records that is associated to a given parent record and updating a number field in the parent record. The field to aggregate on is the Id field; sharing mode is System. When the rule is in real time mode it works under all scenarios. I have changed the rule to Developer mode and am calling the api in apex code to meet some business requirements around merging. That works in all scenarios except one. When I change the lookup field of the child record to a different parent, the new parent’s count is incremented correctly, but the old parent’s count is unchanged. This happens only when the old parent no longer has any children of that object. In the code to update the old parent, I am passing an empty list of child records to the rollup method. Am I doing something wrong? Thanks in advance for any suggestions.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s