Account Notification and Filtered Related List with Conditional Visibility
Yesterday, my team and I were presented with a challenge:
How can we make it obvious to anyone viewing an Account that the Account has upcoming renewals?
We knew we wanted to use Lightning's Component Visibility feature to show a new section on the Account page that contained a notification and a list of the renewals. It sounds like a simple problem, but it revealed a current limitation of the platform. There isn't a standard component (Related List or List View) available that supports additional dynamic filtering. Because we store renewals as a Type of Opportunity I assumed that I was going to need to build my own Lightning Component to solve this one... until I came across this brilliant post by Doug Ayers called Filter Related Lists without Code.
The solution presented in Doug's article gave us a perfect way to get a filtered Related List by populating a new lookup field on the Opportunity. But, how could we update all existing Opportunities that meet our criteria, flag all future Opportunities, and remove Closed Opportunities?
To overcome that hurdle we utilized another of Doug Ayers' ideas! We used Mass Action Scheduler to grab all of the records that met our criteria and pass them to a Process Builder flow to populate our new lookup field. We scheduled the job to run daily and we added another Process Builder flow to remove the value from the lookup field when the Opportunity was Closed. This combination keeps the data in our new Related List accurate.
The last step was to only show our notification and new Related List when the list contains records. For this we turned to one of our most heavily utilized tools Declarative Lookup Rollup Summaries by Andrew Fawcett. This tool allowed us to store a count of Opportunities that had a value in our new lookup field. We used this count in Component Visibility to dynamically show our new Related List of upcoming renewal Opportunities.
By combining all of these declarative steps we were able to build a solution to our problem without code in less than an hour. For bonus points we wrote a tiny lightning component to make the notification look nicer with the lightning:card component and the action:priority icon.
Here is an overview of the steps:
- Add a new lookup field on the Opportunity named Upcoming Renewals that looks up Account (do not put on page layout)
- Add a number field on the Account to hold named Upcoming Renewals Count to act as a roll-up summary via DLRS
- Create a list view or report to produce a list of Opportunities with IDs
- Add a new Invocable Process Builder flow to set Upcoming Renewals equal to Account.Id
- Add a standard Process Builder flow to remove the Upcoming Renewals lookup value when the Opportunity is Closed
- Schedule a Mass Action Scheduler job to call your Invocable Process Builder flow for each record in your list view/report
- Create a real-time DLRS summary trigger to produce a roll-up summary count of Upcoming Renewals
- Utilize component visibility in Lightning App Builder to only show the notification and Upcoming Renewals related list when Upcoming Renewals Count is greater than zero
- (Optional) Create a Lightning Component to make your notification look nice
Here are the detailed steps with screenshots:
- New Lookup Field named Upcoming Renewals on Opportunity
- New Number Field on Account named Upcoming Renewals Count
- New List View on Opportunity named Upcoming Renewals - 90 Days
- Add an Invocable Process Builder Flow that populates the Upcoming Renewals lookup field with the Account Id
- Add a standard Process Builder Flow that removes the Upcoming Renewals lookup field value when the Opportunity closes (this keeps the Related List clean as Opps get closed)
Declarative Lookup Rollup Summaries (DLRS)
- Create a new realtime summary on Account that is a count of Opportunities that are related via the Upcoming_Renewals__c look field
Mass Action Scheduler
- Utilize the List View Upcoming Renewals - 90 Days to get our list of records
- Invoke the Process Builder Opportunity - Set Upcoming Renewals
- Schedule accordingly - we will run daily
Lightning App Builder
- Add a Related List - Single component (be sure to add to Page Layout first) that references our new Upcoming Renewals related list
- Add a Rich Text field to help draw attention to the section of the page
- Set Component Visibility based on Upcoming Renewals Count > 0
(Optionally) You can create a simple Lightning Component to make your notification look nice.
After putting all of these pieces together, the Account page will now draw attention to the fact that the Account has upcoming renewals and display a full list of renewal Opportunities with a close date in the next 90 days. As these Opportunities are closed they will be removed from the list and the section will no longer show on the page.