Andy in the Cloud

From BBC Basic to Force.com and beyond…

381 thoughts on “New Tool : Declarative Rollups for Lookups!

  1. Thanks Andrew for completing the story, very well covered and really made useful for Admins now !

    • Thanks Abhinav, I’m having fun with it, your library is well coded and has a great API design, really pleased you feel I’ve done it justice! 🙂

  2. This is awesome! Thanks for sharing Andrew. I am going to test it and will let you know feedback!.

    • Your very welcome, please can you raise any issues, requests etc via the Githib Issue tracking system for the project. It is located here.

      • Have you had a chance to take a look yet Prafulla? I updated the blog post at the weekend with Beta 6, it contains a few bug fixes.

  3. This sounds excellent. Again, very grateful for your contributions. I’m going to pass this along to our team.

  4. Thank you for creating this! I just installed on one of my client’s accounts and am testing with 4 rollup fields. My only problem is the testing is on records that already exist in the system. Is the trigger only currently working on new records or updates to existing records? Or will it calculate regardless?

    • Your very welcome, and yes it works on insert or update, in a future version a Calculate button will be available for existing data, as well as a Scheduled mode alternative to the Realtime trigger mode.

      • great! I look forward to seeing that. Please let me know when future releases are out. Do you have any shortcuts/hints on how I can “trigger” the system to have it ccalculate on all of the existing records?

      • It’s optimised not to run the queries if the aggregate fields have not actually changed, even if other fields have, which kinda makes it hard to just go and do a dummy update to the child records, darn that optimisation! 😉

        However if you add a new child record and delete it that should do the trick.

  5. Andrew,
    Any plans of launching this as a free app on AppExchange. It may give it a wider audience.

    • Maybe, it’s going through security review at present, ideally i’d like to get more feedback and do the schedule/calculate mode as this provides another option that is less intrusive.

  6. Andrew – I wanted to provide you some feedback for the short few days I’ve been using this tool. I haven’t been able to fully test it since the calculations I have are on existing records. But I did want to let you know about one small glitch I ran into in installing/setting up the tool. Part of this is my lack of knowledge on installing APEX tools. It works great for the System Administrator, but when another user in another Profile tried to enter a new record that was being affected by the tool, it gave an error message and would not save the record. It took me a little while to figure out where in the Profile to fix this but I managed to figure it out.

    It would be great if during the setup/install of the tool, there was a prompt to ask which Profiles you want to enable the tool on, to save the Admin the time to do that separately. And if this can’t be done, then to provide simple instructions on where to go to activate it for each set of users beyond the system admin.

    Otherwise the setup was easy, and creating the rollups was easy as well. I look forward to further testing on the tool and seeing future releases with real-time calculations on existing records, etc.

    • Thanks for this great feedback! I will add a section to the readme file describing which operations need what profile settings to clarify this better. Thanks again!

      • More feedback – when you delete records that are triggering the calculation (a Donation record that gets deleted, and the total donations for a contact is calculated on the contact record in a field), the calculation is not updated on the contact record. Not sure if this is missing in the program or something is wrong on my end.

      • Ok thanks for the update, can I ask a favour, can you raise this as an issue in the Github repo, comments once read on my blog tend to get a little lost. Thanks again for the feedback and I’ll take a look soon (I’m out in the US at present, so will likely be early next week). In the meantime, the source code is also available in the Github repo, so if you have any devs that fancy taking a peak at what might be the issue, also more than welcome.

      • I’ve reproduced this and fixed it, i raised an issue on your behalf and have closed it, check the read me file for the Github repo for version 1.4 of the package (you can upgrade to it from v1.3). Thanks again for the feedback!

  7. Wow! Thank you very much. Exactly what we were looking for!

  8. Just from end user perspective , the parent object and child object should be a pick-list or a look-up. I as an end user might make multiple typos. Plus the relation ship can populate valid values based on the child and parent object choosen.

    This would add extra code but would definitely enhance the UI experience of admins using it.

    • That’s great feedback! Would mind adding it as an Issue to the GitHub repository please? It can then be traced through to being applied. Thanks again!

  9. Andrew,
    This is a remarkable value-add to an already great library by Abhinav. Has development of the ability to Schedule the rollup engine been put on hold? I only ask because I was wondering how far out it might be, if your were combining batch processing with the scheduled job (I couldn’t tell from my glance at the source if the LRengine was actually batched, just where it noted it would ideally be called from a batched context) and finally, if you had considered using the Relax package to declaratively handle job scheduling.

    Bottom line, I wanted to compliment you on the quality of both your regular blog posts as well as the open source code you create and share with the community ( githubsfdeploy, etc)

    • Thank you so much for the kind words! I have been thinking for a while how to tackle the schedule option and I think I am close to a solution. So no, not on hold, just pondering. 🙂

    • Just out of interest, what is it about the schedule mode vs the trigger mode you like? Is it not having to deploy the trigger, performance or something else?

      • The Schedule mode is mainly about performance.

        Use Case: We have an online LMS where Evaluators are reviewing student projects. The visualforce grading page they are working on updates project components which have triggers that update the projects they are related to…and I’m rolling up number of completed projects (and related data – avg score, etc) to the Student’s contact record. I could see performance issues down the road as students projects list increases, and it doesn’t really need to be realtime (as the data being used is really for weekly or monthly review). it would seem unnecessary to slow down the editing of objects once removed from where the rollups are if the rollups aren’t important to either the evaluator or student.

        Secondary to that, but still a concern, is the possibility that we might need to rollup data based on records that have existing custom triggers, where the rollup filtering criteria are being met based on modifications done within those custom triggers – since the trigger order can’t be guaranteed, the rollup might not work properly so it could be better to simply batch process the rollups. That’s just theory right now though.

        Thanks again, looking forward to your next blog post!

      • Very useful use cases thanks!

      • Hello, I’m looking forward to the schedule mode too. I’m assuming it will help with the objects that triggers can’t be created on…for example, OpportunityContactRole

      • Schedule mode is implemented now, sadly it still requires a trigger though, to record/queue parent records to update in the background. I could not see any way to avoid needing this sorry to disappoint.

  10. Hi Andy!

    This is a very useful tool and I really appreciate the time and effort that you put into it!

    If you get a free moment, I just have a quick question: You mention in your documentation that using Scheduled calculation mode does not require a trigger… so do I still need to deploy the trigger on the child object when setting up a scheduled rollup? The reason I ask is that I keep getting errors every time I try to deploy the child trigger. But if I can use a scheduled calculation without a trigger, it will meet my needs beautifully.

    Thanks again, awesome app!

    John

    • Thanks!

      Scheduled mode is currently under development, hopefully released this weekend. I have really tried to avoid using a trigger to implement it, but in the end it is still needed to record parent records that need recalculate, I was just unable to come up with a reliable and performant way to know which parent records needed recalculation.

      So the next release will indeed allow you to schedule but the job will need to be fed by a list of parent records to process which the trigger will create. So in the meantime let’s get to the bottom of your trigger deployment issue. Can you raise a Git issue on the Repository with the error and I will try and help you out.

      Thanks, Andy

  11. Thanks Andy! Yes, I will raise a GIT issue. I appreciate your help very much, this really is quite a tool! This is going to change my life!

  12. Great tool, thank you for sharing it!

    • Your welcome, working on an update at this very moment as it happens! Best way to spend a holiday day off in the cold UK at the moment! 🙂

  13. Pingback: New Release: Spring’14 Declarative Rollup Summary Tool | Andy in the Cloud

  14. Hey would you mind stating which blog platform you’re
    working with? I’m looking to start my own blog soon but I’m
    having a difficult time making a decision between BlogEngine/Wordpress/B2evolution and Drupal.
    The reason I ask is because your design and style seems
    different then most blogs and I’m looking for something completely unique.

    P.S Apologies for being off-topic but I had
    to ask!

    • No worries at all, it’s WordPress.com (the version hosted by them, so your up and running in seconds, no server to mange etc) it’s pretty good and has an excellent mobile app which clinched it for me!

  15. Hello Andrewm

    Thank you for the tool, it is great. I have a problem I have not been able to solve. I want to do a Roll Up from a Child Account to the Parent Account. So we can see the performance of one network of partners. How would I accomplish this? Thank you for your time.

    Daniel

  16. Wow, that’s what I was searching for, what a material!
    existing here at this blog, thanks admin of this site.

  17. Wow great tool. I was looking for this one a long time 🙂
    But I’m getting an error on counting the number of events.

    When I’m inserting an event (as an administrator) it’s working fine, but when a user is inserting an event he / she will get the following message:
    Permission to access an dlrs__LookupRollupSummary__c dennied

    • You need to grant read/write access to all the objects in the package (view Install Components by clicking the package under Setup then Installed Packages) for users who will be defining and creating rollups. For other users only read access to these objects is needed. The next release of the tool will include a Permission Set to make this process easier.

  18. Currently it appears like Drupal is the top blogging platform out
    there right now. (from what I’ve read) Is that what you’re using on
    your blog?

  19. Impensable, je ne connaissait pas votre site, vous etes maintenant bookmarker !

  20. Pingback: New Declarative Rollup Tool Release – Community Powered! | Andy in the Cloud

  21. Looks like a great tool, I will be trying it out in my dev org. Andrew, could you share the AppX link for the tool as well? For client installations, it will be important to show that the tool has gone through Salesforce security testing.

    • Think package link is on the GitHub repo readme page linked in the blog. I do not intend to list on AppExchange though it has previously gone through security review as the package is exempt from app and object limits as a result and is also licensed (free).

  22. I’ll be trying this in my dev org. For client installation, it will be important to see that this tool has gone through Salesforce’s security review. Andy, would you share the AppExchange link for your tool please? Even if it’s a private listing. I’m going to heart it there as well.

  23. Thanks for making this tool available!
    I am having a challenge though in getting the remote site settings to work when installing in a developer org. I changed the eu2 to na15 but get an I/O Exception (IO Exception: Unauthorized endpoint, please check Setup->Security->Remote site settings. endpoint = https://dlrs.na15.visual.force.com/services/Soap/m/28.0
    Error is in expression ‘{!deployZip}’ in page dlrs:managetrigger)
    Any thoughts?
    Thanks!

  24. Hi Andrew, I would like to congret you for the solution, I just discovered it today and I’m already using it ! Thank you again !

  25. Hello Andrew, Is there a way of making a parent “roll-up” to a child using this information or “roll-up” to an unrelated object or a junction object? Thanks

  26. Hi Andrew. This is awesome….THANKS! I’m confused by one thing though. It seems that I have to choose a Numeric/Date field to aggregate when using the Count function. I have found a workaround for my needs, but just wanted to point that out. Salesforce.com’s UI/engine only requires an aggregate field to be chosen when using SUM, MIN, MAX and AVG, but for Count it simply just counts the records….I think this makes more sense, and I don’t see any mention of it above.

    However, you seemed to have this worked out with the ‘Enhanced Edit (Pilot)’ button. I’m curious how this works since the ‘Field to Aggregate’ field is configured to be required. Do you enter a default value when Count is chosen?

    • This is a really good observation! I’ve raised an bug on your behalf to see if i can make the field not required when using the count operation. Your correct the enhanced edit hides the field to aggregate field, however it does eventually fail to save the record for the same reason as you’ve pointed out the ‘Field to Aggergate’ is required. As you say you can workaround this for now by picking any field. Thanks for raising this!

  27. Hi Andrew, I had some challenges deploying the tool. When I went to activate the “Manage Child Trigger”, i received an error message, “Error is in expression ‘{!deployZip}’ in page dlrs:managetrigger”. Any suggestions of how I can work through this would be appreciated.

    Best,

    Nick

    • Sorry Andrew, I’ll move these questions over to the GitHub Issues page.

      Best,

      Nick

      • Thanks, helps me track things better, i think someone else report this btw today, something odd going on, maybe platform issue, can you check when raising the issue the above is the full error? Is there something above it?

  28. Regarding the LRengine (https://github.com/abhinavguptas/Salesforce-Lookup-Rollup-Summaries) can you confirm if the code covers the case when an Opportunity is re-parented to another Account. (deleting an Opportunity works, re-parenting doesn’t seem to)

    For example

    You have 2 Opportunities which roll up their revenue (using the LRengine) to Account.

    account1.Total_Rev_c = 20
    opp1.Rev_c = 10
    opp2.Rev_c = 10

    If opp2 is updated to lookup a new account (account2), you’d expect account1.Total_Rev_c = 10 not 20.

    • Thats definitely a use case this tool that wraps the LREngine looks out for (in the latest versions) and handles it, I’ve got unit tests that cover the above (again via the tool engine). Are you calling LREngine directly yourself from your own trigger? If so, also make sure you have the latest LREngine.cls (i submitted some fixes for a use case similar to this).

  29. Hi Andrew,

    Can this tool be installed on any sandboxes. The link provided here is taking me into production environment.

    Please let me know.

    Thanks,
    Debarshi

  30. Andrew,

    This looks like a great tool! I work for a Salesforce implementation partner and we have recommended this tool to be used for a Salesforce implementation. They are asking for proof that the tool has passed the Salesforce security review. Do you have evidence of this that I could share with my customer?

    Regards,

    John

  31. It looks like this doesn’t support MIN/MAX functions on Date fields like the standard rollup summary fields do. Any hope of fixing this in a future release?

  32. It looks like this doesn’t support MIN/MAX function for Date fields like the standard summary roll-ups. Any chance of fixing this in a future release?

    • I will take a look, it should support this yes, can you raise an issue on the GitHub repo and include a screenshot of your rollup and details if your fields and error message. Thank you.

  33. I installed it using the package but cannot get the remote site to work. My instance is na17, so I am creating a new remote site called “dlrs” with https://dlrs.na17.visual.force.com as the Remote Site URL, and then checking the “Active” box. What am I doing wrong?

  34. Andrew:

    I am having trouble with the remote site settings. I installed the package, and I have created a new remote site called “dlrs” with URL https://dlrs.na17.visual.force.com and I also checked the “active” box. Nothing seems to be working. Help!!

  35. Andy thanks for a great App – have just installed it in a non profit – its giving them some real benefits. – Am doing a presentation this afternoon to the NZ users’ group on AppExchange apps – Am using one of their processes to illustrate use of multiple AE apps – will be showing functionality that is made possible(without programming) – your tool sits behind some of these processes

    • Thats great news, thanks for sharing, really motivating for me to hear about such things! I’ll be sure to Tweet / Retweet for you! Go go go clicks not code! 🙂

  36. Hi Andrew, I want to filter on a Date field, ideally I want to filter on Year-to-date values, can I use a filter criteria like “THIS YEAR” or >= 1/1/2014 ? If so how — tried various things with no luck! 🙂

  37. Andrew, great job.

    I wish I had seen this when it was originally published. I had wasted a lot of time writing triggers to do the same end of last year. Well, it is not too late for me. I can throw those triggers out the window and have them declared decoratively now!

    Wonderful job.

    Thanks.

  38. Andrew, this is so great !!! Just what I was looking for and here it is !!! It’s also great to be able to use multiple criteria as mentioned here: https://github.com/afawcett/declarative-lookup-rollup-summaries/issues/41

    A thousand thanks for this !

    • Your so very welcome! Pleasure to receive such a nice comment and very inspiring. It’s a busy time for me at the moment personally, but hope to get back to doing more on this tool soon! Thank you again!

      • Your MVP position is very well deserved as your tool is both very simple to implement, easy to use and highly effective. I was looking for something like this two years !

        I found it through Success Community (thanks to the post of Jodi Nemser-Abrahams, so credit to her as well). Too bad Salesforce is not implementing this (2270 votes so far and open since 6 years). It’s a quite central feature.

        What you did is very inspiring as well for some project I have, especially regarding the interface. You totally understood the needs of a Sys. Admin. for configuring and putting an elegant solution quickly in place. I wish I could contribute as you do to the community !

  39. Pingback: Rollup Summary for Lookup Fields | LevineSFDC

  40. Is “advanced multi currency” on the road map?

    • There is an issue raised about this, someone has made a contribution that I think delivers this. Can you confirm what your expectation is for this and I will check.

  41. is there a way to do text concatenation rollups with the app?

    • Not 100% sure what you mean, but using forumla fields can get you a long way in doing transformations. Can you elaborate?

      • Hi Andrew
        got the same question, i want to rollup on a master record (Opp) a text field from child records.
        Right now you can only rollup Date/DateTime/Time/Numeric fields, would be great to have as well text fields:

        Context: a “combined” opportunity linked to other opportunities (lookup), I need to rollup the total value of the combined opp (done with your great app) but as well the total product list (text field) rolled up on a text field on the main Oppy.

        Thanks for the feedback and thanks for the great tool

      • Hi, so you want to concatenate the text values of child records into one large text area field on the parent?

      • Hi Andrew,

        exactly as you said, do you see it feasible?

      • Volumes may be an issue, the tool leverages aggregate SOQL queries feature under the good to effectively get the database to do the heavy lifting. It is this that doesn’t support text fields. A workaround would be to read all the children into the tool directly and perform the rollup in code, however this would be bad for volumes of records. Now that I understand the requirement I will give it some more thought, but it’s sadly not a straightforward extension to the current architecture. Thanks for the feedback though! 🙂

      • Hi Andrew,

        thanks for looking into it, in our particular scenario this will not be an issue as we are never rolling up more than 4 child records and the info is coming from text or picklist fields,
        Do you foresee any chance to get it working for picklists as well?

        Thanks once again

      • I’ve raised an enhancement for this on the GitHub repo, feel free to add further details if you want.

      • Great thanks!

    • Sadly not at present, your not now the first to ask about this though, i’ve added to my list of things to think about for future.

  42. Getting that error:
    permissionsets/LookupRollupSummariesFull.permissionset(LookupRollupSummariesFull):Unknown user permission: AllowUniversalSearch
    permissionsets/LookupRollupSummariesReadOnly.permissionset(LookupRollupSummariesReadOnly):Unknown user permission: AllowUniversalSearch

    What do I need to do to get around this error. Would love to get your tool installed and working.

  43. Hi Andrew,

    after enabling the trigger and see that the roll up works, I got the following error when tryind to edit an opportunity record:
    Apex trigger dlrs_OpportunityTrigger caused an unexpected exception, contact your administrator: dlrs_OpportunityTrigger: execution of AfterUpdate caused by: System.QueryException: line 1:136 no viable alternative at character ‘”‘: (dlrs)

    Do you know why this can be caused? Thanks

    • Are you using a rollup filter? It could be the syntax of this. It has to match SOQL Where clause syntax.

      • Hi Andrew, that was right, I took out the filter and works now, will investigate in detail the SOQL I need to add. Thanks once again.
        One last question, is there any place here I can see the security settings I need to add to the profiles to run the rollups? during the installation I onyl granted admin rights, now I want to release to end users and not sure which classes/trigger I need to add to the profile and cannot find any info on that

        Cheers

      • Check your Permission Sets I included some in the package for just this purpose.

  44. Hi Andrew,

    We’re seeing some issues when trying to deploy a change set to the live environment where we have your package installed. We are seeing two errors where two triggers have 0% code coverage. Would you like me to post the errors here, or email you?

    Thanks,

    Simon.

    • I’ve got another report of a similar issue here, https://github.com/afawcett/declarative-lookup-rollup-summaries/issues/74, i also had someone mention to me this morning verbally an issue (not the same object). So I’m starting to worry something has changed since Winter’15, has your org recently gone up this weekend or last?

      • Hi Andrew,

        Yes, I think the org. was recently upgraded to Winter ’15. I managed to get our code deployed by writing something myself to pull up the coverage (even though the code coverage, when the tests were run in the live org, showed 100%) so the urgency for me personally has gone, but it would be interesting to know what this could be…

      • Ah thats good to know! Yes, i’m trying to do some research, would you mind sharing your workaround on the open issue i referenced earlier in the meantime.

      • Hi Andrew,

        I’ve updated the outstanding issue with the problem we saw and what we did to implement a workaround.

  45. Hi Andrew,
    When I refreshed my Sandbox the Rollup Summaries in my org didn’t follow into my Sandbox. I am re-creating the summaries in my Sandbox and it is requesting that I Create Remote Site Settings, when I click the “Create Remote Site Setting” button I am receiving the error “Error: This Remote Site Name already exists or has been previously used. Please choose a different name.”
    Any help would be appreciated.
    Thank you!

  46. Andrew, many thanks for this fantastic tool!

    I have a general question regarding multiple currencies. Apologies if this is documented somewhere else and I’ve missed it.

    We have custom objects with currencies other than the corporate currency. Both the parent and child are in the same currency, which is other than the corporate currency (e.g., the corporate currency is USD and the custom object records are created in the ILS currency). The roll up is for a currency field type.

    When the roll up occurs (SUM), it seems to sum the amounts in the corporate currency, then convert it again using the exchange amount. For example:

    Child record #1 currency amount: ILS 5,000 (USD 1314.06)
    Child record #2 currency amount: ILS 10,000 (USD 2,628.12)
    Child record #3 currency amount: ILS 15,000 (USD 3,942.18)

    Parent record receiving the summed rollup: ILS 7,884,36 (USD 2,072.11)
    Expected: ILS 30,000 (USD 7,884.36)

    This could very well be something we’ve configured wrong. But thought I’d ask the general currency question.

    Thanks again Andrew!

    • Multi-currency is not supported, but is an issue raised and is on the enhancement list in the repo, there has been some coding effort in the LREngine library (the tool uses) to support this, so its a matter of checking if upgrading the library in the tool will suffice in providing this functionality. I’m hoping to get some more spare time to look at this in the coming weeks. Sorry its not possible today.

    • Thanks Gene, the underlying library this tool used was recently enhanced by another community developer, i plan to review this to see if upgrading to this within this tool will add this support and if not, what extra is needed. Your detailed example here helps a lot! I’ve added to the enhancement here, https://github.com/afawcett/declarative-lookup-rollup-summaries/issues/57

  47. Hi Andrew,
    Is it possible to have multiple Relationship Criteria? For example Bus_Type__c = ‘Bus Type 1’ && Installation__c = 1
    Thank you!

  48. Something so simple! Thank you dbpros for all your help!

  49. Could this be installed as an unmanaged package?

    • Yes, if you scroll to the bottom of the README file there is an option to install unmanaged directly from the repo, using the ‘Deploy to Salesforce’ link.

      • Is there a trick to installing this in a sandbox environment? I click the deploy to SF link which redirects to a production URL. when I change login.sf.com to test.sf.com and login with my sandbox credentials I get the following error: Authentication Failed: OAuth login invalid or expired access token

      • Sorry my bad, i’ve updated the link to the new style button, please try it again.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s