Andy in the Cloud

From BBC Basic to Force.com and beyond…

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

  1. Eeeeeeeek! This just saved me. I’ve been trying to figure out a solution on my own for weeks and after enough googling, I stumbled upon your post and your solution. Thank you so much for sharing this with the community!!!

    FYI – here’s the scenario I’ve been struggling with that this solved: We have created an opportunity hierarchy using a Parent Opportunity lookup field. We want to be able to report on the total value of child opportunities on the parent opportunity. This tool worked perfectly.

    [doing a happy dance]

  2. Hello Andrew,

    Is your tool work with standard objects like AccountTeam or opportunityTeam?

    Thx

    Regards,

    • Yes, some only work in scheduled mode though. Try it out and raise issues in the Chatter Group linked on the readme file.

  3. Please Help! I am unable to get this to work in my sandbox. When I attempt to Calculate I get an error indicating the Rollup must be “active” but I am not seeing anywhere to set it as active or not??

    • Also I find it strange that when I try to use the Enhanced Edit – the Child Field to Aggregate picklist does not contain the field on the child object that I want to summarize – I am able to specify it when I create the rollup…but it is not available in this enhanced edit mode.

      • This could be a bug. Have you seen that there is a Chatter Comminity group linked on the Readme file. Lots of help in that group also. You can also search for known issues on the GitHub page for this tool. Finally try to use the Manage Rollup Summaries tab this is the recommended way to define rollups and is backed by custom Metadata that allows you to migrate your rollups via change sets

    • This is a field on the rollup definition

  4. Ok – I think Im on the right track now…seems I installed a WAY old version of the tool. Updated it and am currently waiting for the first calculate job to complete – THANKS!!

  5. I am using Account and a custom child object. I can’t get it to work in Real Time, but I can get it to work when I do Calculate and put in a simple Name LIKE … query. Is there something I could be leaving out ? It’s activated, and I have indeed chosen Real Time. I’m not using any of the optinal fields such as Relationship Criteria or Relationship Criteria Fields.

    • In realtime mode the tool needs to know what fileds to watch for changes in. By default this is the field your aggregating. So if that changes the realtime recalc will be done. If your changing other fields, you need to list them in the Child Relationship Fields field (if you don’t have a filter).

  6. ah ok – that makes sense. So I guess it will work in Schedule then ?

  7. Hi Andrew,
    Thanks for sharing. I have a difficulties with deploying Apex Trigger. Would you please advise of how to resolve this issue?

    Error:
    dlrs_SCMC_Production_OrderTest.testTrigger System.DmlException: Insert failed. First exception on row 0; first error: FIELD_CUSTOM_VALIDATION_EXCEPTION, Item is required: [SCMC__Item_Master__c] Class.dlrs.RollupService.testHandler: line 272, column 1 Class.dlrs_SCMC_Production_OrderTest.testTrigger: line 11, column 1

    Warning:
    dlrs_SCMC_Production_OrderTrigger:Test coverage of selected Apex Trigger is 0%, at least 75% test coverage is required

    • Take a look at the wiki on the GitHub repo it has an article around code coverage and test code needed in cases like this. The default test code the tool generates does not comply with the validations in your child object.

  8. We are using this to calculate a value that is an ‘average’. (i.e. the average number of match items for all child records in a group). The values returned are frequently more than 2 decimals, however, and we want to display them in an email {mere field} with 2 or less decimals. The Salesforce fields show 2 decimals when viewed on the account or in reports, but the merge field is pulling more. How can we limit the output from the rollup summary so the merge field displays correctly?

  9. HI Andy
    I am getting error when i deploy the code. code coverage is not done please can you help me.

  10. Hi Andrew ,

    is there any limit with this app.

    i am not getting new button after clicking on lookup rollup summary tab.

    thanks
    rajni

    • There are two ways to create rollups. Recommended is via the “Manage Rollup Summaries” tab. This supports custom Metadata and allows for easier migration of rollups from sandbox to production. The “Lookup Rollup Summaries” tab is the original option. You must manually copy over rollups from sandbox to production here. If you do want to use that option, keep in mind when opening that tab to see New you need to click Go and have the correct permissions assigned, these are permissions sets under setup for the tool. Hope this helps.

  11. Hi Andrew, this has proved VERY useful!

    I’m not the most experienced with this stuff, I’ve got a Roll-up to sum all “donations” (a custom object) for an activity but can’t find a way to make the same field but only for those ‘donations’ within a time frame, I.E a financial year. I’d love to be able to set it up and it update the dates for the FY each year rather than enter dates every April, but I can’t even get it to understand “equal to or greater than ___ date and Equal to or less than ___ date.

    Any ideas?

    Thanks so much for this AMAZING tool! 🙂

  12. Hi Andy,

    I am trying to rollup Article (child) information to a Case (Parent) custom field but I get an error:
    Error:
    triggers/dlrs_CaseArticleTrigger.trigger (Line: 4: Column:36) : SObject type does not allow triggers: CaseArticle

    Cases can have multiple articles attached. I want to capture the date/time of the article that was created the last one.
    Salesforce stores this information in the CaseArticle table and the Rollup Helper tool displays it but it does not work well in our org.
    Knowledge Articles do not support triggers, so this is where the error may come from?

    Thank you for your support!
    Julian

    • Same error message appears when using the API name of my article type (HSM_Article__kav):
      Error:
      triggers/dlrs_HSM_Article_kavTrigger.trigger (Line: 4: Column:40) : SObject type does not allow triggers: HSM_Article__kav

      The strange thing is that in both cases I also get, right below the error message, another message saying “Deployment Complete”

      However the roll ups do not work

    • Yes this is correct, your only other option is to use the full schedule recalc mode. See the wiki article on the GitHub project for more.

  13. Hi Andy – This is a fantastic product and I am finding a number of uses for it. Thanks for creating this. Gotta question for you. In the scheduled job, I’ve added a filter “Rep_Type__c = SOB AND Status__c = Active”. After saving, the filter no longer shows. Only the example “Name LIKE ‘Fred%’ AND Custom__c > 0” shows. Even though it doesn’t show, will my filter still apply to my scheduled jobs?

    • Yep it will

      • Thanks. One last question. I am rolling up sales rep activity onto a custom object called Agent. There are 6 fields which I rollup data to (calls, quotes, sales, etc.) Each sales rep has 2 Agent records. One for Inbound work and the other for Outbound work. When the trigger fires, some of the data gets added to the Outbound record and the other data is added to the Inbound record. For example, calls and quotes get added to the Outbound record and sales # gets added to the Inbound record. Is there a way to set it up so that only the Outbound record gets updated with all of the data? Or, is there a way to update both records with all of the data?

      • So you have Custom object > sales rep > outbound and Custom object > sales rep > inbound object relationship? If so you need to define a rollup between each relationship to bubble the information up to the custom object you need

  14. Hi there and thanks so much for this awesome tool. I am using it for the first time and although I think I set this up correctly it seems that the rollup isn’t populating. Basically I created the rollup and have connected it using the manage child trigger. I have set all the fields and checked the code in my relationship criteria and listed all of the criteria fields on seperate lines, the rollup summary saves and is activated. The figure isn’t showing up and not sure why, I created a lookup link and a new custom object to place these rollups and its still just blank in there.

    Its a one person team here from a primary school in new york so any help and steps much appreciated.

    Any help much appreciated.

    • Also I should add i used a currency field as the field that will be rolled up to should I have used a text field?

    • Do you have a realtime rollup or scheduled? If it’s the later check the wiki on the GitHub repo for further instructions.

      • Thanks! OK so this is working now, amazing. I do have another issue though. I am using a process builder to update the lookup field I have with the same id each time a record is created so I can roll-up these figures to a specific object. That works great up to a point but once I tried to add a couple more I am getting an error and the records cannot be saved at all. When I delete the newly made roll-ups it works again.

        Is there a limit to how many roll-ups I can create before process builder cannot update the field it needs to? Its such an odd issue but I do have about 35 roll-ups currently. If I were to schedule to roll-ups would I have more success do you think? They only need to run once a month. I have the error message here below if you think its useful to see.

        Error Occurred During Flow “PRRU_000001date_Purchase_Request”: The flow tried to update these records: nul…

        Error element myRule_1_A1 (FlowRecordUpdate).
        The flow tried to update these records: null. This error occurred: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY: dlrs_Purchase_Request_FormTrigger: System.LimitException: dlrs:Too many SOQL queries: 101. For details, see API Exceptions.

        This report lists the elements that the flow interview executed. The report is a beta feature.
        We welcome your feedback on IdeaExchange.

        Flow Details
        Flow Name: PRRU_000001date_Purchase_Request
        Type: Workflow
        Version: 1
        Status: Active

        Flow Interview Details
        Interview Label: PRRU_000001date_Purchase_Request-1_InterviewLabel
        Current User: Aaron Louie (005G0000007BpTo)
        Start time: 9/19/2017 12:42 PM
        Duration: 1 seconds

        How the Interview Started
        Aaron Louie (005G0000007BpTo) started the flow interview.
        Some of this flow’s variables were set when the interview started.
        myVariable_old = null
        myVariable_current = a4s0f000000XyTaAAK

        ASSIGNMENT: myVariable_waitStartTimeAssignment
        {!myVariable_waitStartTimeVariable} Equals {!Flow.CurrentDateTime}
        Result
        {!myVariable_waitStartTimeVariable} = “9/19/2017 12:42 PM”

        DECISION: myDecision
        Executed this outcome: myRule_1
        Outcome conditions: and
        1. {!formula_myRule_1} (true) Equals true
        Logic: All conditions must be true (AND)

        RECORD UPDATE: myRule_1_A1
        Find all Purchase_Request_Form__c records where:
        Id Equals {!myVariable_current.Id} (a4s0f000000XyTaAAK)
        Update the records’ field values.
        Purchase_Request_Roll_Ups__c = a2D0f0000016gw2
        Result
        Failed to update records that meet the filter criteria.

        Error Occurred: The flow tried to update these records: null. This error occurred: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY: dlrs_Purchase_Request_FormTrigger: System.LimitException: dlrs:Too many SOQL queries: 101. For details, see API Exceptions.

        Thanks again for this amazing tool

      • This is a platform limit the tool cannot avoid. Generate a debug log and review it carefully or use the dev console and use the Execution Overview pane. What I think is happening is some recursion causing the trigger to fire over and over until the governor fires. Other option maybe to use one of the schedule modes (see wiki before using schedule modes).

  15. I’m having the same challenge with my rollups that aren’t populating. I have one date/time field and two text fields. I suspect it is the aggregate operation perhaps. I’ve tried first, last, concatenate. The others (sum, max, min) gave errors and wouldn’t allow me to select/save the rollup until I changed it.

    I am using this for two custom objects that had a master/detail relationship. When the rollup wasn’t working, I created a lookup relationship between them, but still no luck.

    Any suggestions?

    Thank you,
    Helen

    • I would advise going back to the rollup summary the platform provides and trying that again it is always going to perform better than this tool tbh

  16. Does this work between two custom Objects? I have been trying to get it work between two custom objects but no luck. it works like magic when I chose one of the standard object (wither Parent or Child). could not find anything related to it in the documentations as well. Please suggest.

    • Yes works with two custom objects. Drop your error in the Salesforce Community Group, I and many others can help out then

  17. Hello Andrew,

    We have more than 3 millions records in the object ‘Lookup Rollup Summary Schedule Item’ and i see that this object is used only to keep history, we use declarative lookup between two customs objects and we want know if we can truncate this object because all our fields are filled on the parent object? could you confirm this?

    Regards
    Nadir

    • Yes you can. This object drives the incremental rollup recalcs when using the applicable schedule mode. So it looks like the schedule job is not clearing these or it’s not scheduled. Take a look at the wiki article on the GitHub repo for an explanation of the schedule modes. It sounds like if you have not noticed any rollups not calculating you may have a rollup that’s not needed but still outputting entires that are not needed?

      • Hi Andrew,

        I have one scheduled lookup and it calculate the number of cancelled order(custom object), i have the good value in the agregate result field but records in dlrs__LookupRollupSummaryScheduleItems__c are not deleted.
        How can I know if the scheduled job is executed or not yet plz?

      • Under Setup search for Scheduled Jobs

      • Hi Andrew,
        My job is executed every hour but he didn’t delete this lines from the object dlrs__LookupRollupSummaryScheduleItems__c, i have a scheduled rollup summary between Order__c and Order_Line_Item__c with dlrs__RelationshipCriteria__c = “Status__c = ‘Returned'”, but our schedule rollup summary don’t delete the record after executing of the job? i find the good value expected in my Aggregate Result Field
        If my job is executed, i can schedule a job to delete evry day the records not deleted?
        thanks by advance

  18. Andy,
    Are the triggers that are deployed bulkified? We are running into issues here with Too Many SOQL Queries and the like. I am just reaching out to any vendor that has triggers as part of our managed packages to double-check. Thanks.

    • Yes they are. But… if you have multiple rollups, over different parent objects, different criteria this does end up using different queries, since these cannot be combined into one.

  19. Is it possible to do a Contains operator in any way in the relationship criteria? As in, Field1! Contains ‘text’

    Another way to reach what I’m looking for would be to be able to do an OR on the same field? An example: (Field1!=’abc’ or Field1!=’def’) AND Field2!=’text’

    I tried the above field, which allowed me to save, but it’s not filtering when I run it.

  20. Andrew , What a great tool my recommendations. I’m not a programmer but was basically writing my own trigger when I stumbled on this post. Made my life so much easier. I did not read it all yet but I noticed I built two rules on one objects accounts and when I went to deploy the second object it asked me to delete the first trigger I’d created. eg I wanted to do both count and sum of 2 different fields from the related object ( also an account).

    Thanks again it was great.

  21. sorry it should say when I went to deploy the second rule

  22. Sorry maybe this will answer my own question. when I create the second rule on the same object. does the manage the deployment combine both rules into the one trigger? I’m guessing yes I did not go and check the code but I suspect that is what you will tell me .
    Thanks again great tool.

  23. OK I tested it be re-activating the other rule and sure enough works like a dream.

    All the best. Great tool for experienced admins

  24. This looks awesome. Two questions, can we rollup from a Formula field if that field type is a number on the child object? Also what is the link for the managed package for this so we can install? I could not find it on the AppExchange

    • Yes you can. Refer to the links in the GitHub Readme file for install links. Also see the documentation section for blogs links, there is one on formula field considerations.

  25. @Andrew Fawcett Trying to create a Rollup Field based on of the ParentId field. Basically, I want to know the number of Child Accounts under an account based off the ParentId field. When I trying to setup up the record I am getting the below error :

    Child Object: Object does not exist.

    Below are my Record details :
    Parent Object : Account
    Child Object : Account
    Relationship Field : ParentId
    Field to Aggregate : ParentId
    Aggregate Operation : Count
    Aggregate Result Field : Number_Of_Child_Records__c

    Is there anyway I can get around this 🙂 Thank you in advance

    • That is most odd, Account as a value looks good to me. It’s best to post a screenshot in the Community Chatter Group setup for this tool. See GitHub Readme file for the link.

  26. Andrew

    I have been using this tool for about a year and it works pretty well. I have scheduled jobs that run every hour on four different custom fields that are conducting rollups on the contact record. I have encountered a corner case situation when I merge duplicate contacts, the rollups do not seem to recalculate totals when the schedule jobs runs. I have tried to use the “calculate” button to bring records up to date manually but it throws an exception and says I already have jobs scheduled.

    Thanks for your help to resolve this.

    Paul

    • There is a feature added in a more recent release to support merging. You have to click the Manage Child Trigger Buffon and remove the trigger code then re add them, when you do this for a merge capable object the Manage Child Trigger button generates trigger for the parent and child objects, so the tool can detect merge operations. Please check through the release history of the tool on the Readme for more information.

  27. @Andrew Fawcett – Would this tool help with a Roll up field from the Lightning Enhanced Notes. Trying to put “Latest Note” on an Opportunity based on Enhanced Notes related to that Opportunity. However, I can’t find an Object to specify with Notes. Doesn’t look possible. Is that right?

    • I am not sure tbh, but I think this question has been asked before in the Community Chatter group. See readme for a link to that. If not, I recommend you post a new question.

  28. I’ve left this comment on Dan Kludgy’s post “How to DLRS” as well. I installed the app (version 2.9) in our Sandbox. After creating a Lookup Rollup Summary, I clicked on the Manage Child Trigger button and then chose Deploy I got the following error messages:

    classes/dlrs_AccountTest.cls (Line: null: Column:null) : Not available for deploy for this organization
    classes/dlrs_OpportunityTest.cls (Line: null: Column:null) : Not available for deploy for this organization
    classes/dlrs_OpportunityTest.cls-meta.xml (Line: null: Column:null) : Not available for deploy for this organization
    classes/dlrs_AccountTest.cls-meta.xml (Line: null: Column:null) : Not available for deploy for this organization
    triggers/dlrs_OpportunityTrigger.trigger-meta.xml (Line: null: Column:null) : Not available for deploy for this organization
    triggers/dlrs_AccountTrigger.trigger (Line: null: Column:null) : Not available for deploy for this organization
    triggers/dlrs_OpportunityTrigger.trigger (Line: null: Column:null) : Not available for deploy for this organization
    triggers/dlrs_AccountTrigger.trigger-meta.xml (Line: null: Column:null) : Not available for deploy for this organization.

    Because of that I now can’t save any changes in the Lookup Rollup Summary (to make it active), calculate it or create any new Lookup Rollup Summaries.

    I am using the DLRS to show in the account object a summation of opportunties where a custom field isn’t blank. Do you know of a solution for the errors?

    • What org type are you in? PE is not supported.

      • I was able to resolve the issue. Dan Kludgy had me check to see if I had AuthorAlex permissions. I didn’t. Once I set that permission I was able to get it to work. So great! Thanks for creating this app! One thing I would love to see is a way to rollup a max for dates. It looks like it can only do max for numeric fields. Is that something you hope to create?

      • Of course! Love it when the great community around this tool comes to the rescue! Great enhancement idea, log it on the GitHub Issues page please. Thanks!

  29. Hi, please excuse me if I’m just ignorant or have missed reading this somewhere, but what do I need to do to get the latest version of the app? I installed it ages ago and just checked now – it’s still on v1.22.

    • No problem. I do not do push upgrades. You have to go to the readme file and find the latest link to install/upgrade your package. Please review notes between the releases for post install steps. You can go straight to latest.

  30. Excellent tool, saved my life more than once.
    I have now a question: my need is to write Contact Id (the last created one) on its relative Account.
    Can I do it by this tool without writing any apex trigger by myself?

    I created a custom field (text) on which I am supposed to export the result ID: operation is LAST and Aggregate field would be Account on Contact; but it’s not working.
    Any ideas?
    Thanks again

    • It’s hard to tell with out seeing more. Suggest you share some details on a GitHub issue or a Chatter post Per the group on the readme file

  31. Hello Andrew,

    I have a question on “Schedule Recurring Calculate Job” page – rollupscheduledcalculate

    we have options to select for: Min, Hour, Day of the month, month and Day of the week.

    I have been trying to schedule a rollup job for every 5 min and have not managed to figure this out. It seems the lowest denominator is hourly. Is it not possible to do every 30 min or so?

  32. Hi Andrew,

    I am trying to utilize this tool to count the number of records on a related list in a custom object. I named the parent object the custom object, the child object is accounts, and the related look up field is Referring Attorney. I want to count the number of distinct records in the related list on the custom object so I entered Name in the field to aggregate, and then added a custom field on the custom object is a number field for the aggregate result field. I have activated the summary and deployed the trigger for the child object.

    Nothing is showing up in the custom field on the parent object. Is there something that I am missing here?

    • Assuming it’s a real-time rollup, make sure the relationship criteria fields field is completed so the tool looks for the correct changes in child record edits. It’s hard to advise further with knowing your steps and expected results.

  33. I’m struggling to implement an aggregation that I can do with SOQL but would like to use the declarative rollup tool for instead. Here is my use case:
    – Fund is a child object of Account. Fund has 2 important attributes – Asset Class (picklist) and NAV (currency)
    – For each Account, I need to sum the NAV for all Funds in each Asset Class, then display the Asset Class with the greatest NAV amount on the Account record.
    Here is a SOQL query that works… can this query be implemented in the declarative tool?
    SELECT AssetClass__c, Account, SUM(NAV__c) FROM Fund__c WHERE Account =’0012F00000BqTla’ GROUP BY AssetClass__c, Account ORDER BY SUM(NAV__c) DESC NULLS LAST LIMIT 1

    • Clarification on requirements:
      For each Account, I need to sum the NAV for all Funds in each Asset Class, then display the Asset Class with the greatest SUM(NAV) on the Account record.

      • So for each Asset class create a target field for the rollup for that asset on your Account object. Then define a rollup for each asset class. Then have a formula on Account that resolves the greatest value from the Asset class rollup fields on Account.

  34. Hi Andrew,

    Can I schedule the dlrs I’ve set up by running an Apex script?
    Or this is available only via Schedule Calculate button?

    • Sure you can use the Apex Schedule API or via the DLRS API (see RollupSevice) depending on the schedule mode you are using.

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 )

w

Connecting to %s