The problem
Let’s start with a little story…
Winn Ing – EMEA Sales Team Member
“In the past 12 months I sold some widgets to ABC Technology. The sale went well and I’m pretty sure they like me 😃
I venture that I could probably sell them some more widgets. I get on the phone, set up a meeting and rock up feeling confident, only to be met with a room full of stony faces.
Something is very amiss here. ABC Technology inform me that since they purchased their widgets they have had to open tons of support requests and they currently have an escalated case open! I wish the floor would swallow me up.
Why did no one tell me about these issues?!?!”
“In truth, there should have been no need to inform Winn about the issues with her customer. The cases are logged on Salesforce so the information was available to her the whole time.
It would be great if I had an easy way to highlighting service issues to sales users…somewhere they actually look in Salesforce?!”
Prerequisites
👉 Install Declarative Lookup Rollup Summaries (DLRS) from GitHub
👉 Read Getting Started With DLRS before starting this tutorial
👉 Install Graphics Pack by Salesforce Labs from the App Exchange
👉 Part of the solution is to create an image formula that displays a picture warning users there are open escalated cases. After installing Graphics Pack, choose an image and follow the Graphics Pack instructions to find the document ID which will be used in our formula.
The solution
We want to be able to see service information (cases) for an account on any opportunity. We can achieve this by first using DLRS to roll up the values from cases to the account, and next using formula fields to bring these values over to any related opportunities.
We do not have the option to use the standard ‘Rollup’ field type on the account as we can only rollup opportunities and entitlements in this way.
Once we have our information ready, we can then display it somewhere prominent on the opportunity Lightning record page and use component attributes to ensure it only displays when relevant.
It sure sounds like a lot of work but I promise you can achieve all this within an hour or two.
- Make sure you’ve completed the prerequisites above!
- Navigate to Setup > Object Manager > {Accounts} > Fields & Relationships
- Create 3 new fields on the Account object – make them visible to all profiles and display on the Account page layout:
- Data Type = Number
- Field Label = Open Escalated Cases
- Length = 18
- Decimal Places = 0
- Enter a description and help text
- Data Type = Number
- Field Label = No Of Cases Created This Year
- Length = 18
- Decimal Places = 0
- Enter a description and help text
- Data Type = Number
- Field Label = Open Cases
- Length = 18
- Decimal Places = 0
- Enter a description and help text
- Navigate to the DLRS App (click on the 9 dots to see all your apps and objects)
- Click on the Manage Lookup Rollup Summaries Tab
- The screen will default to a blank template to allow you to create a new Lookup Rollup Summary
Helpful hints:
- You need to manually enter the ‘Lookup Rollup Summary Unique Name’ which is your ‘Lookup Rollup Summary Name’ with underscores between each word
- The ‘Aggregate Result Field’ (the field you want your information to rollup to) should be the API name – that’s the one with underscores!
- ‘Calculation Mode’ should be realtime
- Calculation ‘Sharing Mode’ should be system (this will bypass any privacy restrictions)
- Leave ‘Active’ un-ticked on the first rollup until you have deployed the child trigger via the ‘Manage Child Trigger’ button; then go back and activate the rollup (simply tick ‘Active’ and ‘Save’)
- “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.” Source: Getting Started With DLRS
- The last step is to click ‘Calculate’ to run the rollup – this may take a while and I recommend doing one rollup at a time and making sure the calculate job has finished before doing the next one
- Create the ‘Total Open Cases’ DLRS:
- Create the ‘Cases Created This Year’ DLRS:
- Create the ‘Open Escalated Cases’ DLRS:
- Make sure you have run the calculate job on each rollup by clicking the ‘Calculate’ button!
- Check your results by navigating to an account record and see if your fields have values in them (if yes then move on with the next steps, if no then try running the calculate job/s again)
- Navigate to Setup > Object Manager > {Opportunities} > Fields & Relationships
- Create 3 new fields on the Opportunity object – make them visible to all profiles but DO NOT display on the Opportunity page layout:
- Data Type = Formula
- Field Label = Open Cases
- Formula Return Type = Number
- Decimal Places = 0
- Enter a description and help text
- Enter the following formula but use ‘Insert’ to ensure you use the correct API name:
Account.Open_Cases__c
- Data Type = Formula
- Field Label = No Of Cases Created This Year
- Formula Return Type = Number
- Decimal Places = 0
- Enter a description and help text
- Enter the following formula but use ‘Insert’ to ensure you use the correct API name:
Account.No_Of_Cases_Created_This_Year__c
- Data Type = Formula
- Field Label = Warning Escalated Case!
- Formula Return Type = Text
- Enter a description and help text
- Enter the following formula but use ‘Insert’ to ensure you use the correct API name and replace the document ID (in red below) with your unique ID (hint – check the prerequisites section for where to find it!):
IF( Account.Open_Escalated_Cases__c > 0,IMAGE("/servlet/servlet.FileDownload?file=0151o000005dxHe","Warning",16,16), "")
- Once your new fields are created, navigate to Setup > Object Manager > {Opportunities} > Buttons, Links, and Actions > New Action
- Choose action type ‘Update a Record’, give it a name e.g. “Service Issues”, a description and save
- Remove any fields you do not want to display (don’t worry about any fields having a red asterix)
- Add the fields ‘Open Cases’, ‘No Of Cases Created This Year’ and ‘Warning Escalated Case!”, then save (I also added my SLA field that I created in the previous tutorial)
- A warning message will appear – select ‘Yes’
- Navigate to your Lightning record page (Setup > Object Manager > {Opportunities} > Lightning Record Pages) and select ‘Edit’
- From the list of components (left) drag and drop a Related Record component somewhere highly visible on the canvas
- Select the component by clicking on it and set the component attributes:
- Header Label = Service Issues (or similar)
- Lookup Field = Use This Opportunity
- Update Action = Service Issues (select the quick action you just created)
- Optionally, add some component visibility criteria so it only appears when relevant
- Scroll down to the bottom of the component attributes column and select ‘Add Filter’
- Field = Open Cases
- Operator = Greater Than
- Value = 0
- Scroll down to the bottom of the component attributes column and select ‘Add Filter’
- Click ‘Done’
- Save, and if required, activate the page
The result
A clear warning to your sales users when there are ongoing service issues!
You could take this further by:
👍 Only displaying the Service Issues component on open opportunities
👍 Displaying a positive image (smiley face anyone?!) if there are no open, escalated cases (currently it will just be blank)
Gotchas & hints
*Our ‘Cases Created This Year’ DLRS query is using THIS YEAR. This is great but these don’t automatically calculate at the start of the year; the figures are only updated when the Case is edited. In layman’s terms, this means that at start of each new year, you need to manually force a mass update of all cases. You can go back to your DLRS queries and use the Schedule Calculate button to recalculate all the records.
*DLRS is probably the BEST thing I’ve ever installed as a declarative Admin…that being said, it can be a little confusing initially, so do join the Trailblazer Community Group or contact me if you need help with this tutorial!
Useful links
Declarative Lookup Rollup Summaries GitHub
Counting Tasks with DLRS by Dan Kludgy
Declarative Lookup Rollup Summaries Tool Trailblazer Community Group
Resources
Total Open Cases Relationship Criteria:
Status 'Closed'
Cases Created This Year Relationship Criteria:
CreatedDate = THIS_YEAR
Open Escalated Cases Relationship Criteria:
IsEscalated = TRUE AND Status 'Closed'
The Everyday Admin 
Oh my gosh I love this idea, however here is a warning for you when you go to implement this solution. Be careful about your wording when putting the comments in. If you are ever in a position where you share all your information about a contact with that contact (from federal regulations, etc), having a flag on that account that states “We have a badass over here” or “unhappy customer” could look not so kindly to that customer. Perhaps there may be gentle words to use on the account that would convey the same message internally and yet show the end customer that you still care.
however, I LOVE the technical solution and I will most likely use this for some of our customers who are regular scofflaws. We have folks who love to get free stuff and not pay for them. Having some way of visually showing those on the screen for our agents would be great! Thanks. .
LikeLike
Thanks for your comments. Glad you liked the post!
LikeLike
Hi, I use the DLRS for the first time and have in the error logs CUSTOM VALIDATION RULES EXCEPTIONS. Any way to give a bypass to a DLRS job ? Our validation rules have a line with a BypassValidation
AND(
NOT($User.PAD_BypassValidation__c),
validation rule condition=true
)
Many thanks in advance.
LikeLike