Salesforce is great for making object creation and data collection super easy. One thing it doesn’t make so easy is jumping tables as you MUST use a Master Lookup if you want to use Rollup Summary fields and those are even limited in the functions they can do. You just want to move a field off of a child record onto the parent, or maybe just grab the user name of the last completed activity and write it to the opportunity record that task was related to. Before it would take some fancy apex or other developer style work to accomplish, UNTIL Andrew Fawcett created this amazing tool called Declarative Lookup Rollup Summaries. A bit of a mouthful so I always refer to it as DLRS.
This blog is going to be a super broken down step by step of how to DLRS, the most common issues you might run into with it and a few ways I use it. Pro-Tip don’t try and install this from the app exchange you will find a very out-dated version that no longer installs. So, to install the package head over to the github (https://github.com/afawcett/declarative-lookup-rollup-summaries) and click either Sandbox or Production for the latest version. I’d recommend giving it a spin in your sandbox first.
Now wait for an Email from SFDC telling you the install has finished. You should now see a handful of new tabs and a new app called Declarative Lookup Rollup Summaries.Start off by either loading up the app via the app menu or clicking on the welcome tab. You should see a welcome screen with an error message. Don’t worry it is just the tool noticing it can’t talk to the SFDC API that does a lot of the heavy lifting for the tool. To fix this just click on the “Create Remote Site Setting” button.The tool will automatically create what it needs and the screen should reload and look like below.Now there are two ways to use this tool, both work, pick one, stick with it. If you were to ask Andy he would most likely say to use his cool metadata method because it is slick, smooth, works with change-sets and he also spent a shitload of time making it =). But in the end either way will work. The new cool custom metadata way you’ll want to use the Manage Lookup Rollup Summaries tab. For the old non fancy way you would just use the Lookup Rollup Summaries tab. If you are brand new to this and don’t have any historic DLRS records best bet is to stick with the new custom metadata way.Loading that up you should see a screen like below. We will go over what each of the fields means, and what you should enter into them.If you are using the old way it is very similar you’ll just get a record list and will have to select your DLRS record or click on new.
- Lookup Rollup Summary Name – This is more or less like a field Label. This is the name of the record for easy people reading.
- Lookup Rollup Summary Unique Name – This is like an API name when creating a field. You will want this to be Unique and use _ in place of spaces.
- Parent Object – This is where you will put the API name of the Parent Object. If using a custom object don’t forget about the __c. Think of the Parent Object where you want the data to be written to.
- Child Object – This is where you will put the API name of the Child Object. If using a custom object don’t forget about the __c. Think of the Child Object as the object you want to get the data from.
- Relationship Field – This is the field that holds the ID or PK from the Child to the Parent. Double check your API names as many times the UI will show say Account but double check in the SOAP API Document. Lots of times it might really be AccountId. The magic here is that this can just be a text field, a normal lookup field or a master lookup it can work off any of those. Again if this is a custom field don’t forget the __c =)
- Relationship Criteria – This is where you will write out the WHERE clause of a SOQL query. We use this to make sure the tool only selects the records we want. Example say I want to count all of opportunities that have Closed – Won on an account. There are a few ways to do so but I could go isWon = true or I could say Stage = ‘Closed – Won’. You can use AND statements here if you have multiple criteria and also use OR statements. When using an OR statement make sure to wrap the OR statement inside parentheses.
- Relationship Criteria Fields – This is where we will want to list each field that we used in the above Relationship Criteria on a separate line. We don’t need to copy the logic just need to copy the API Field Names.
- Field to Aggregate – This is the API name of the field on the CHILD object that we want to roll-up.
- Field to Order By – This field only matters if you are using the Concatenate, Concatenate Distinct, Last and First selection for #10 Aggregate Operation. By default if you leave this blank it will just use the Field to Aggregate by. This is a helpful field if you want to select the most recently created record you would put in CreatedDate here so that way the results would be sorted by the CreatedDate field.
- Aggregate Operation – We have a few options here.
- Sum – This will only work on numeric based fields. It takes the sum of your field to aggregate from all of the child records that it finds.
- Max – This will only work on numeric based fields. This will select only the highest value of the field to aggregate from all of the child records it finds.
- Min – This will only work on numeric based fields. This will select the lowest value of the field to aggregate from all of the child records it finds.
- Avg – This will only work on numeric based fields. This will take the MEAN of the field to aggregate from all of the child records it finds.
- Count – This will just do a count of the rows/amount of records returned. When using Count it is normally best to use the ID field in your Field To Aggregate.
- Count Distinct – This will do a DISTINCT count of the values in your field to aggregate. Example is if the returned values are (A, A, A, B, C) in a count it would be 5 but in a count distinct it would only return 3 as there are only 3 distinct values.
- Concatenate – This will only work on text fields and it will grab the values in your field to aggregate and place them value after value into the field you want. You’ll want to use #17 to set how you want the tool to break these values apart either with a comma or a dash etc…
- Concatenate Distinct – This works the same as above but again just like a Distinct Count it will only grab the distinct values. So if we had (Dan, Dan, Dan, Not Dan) with just Concatenate we would return in a text field “DanDanDanNotDan” but with Concatenate Distinct we would only return “DanNotDan”.
- First – This will work with any type of field. This will grab the FIRST record based on how the results are sorted then take that field to aggregate from that record.
- Last – This will work with any type of field and does the same as above expect it grabs the LAST record then will take the field to aggregate from that record.
- Aggregate Result Field – This is the field on the PARENT object where you want the results to be written.
- Aggregate All Rows – This will include records that might be in the recycle bin or are archived by the system. If this box is not checked then records that have been archived or are in the recycle bin will not be counted.
- Row Limit – This only works with the Last and Concatenate Operations but it will let you define a set row count. Say you only wanted to grab the last 5 records you would use the Last Operation and set this to 5.
- Active – This is what activates the DLRS record or not. If this box is not checked DLRS will not apply this rollup function.
- Calculation Mode –
- Realtime – Happens in real-time. When someone makes a change that would trigger DLRS to run, it will calculate the roll-up and change the result on the parent if the result is different than what is already there. You must deploy the child apex trigger for this mode to work.
- Scheduled – This mode will start to collect records at the bottom of each roll-up under the “Lookup Rollup Summary Schedule Items”. You will have to schedule apex to run on whatever cadence you would like by having the system run the Apex Class RollupJob. When this Apex Class runs it will collect all of your Lookup Rollup Summary Schedule Items and process them, then calculating the rollups and updating the parent records. You must deploy the child apex trigger for this mode to work.
- Developer – This mode allows you to call DLRS from your own Triggers/Classes.
- Process Builder – This mode allows you to call a DLRS record from inside Process Builder. When using this mode you DO NOT need to deploy the child apex trigger.
- Calculation Sharing Mode –
- User – The user that triggers the DLRS to be run, the lookup will respect system sharing rules and only calculate the rollup based on what that user has access to.
- System – This acts like Apex or Workflow Rules by running in system and has visibility into all records. Rollups will be calculated based on all child records regardless if the user who triggered the DLRS has the permissions to see all records.
- Concatenate Delimiter – This is how you set how you want your concatenated operation to be split. You can use a , or a | or a line break BR().
- Description – This is where you can leave notes for yourself on the purpose of the DLRS record, what it should be doing and why so that way the next admin who comes across this won’t be totally lost.
- Test Code – If you have custom validation rules or apex in your org that has the manage child trigger running into issues deploying the trigger, you can type in custom test code here.
- Manage Child Trigger is how you can deploy and remove the apex code that this tool needs to run. You only need to deploy this one time per child object that you have DLRS running on. Example if you have 3 DLRS records all running on Opportunity as the child object you need only deploy the trigger one time.
It might take a bit of time to deploy, just wait young grasshopper, soon you shall be rolling up fields like a pro. Once it is done you should see this
at the top of the page.
- Calculate this button will do an entire org recalculation of the dlrs record. This is great for back filling parent records after you create a new DLRS record.
This text box allows us to apply some PARENT object filters. Say we are running a recalculate on a DLRS and the Account object was the parent object. But we only want this back-calculation to be done on Customer Accounts. We could put a WHERE clause here like Type = ‘Customer’ then when it does a system recalculate it will only do so for parent records that meet that criteria. Calculate Jobs are run as Apex Jobs and you can find the status of them in the Apex Jobs section of Setup.
- Schedule Calculate this one is a tricky one. It more or less will automate going into the DLRS record and pressing that Calculate button for you on a set schedule. In those cases where you have time or date values as part of the relationship criteria you will normally want to force a system recalculation every now and then.
Here you can also enter in a Parent Object where clause, and then pick the frequency and time you would like this to run. Once you press Schedule Recurring Calculate Job you can find the job under Scheduled Apex.
Now lets take a look at a sample request someone might have and a real world solution on how to use DLRS. We will not only look at the request, how to build the DLRS we will also try some SOQL queries out to understand how the DLRS tool works.
If you live in the SaaS world then you love to know when your customers might be at risk of churn. One of the signs that a customer account might be at risk is you have no data on customer contacts at the company. Not only do we want to know if we have contacts we want those contacts to have contact roles so we know that if we do have contacts on an account we have a few critical ones that are important when we need to reach out.
In this case we can create a DLRS that will look at all the contacts on an account and see if they are the type of contacts we want then roll a count of how many of those contacts we have onto the Account. We can then do all the fun Salesforce stuff like build a workflow email off that field where if the value is too low it can alert the account owner or a CSM. Below is an example of how we would build this.One thing you might notice is the Aggregate Result Field I have the start of the field name DLRS_. I would HIGHLY recommend naming all the fields you plan to roll values into with DLRS somewhere in the field so other admins or later in life you know that there is apex that relies on that field and it should not be deleted or manually modified.
We set our Parent Object to Account and our Child to Contact. We know that the field on the Contact that holds the Account relationship is called AccountId. How did we know that? We double checked here. Then we know we want to make sure that we are only looking at customer accounts, but DLRS is limited to relationship criteria on the child record only. So I created a formula field that brings down the Account.Type onto the contact record. Pro-Tip changes to Formula fields don’t cause a record edit so if this Account became a non-customer that would not cause the DLRS to re-fire and re-calculate the field on the parent.
Next we want to make sure the contact record isn’t old and they are still with their company. Then we have a list of contact roles that are important to use. So we build up the WHERE statement of
(No_Longer_With_Comapny__c != TRUE AND Contact_Role__c INCLUDES ('Operations Contact', 'External Community Manager', 'Internal Community Manager', 'Primary Contact') AND Account_Type__c = 'Customer')
Thats a big statement, first if you are not familiar with != it is the same thing as NOT EQUALS or <>. Includes is there because Contact_Role__c is a multi-select picklist so we need to just see if that text is anywhere in the field. Then Account_Type__c = ‘Customer’ is because we don’t need this DLRS to run unless they are a customer. Now lets see how this works from a SOQL point of view.
If you have never opened the Developer Console click on your name in the top right and you should be able to select Developer Console. A small popup window should appear, in the bottom area you’ll want to select Query Editor. Then lets create our SOQL query. We already have our WHERE in our Relationship Criteria. Now for the start of it lets put
SELECT [Field we want to rollup cept without the brackets] FROM [Child Object] WHERE [Relationship Criteria] AND [Relationship Field] = '[Id of a parent record to use for testing]'
In our case it looks like this
SELECT No_Longer_With_Company__c, Contact_Role__c, Account_Type__c FROM CONTACT WHERE (No_Longer_With_Company__c != TRUE AND Contact_Role__c INCLUDES ('Operations Contact', 'External Community Manager', 'Internal Community Manager', 'Primary Contact') AND Account_Type__c = 'Customer') and AccountId = '0015000000os367'
This is what we get when we press the execute button in the bottom left.Now in this case we are just using a count so it will just count the total rows. In this case we can see the results have a total rows of 2, so in theory our DLRS results field should show 2. This is a great way of checking if the issue is with DLRS as a tool, your data, or your SOQL query.
In this case I then built a formula field that returns a checkbox and if the DLRS Result field of IF(DLRS_At_Risk_Contact_Count__c > 1, False, True). This way if we have more than 1 result it wont mark the account at risk for low contact count and if it is below 2 then it will mark the checkbox that fires off some workflow rules.
With that you should be more than ready to start building your own DLRS records! If you have any issues come to the DLRS community and plenty of people will be more than willing to help you troubleshoot any issues and problem solve on creative solutions to issues you might be having with the tool.