Apprentice Hoots: Achieving data insights by summarising data with Salesforce roll-up summary and formula fields
Understanding statistics at a high level where all the key information is captured in one place is an important overview to have within a system. Find out how I used the universal roll up summaries, formula fields and validation rules to capture a summary of key data from an event in a customer’s Salesforce system.
Using roll up summaries to calculate the sum of a value from a related list
Roll up summaries are a great way to display a sum, minimum or maximum value of multiple records from a related list.
In this use case, the opportunity had a related list called bookings. On each booking, the customer wanted to capture how many anonymous guests they plan on bringing with them. On a higher level I needed to capture the total number of attendees, including the booking member, the adults and the children they were bringing with them.
With these requirements taken into consideration, I created three roll up summaries.
- Roll up summaries to count the number of booked and paid bookings
- This required a roll up summary on the Opportunity with opportunity as the master object and bookings as the summarized object
- I selected the COUNT rollup type with the filter criteria as Status equals Booked or Paid (as we did not want cancelled or waiting list bookings counted)
- Roll up summaries to calculate the total SUM of all the adult and children across all the bookings
- This required two roll up summary fields for each field (one roll up on the adult and the other on the child) on the Opportunity with opportunity as the master object and bookings as the summarised object
- This time I selected the SUM roll up type with + adults or + children as the field to aggregate with the same filter criteria as the first roll up summary.
- Formula field to calculate the total attendees
- This required a simple number formula using the add operator and all 3 fields: number of bookings, extra adults and extra children.
Developing a checkbox formula field to display as true automatically when criteria is met
Our customer wanted a checkbox to display as ‘full’ when the total attendees met the maximum number of potential attendees. This required a formula field with a checkbox return type on the opportunity that if the total attendees is more or equal to the maximum attendees to have the checkbox ticked.
The formula looked like this: IF( Total_Attendees__c >= Max_Attendees__c , TRUE, FALSE)
Creating a validation rule to display an error message
Now we had the full checkbox in place, we wanted to ensure when the booking was full that an error would display and prevent the user from booking anyone else. I used a validation rule on the booking object to do this.
This validation rule was very similar to the formula field except as we were now on the booking object we needed to make sure we were referencing back to the opportunity, as well as only flagging if the new booking status was going or paid, as we still wanted waiting list bookings to be booked.
The formula looked like this: IF( Opportunity__r.Max_Attendees__c < Opportunity__r.Total_Attendees__c + adults__c + children__c && TEXT( Status__c ) = “Going” , true, false) || IF( Opportunity__r.Max_Attendees__c < Opportunity__r.Total_Attendees__c + adults__c + children__c && TEXT( Status__c ) = “Paid” , true, false)
Putting this together to make a complete booking system
Finally I added the new fields onto the event opportunity compact layout to appear at the top of the screen as an overview so now we have an overview of all the details from the bookings related to the opportunity, calculated on the opportunity itself, the ability to see if the booking is full and ensuring that bookings outside of waiting list bookings cannot be booked once the booking is full.
Owl see you later…
Charlotte
