Andy in the Cloud

From BBC Basic to Force.com and beyond…

Declarative Lookup Rollup Summaries – Spring’15 Release

202 Comments

This tool has had a steady number of releases last year, containing a few bug fixes and tweaks. This Spring’15 release contains a few enhancements i’d like to talk about in more detail in this blog. So lets get started with the list…

  • Support for Count Distinct rollups
  • Support for Picklist / Text based rollups via Concatenate and Concatenate Distinct operations
  • Support for Rolling up First or Last child records ordered by fields on the child such as Created by Date
  • Support for Lightning Process Builder

UPGRADE NOTE: The Lookup Rollup Summaries object has some new fields and pick list values to support the above features. If you are upgrading you will need to add these fields and pick list values manually. I have listed these in the README.

NewRollup

The following sections go into more details on each of the features highlighted above.

Support for Count Distinct rollups

If you want to exclude duplicate values from the Field to Aggregate on the child object select the Count Distinct option. For example if your counting Car__c child records by Colour__c and it has 4 records, Red, Green, Blue, Red, the count would result in 3 being stored in the Aggregate Result Field. You can read more about the original idea here.

Support for Picklist / Text based rollups

TextRollupThe rollup operations Sum, Min, Max, Avg, Count and Count Distinct operations are still restricted to rolling up number and date/time fields as per the previous releases of the tool.

However this version of the tool now supports new operations Concatenate and Concatenate Distinct that support text based fields. Allowing you to effectively concatenate field values on children into a multi-picklist or text field on the parent object record.

By default children are concatenated based on ascending order of the values in the Field to Aggregate field. However you can also use the Field to Order By field to specify an alternative child field to order by. You can define a delimiter via the Concatenate Delimiter field. You can also enter BR() in this field to add new lines between the values or a semi-colon ; character to support multi-picklist fields. You can read more about the original idea here.

Support for Rolling up First or Last child records

RollupFirstBy using the new First and Last operations you can choose to store the first or last record of the associated child records in the parent field. As with the concatenate feature above, the Field to Aggregate is used to order the child records in ascending order. However the Field to Order By field can also be used to order by an alternative field. Original idea here.

Support for Lightning Process Builder

To date the tool requires a small Apex Trigger to be deployed via the Manage Child Trigger button. With added support for Lightning Process Builder actions. This allows rollup calculations to be performed when creating or updating child object records. Sadly for the moment Process Builder does not support record deletion, so if your rollups require calculation on create, update and delete, please stick to the traditional approach.

ProcessBuilderModeTo use the feature ensure you have selected Process Builder from the Calculation Mode field and specified a unique name for the rollup definition in the Lookup Rollup Summary Unique Name. RollupUniqueName

Once this is configured your ready to add an Action in the Process Builder to execute the rollup. The following Process shows an example configuration.

ProcessBuilder1

ProcessBuilder2

As you’ve seen from some of my more recent blogs, i’m getting quite excited about Process Builder. However i have to say on further inspection, its got a few limits that really stop it for now being really super powerful! Having support for handling deletion of records and also some details in terms of how optimal it invokes the actions prevent me from recommending this feature in production. Please give me your thoughts on how else you think it could be used.

202 thoughts on “Declarative Lookup Rollup Summaries – Spring’15 Release

  1. I am getting an error when I convert the Lead to a Contact. I have a lookup that updates Leads from Activities. Here’s the error: Any ideas? It looks like perhaps yours is executing after the Lead IsConverted gets changed. I tried adding where lead.Converted is False into the Where clause, but that doesn’t work because the field is not in the child object

    Error Type: System.DmlException
    Error Date: 2016-07-12 19:48:36
    Message: “ConvertLead failed. First exception on row 0; first error: UNKNOWN_EXCEPTION, System.DmlException: Update failed. First exception on row 0 with id 00Tj000001Cf01eEAB; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, dlrs_TaskTrigger: execution of AfterUpdate

    caused by: System.DmlException: Update failed. First exception on row 0 with id 00Qj000000NlnzlEAB; first error: CANNOT_EXECUTE_FLOW_TRIGGER, The record couldn’t be saved because it failed to trigger a flow. A flow trigger failed to execute the flow with version ID 301j00000002cIN.

    • Looks like your parent object has Flow associated with it and this is failing. It is possible to add additional filters to the debug log for flow which might help you discover the cause. Typically Flows are called via Process Builder.

  2. Hi Andy,
    Thanks for the useful app!
    I only have one problem, why our security token is resetting every time we run the Roll Up app exchange?

    • Thanks. I am not aware of that issue, sounds strange. Can you use the Chatter group or GirHub to raise an issue and provide more details on the steps to reproduce. Thanks

  3. Hello,
    I try to delete or deactivate a lookup rollup summary and I can’t because of this Error:
    Error occured processing component dlrs__LookupRollupSummary2.Not_Claimable_number_of_investigations. An unexpected error occurred. Please include this ErrorId if you contact support: 959742975-348357 (1216218650) (UNKNOWN_EXCEPTION).

    What should I do?

    • This is an internal Salesforce error, you need to raise a support case and ask them to decode this message. Meanwhile let me know the steps your performing, it’s not clear from your description.

  4. Hi Andrew,
    Love the tool, its awesome and has saved me a lot of time so thank you.

    Im having one issue however with Picklists in the “Relationship Criteria” field. I have had some success when using only one picklist value: StageName = ‘Closed – Won’ however when I add multiple options in any of the following formats the rollup seems to fail. Essentially I am trying to roll the close date of the first opportunity ever closed on an account. I can’t use rollup summaries as its a second opportunity related list.

    Here are the formats i have tried

    StageName = ‘Closed – Won,Closed – Live,Closed – Unrenewed’
    StageName = ‘Closed – Won’,’Closed – Live’,’Closed – Unrenewed’
    StageName = ‘Closed – Won;Closed – Live;Closed – Unrenewed’

    I have looked at the documentation but it only shows the multi-select picklist format (which I have tried to no success)

    Would be great to get a steer from you on this if you have time.

    Thanks
    Mike

  5. Andrew, this tool is fantastic. Thank you so much for making it available to us – as a Salesforce Consultant for more than 10 years, I have to say its my favourite application ever.

  6. Anrew,
    We love this tool. We have many excellent additions to salesforce metrics because of it.

    I have hit a stumbling block though with one particular query that may not be able to be addressed. We are trying to roll-up a count of the number of EventRelation records associated with an Event. This essentially would give us a count of invitees (or even attendees). When i go to deploy the code though i get this error “triggers/dlrs_EventRelationTrigger.trigger (Line: 4: Column:38) : SObject type does not allow triggers: EventRelation”.

    Is this an insurmountable error?
    Brad

    • It seeens this is a salesforce restriction, which will prevent you using realtime or Scheduled calculate modes. However you can always go the full recalc scheduled route via the Schedule Calcuslte button. In this case just don’t Deploy the trigger and set the Calculation Mode to something like Process Builder

  7. Dear Andrew,

    I am working on a project to calculate the weighted average risk of an account based on the annual fee and risk level (1-5) of each asset in an Account. I found the “Declarative Lookup Rollup Summaries” and installed it successfully in the sandbox. However, I need additional help to use the tool.

    I also tried following:
    – Created a new Number field, Total Fee, in Account to store the aggregate result.
    – Created a new roll up summary via the “Declarative Lookup Rollup Summaries” UI and assign “Total_Fee__c” to the “Aggregate Result Field” based on the “Price” of Asset.
    – Checked the “Total Fee” on Account but no value is displayed.

    I must be missing some steps. I read another blog http://cloud4good.com/announcements/declarative-lookup-rollup-summaries/ about the 1st step to use this tool is to make sure a relationship already exist between the objects I want to roll up.

    Would you mind walking me through the necessary steps I need to create a roll up summary field on Account from an Asset field?

    Thank you very much,

    Sunny

    • The thing that tripped me up in the beginging with Salesforce is the relationship needs to be created from the child side. So you need to add a Lookup field to the Asset field that points to the Account. Then reference that field name on the rollup definition as the Relationship field. Be sure to include other fields you want the tool to watch for changes in to trigger the rollup on your Asset object. These need to be added to the Relationship Critiera Fields field. Good luck!

      • Thanks you very much, Andrew! The roll up fields are working. I will also review other fields that need to be watched in the Relationship Criteria Fields, too.

        Thank you very much again and Happy New Year!

        Sunny

  8. Hi, I’m having issues with the tool. It’s giving me an error “Unable to connect to the Salesforce Metadata API.”.

    I was wondering if you have any tips? Thank you.

    • Can you share on the chatter group (see readme) or on the github repo a screenshot of the welcome tab. Also check readme for know issues

  9. Hey Andrew,

    Love the app. Have used it for many different things with a great deal of success. I am having an issue currently, I am rolling up a date using the Max operation. It works on most, but on a few locations I am getting the following error:

    LocationtTrg: execution of AfterUpdate

    caused by: System.AsyncException: Future method cannot be called from a future or batch method: LocationCallouts.getLocation(Id, String)

    ()
    : CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY ()

    Any tips on how to avoid this?
    Thanks!

    • Are you using the scheduled mode? If so it looks like you have another developers trigger on your object and in some cases it’s trying to run some work in asynchronous mode when the rollup schedule code is also in Asynchronous mode, which is not permitted in the platform sadly. You may have to go back to the dev of the trigger and ask if they can implement it another way or look at the logic to see if it’s needed in your scenario. Or try realtime mode of the rollup.

  10. Could this tool be used to count the Number of Child Campaign records on a Parent Campaign which is not possible using a standard rollup summary

    • Yes possibly, checkout the readme file there is a link to a block on account hierarchy that might help with this understanding

  11. Hello Andrew,
    We have used this tool for some time and it helps us successfully.

    But a rule started to give trouble.

    Error: “A calculate job for rollup ‘Total International Expenses on Boarding’ is already executing. If you suspect it is not aleady running try clearing the Calculate Job Id field and try again.

    Can you help me solve the problem?

  12. Hello,

    I’m trying to create my first Lookup Rollup Summaries instance via the process builder as I’m using the professional edition. However, I can’t find the “Call Apex” choice in the “Action type” Section. What can I do to finish the creation of my process builder?

    Here is a screenshot: https://www.dropbox.com/s/obpyphrw7mgagyc/Capture%20d%E2%80%99%C3%A9cran%202017-03-14%20%C3%A0%2019.51.10.png?dl=0

    Thanks in advance,

  13. I am trying to implement this feature in a Professional environment – hence, why I’m using Process Builder, but I don’t see an option in actions for Apex Class.. any ideas?

  14. How can I abort a scheduled job? I want to update the schedule of a Rollup Summary.

  15. Hey Andrew,

    Firstly, thanks for this absolute stunner of a solution. It’s pulled my fat out of the fire on more than one occasion; I owe you a beer or several…

    Now, my question: After reading the documentation about making it work with PE using Process Builder, I’ve tried setting it up on a PE org. When I try to set up the process, though, I don’t have the “Call Apex” option when adding my Actions.

    It looks like Laura McRaven may have had a similar issue a month ago. Following the links in the conversation, it looks as though this will only work in PE if you have API enabled. Am I reading that correctly?

    As a workaround, I’ll probably use the scheduled option (assuming that works OK…) – Might i suggest you add a note to this effect on the listing where Professional Edition is referenced, for future folks? 😉

    Still a fantastic tool, mind…

    • Thanks for the feedback most kind. I did not know enabling the API in PE resolved the lack of Apex Call issue. Thanks for sharing that, I clarify, would you mind contributing that to the GitHub issue as reminder to me please. Thanks again!

      • Ooh, I wouldn’t go so far as to claim it as a fix. It was put forward as a solution in one of the conversation chains out there; I haven’t actually tested it myself, not having ready access to an instance of PE with API…

        I’ll ask around. I daresay one of our clients is using that combination. If so, it shouldn’t take long to test. I’ll keep you posted. 😉

  16. Hey Andrew,

    Thanks for all your help!! Works great!!

    I have created various Rollups on my sandbox and tested.

    My question is how do i deploy these to production from my sandbox, rather than recreating them all.

    Thanks!!

    • You can use the Manage Rollup Summaries tab to define your rollups. This way both your triggers (created via Manage Child Triggers) and the rollups are available to your Change Sets

      • Thanks Andrew. However, my component ‘Lookup Rollup Summary [dlrs]’ of the change sets says ‘There are no remaining components of the selected type available to add to your change set.’ Is there a different component i need to choose?

      • That’s correct, but you need to define your rollups via the Manage Rollup Summaries tab.

  17. Hi Andrew,
    Great tool. I was using it in it’s previous version – LREngine, but this seems to have much more capabilities.

    I do have small issue, possible wrong usage.

    We have some rollups with Realtime calculation mode and few more with Scheduled calculation mode. Once in a while, user go to the rollups with type ‘Scheduled’ and press the button ‘calculate’.
    If I understand correctly for using the button there is no need to use ‘Scheduled’ calculation, as there are no need to store the records.
    Should we use ‘Developer’ option instead for such Roolups? can you elaborate regarding value ‘Developer’ calculation?

    Assume we should schedule the RollupJob process to run in some interval and it can calculate all the Scheduled rollup, but in case for those specific rollup users will want to do it manually what should be the calculation mode? (possible they want summaries & updated data only at specific date).

    Thanks,
    Liron

    • https://github.com/afawcett/declarative-lookup-rollup-summaries/wiki/What-you-need-to-know-about-Scheduling-Rollups should tell you all you need to know about the two schedule modes. Calculate button is a one off and can be used to get historic records up to date or force a refresh for example. developer mode requires that you define the rollup as normal but a developer has to call the tools api from their code before it does anything. There is also Process Builder mode, covered in one of the blogs in the doc section of the readme

    • If you’re just looking to have rollups that only get calculated when a user goes and clicks the “Calculate” button, then set the Calculation mode to Developer or Process Builder. Doing that will mean that, without further action, those rollups essentially won’t do anything.

      • Good point thanks Jon for sharing this

      • Thanks for the help

        I think I will use other solution: provide button that will run the Job per request:
        dlrs.RollupService.runJobToProcessScheduledItems();

        Issue with button ‘Calculate’ is it running on all records, as in our case we cannot add filter records.

        Liron

  18. I just went to install DLRS from AppExchange and got this message:
    The AppExchange package has been deprecated and can no longer be installed. Please try installing a newer version, or contact the package owner directly to resolve.

    Is it still available and, if so, how can I get it?

    Thanks… Bob

  19. Andrew,

    First off thank you for this tool! It’s great.

    Second, we have recently updated the tool and now my rollups are not working? Can you point me in the right direction where I might get help with this? I have tried everything I can think of with no luck.

    Thanks!

    • Thanks. I think I responded to this question on the GitHub issues list. Let’s continue to discuss there. Also don’t forget there is an excellent Salesforce Community Chatter group with lots of folks happy to help as well. See link in the readme file in GitHub for this group. Andy

  20. Hi,

    Its a great tool, How many Roll ups we can have using this tool?
    is there limitation to use Rolls ups? Like if i want to create around 10 Roll ups for the same Object.

    Please advise.

    Thanks

    • There is no limit, but performance test everything to many realtime and you can make a big performance impact if not careful. Review the schedule mode page in the wiki also. Also check for indexing on your criteria fields.

      • Hello Andy,

        Thanks you for your kind response.

        Yes, you are right it has caused performance issue, my fields are currency data type and i am using Relatime.

        Can you please advise me what i can do here.

        Thanks

      • Take a look at the schedule modes on the GitHub projects wiki. I recommend considering the incremental schedule mode unless you have some date formulas. It’s all in the wiki page, let me know if it still does not make sense.

  21. Hi,

    Its a great tool, How many Roll ups we can have using this tool?
    is there limitation to use Rolls ups? Like if i want to create around 10 Roll ups for the same Object.

    Please advise.

    Thanks

  22. We see the following error when trying to save when including a specific asset:
    Error: Invalid Data.
    Review all error messages below to correct your data.
    Apex trigger dlrs_CaseTrigger caused an unexpected exception, contact your administrator: dlrs_CaseTrigger: execution of AfterInsert caused by: System.DmlException: Update failed. First exception on row 0 with id 02i1a000000TD2kAAG; first error: FIELD_FILTER_VALIDATION_EXCEPTION, Value does not exist or does not match filter criteria.: [Assigned_Location__c]: Class.dlrs.RollupService.Updater.updateRecords: line 1004, column 1

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