Roll-up summary fields

In this section, we'll dive deeper into the use of roll-up summary fields. Let's summarize what we already know about them:

• A roll-up summary field is a type of field only available on a master-detail relationship's parent object.
• It is used to perform a calculation based upon a numeric value of child records.
• A roll-up summary field will grab information from related child object records and perform some calculation with them. Possible calculations are COUNT, SUM, MIN, and MAX.
• It is recalculated whenever you insert, update, or delete one or more of the children, whenever a detailed record is saved to the database.
• You can create up to 25 RUS on a per object basis. This can be increased exceptionally, by submitting a case at Salesforce support, to a maximum of 40. 40 RUS on one object is the hard limit at the time of writing!
• While a normal formula field is calculated depending on reading time, a roll-up summary field is recalculated when saving of one or more child records that it is based on. This has as an extra benefit in that the change of the value in a roll-up summary field can trigger automation features, such as workflow rules, Process Builders, or Apex triggers.

Examples of roll-up summary field use include the following:

• To SUM the total number of invoice records in the account
• To COUNT the number of opportunities an account has
• To show the highest opportunity value on the account using the MAX calculation
• To show the lowest review rating a movie has using the MIN calculation

It's important to know that you don't always have to use all child records in your calculation. There is a possibility to filter child records that you want to include (for example, make the SUM of all Unpaid invoice records and show it on the account). This way, you can exclude paid invoices from the calculation and show only the total amount still due.

Roll-up summary fields can be created on the following:

• Custom objects that are on the master side of a master-detail relationship.
• Standard objects that are on the master side of a master-detail relationship, and where a detail side is a custom object.
• Opportunities for rolling up values of related opportunity products.
• Accounts rolling up values of related opportunities. (This is a special case, as the relationship from an opportunity to account is a lookup relationship, but it acts as a master-detail relationship. As an administrator, you can't create such a field yourself. These are special fields provided by Salesforce at the core of Salesforce CRM.)
• Campaigns rolling up values of related campaign members.

To get a better grasp of the RUS field concept, we'll be creating two RUS on our Movie object and a formula field that performs a calculation based on those two RUS. We can do that because we created a master-detail relationship between Review__c and Movie__c. To explain further, we consider that we would like a field called Average Rating on our Movie object, but an RUS field has no AVERAGE option:

1. So, first, we'll create a roll-up summary field called Nbr of Reviews, which counts the number of review records. Then, we'll create a second RUS called Total Rating, which will be the SUM of all ratings of all review records. Then our formula field, Average Rating, will divide the Total Rating by the Nbr of Reviews and give us the average rating.
2. So, go to Setup | Object Manager | Movie Object and click the New button to create a new custom field. Then, select the Roll-up Summary as the data type.
3. Fill in the label with Nbr of Reviews and make sure the API name is Nbr_of_Reviews and hit Next. In the object to summarize, select Reviews, Roll-Up Type as COUNT, and leave filter criteria as All records should be included in the calculation. Hit Next. See the following screenshot:

1. Hit Next, and then Save, leaving all the defaults for page layout and field level security.
2. For our next RUS, we have a small problem. Calculations except COUNT can only be performed on fields containing either a number, currency, or percent as a data type and we want to base it on our Rating__c picklist. Our picklist only contains numbers as values (1, 2, 3, 4, 5). A picklist is always perceived as TEXT.
3. To solve this little situation, we will first create a formula field on our Review object, converting the value of our rating picklist into a number.

1. So, from Setup | Object Manager, select our Review object and click the New button to create a new custom field. Choose Formula as the field type, Number as a return value (be sure to set the number of decimal places to 0), and call your field Rating To Number. In the advanced formula editor, we will insert a formula to convert the text from our rating picklist into a number. It's a good thing that Salesforce provides us with a plethora of functions with common calculations to perform. Check out the VALUE function in the Functions menu on the right-hand side of the formula editor:

1. The VALUE function converts a text string that represents a number to an actual number data type. And that's exactly what we need. So, select the VALUE function and click the Insert Selected Function button. This will insert the syntax into the formula editor. Now, we need to replace the text placeholder with our picklist field rating. So, remove the text placeholder and make sure your cursor is placed between the parentheses. Click the Insert Field button, and you'll find yourself on your base object, which is Review, and, in the second column, you've got all fields from the Review object. In there, select the rating field and hit Insert. Your formula should now look like VALUE(Rating__c). Now, hit the Check Syntax button to see whether there are any errors. Whoops:

1. You'll encounter this several times in your career. A picklist is a special data type because it comes with some specific functions, such as ISPICKVAL(), to compare against one specific value chosen in the picklist. So, therefore, we need to actually specify that we want whatever TEXT is chosen from the picklist to be converted. To do this, we wrap the picklist field within a TEXT() function, so your formula should end up like VALUE(TEXT(Rating__c)). Now, adapt it and check the syntax again. There should no longer be an error, so you can hit Next and Save, leaving all the defaults.

Sorry for this little sidetrack, but, hey, that's development on Salesforce for you! Sometimes, you start with building out your solution and hit a little bump on the road that you'll need to solve.

1. Now that we have this actual number on our review, let's continue with our initial requirement and create the second roll-up summary field that will calculate the SUM of all ratings. Go back to the Movie object (from Object Manager in Setup, choose the Movie object) and click the New button to create our second RUS. Select Roll-Up Summary as the data type. In the field label, fill in Total Rating as the label and the API name should be Total_Rating. As we did before, select Reviews as an object to summarize, but now set the Roll-Up Type to SUM and choose our special formula field, Rating To Number, as a field to aggregate.

1. Hit Next and Save, leaving the defaults for page layouts and field-level security:

1. We are almost there now. The last thing we need is the actual average calculation! We stay on the Movie object in Setup | Object Manager, and click once again on the New button to create our last custom field. Select Formula as the data type, which will return a Number (with 0 decimal places), and call it Average Rating with the API name Average_Rating. Hit Next. In the formula editor, write down the following formula:

Total_Rating__c / Nbr_of_Reviews__c

1. Hit Next and Save, leaving all the defaults for the page layout and field-level security.
2. Now, let's check out our fields by navigating to our The Godfather movie record (you should know how to get there by now!). And there you go:

Because we said 0 decimal places, the Average Rating is rounded to the nearest whole number. If you would like to see 4.25, you could edit the formula field Average Rating to show 2 decimal places if you want. I'm going to leave it as it is.

Also, you have already learned how to modify your page layout, so you could clean the page up a bit, by placing those fields somewhere else on the page layout and maybe removing the Total Rating field from the page layout, as it is actually used purely for the calculation of the average in the background. Having a Total Rating doesn't tell the user much, right? But I'll leave that completely up to you. 

Let's move on!