Create Formula and Roll-Up Summary Fields

HR recruiters and interviewers have entered lots of data about their candidates into their recruiting app. But for that review data to be useful, they need it calculated and scored. Use rollup summary fields and formulas to accomplish this.

A rollup summary is kind of the reverse of a cross-object formula (which you created in the first step of this project). Instead of getting data from a parent (or grandparent) object, it gets data from records on a child object and stores that data. Results are recalculated and stored whenever any record on the detail record is saved.

Formula fields take input from other fields, values, functions, and operators to produce results. They can reference standard fields, custom fields, and other formula fields. Formula results are not stored—the formula expression is stored as metadata. But the result of that expression is calculated on the fly whenever a user views a record or runs a report containing the formula field.

Create a Formula

Create a custom formula field that calculates the overall score from the Review object.

1. From Setup, click Object Manager, and select Review.
   
2. Click Fields & Relationships, then click New.
   
3. Select Formula as the Data Type, and click Next.
   
4. For Field Label, enter Overall Review Score.
   
5. For Formula Return Type, choose Number.
   
6. Click Next and add the formula under Overall Review Score (Number) = 
   

(VALUE(TEXT(Core_Competencies__c)) + VALUE(TEXT(Experience__c)) + VALUE(TEXT(Leadership_Skills__c)) + IF( Recommend_for_Hire__c , 5, 0) ) / 4

7. Ensure your screen looks like this:
   
8. Click Check Syntax to verify that there are no errors.
   
9. Click Next, Next, Next, then Save.
   

Create Roll-Up Summary Fields

Now, create a roll-up summary field for Number of Reviews on the Job Application object.

1. From Setup, click Object Manager, and select Job Application.
   
2. Click Fields & Relationships, then click New.
   
3. For Data Type, choose Roll-Up Summary, and click Next.
   
4. For Field Label, enter Number of Reviews.
   
5. Click Next.
   
6. Select Reviews from the Summarized Object dropdown.
   
7. Select Count as the Roll-Up Type.
   
8. Select All records should be included in the calculation as the Filter Criteria.
   
9. Click Next, Next, then Save & New.
   

Next, create a roll-up summary field for Review Scores on the Job Application object.

1. Select Roll-Up Summary as the Data Type, and click Next.
   
2. For Field Label, enter Total Review Score.
   
3. Click Next.
   
4. Select Reviews from the Summarized Object dropdown.
   
5. Select Sum as the Roll-Up Type.
   
6. Select Overall Review Score as Field to Aggregate.
   
7. Select All records should be included in the calculation as the Filter Criteria.
   
8. Click Next, Next, then Save & New.
   

Create Additional Formulas

Now create a formula field that calculates the Average Review Score for a job application.

1. Select Formula as the Data Type, and click Next.
   
2. For Field Label, enter Average Review Score.
   
3. Select Number as the Formula Return Type.
   
4. Click Next and add the formula under Average Review Score (Number) = 
   
   1. IF(Number_of_Reviews__c <> 0, Total_Review_Score__c / Number_of_Reviews__c, null)
5. Ensure your screen looks like this.
   
   
6. Click Check Syntax to verify there are no errors.
   
7. Click Next, Next, then Save & New.
   

Create a formula field to show a green, yellow, or red light on a job application record, depending on the Average Review Score.

1. Select Formula as the Data Type, and click Next.
   
2. For Field Label, enter Review Indicator.
   
3. Select Text as the Formula Return Type.
   
4. Click Next and add the formula under Review Indicator (Text) =
   

IF(Average_Review_Score__c >= 3.5,IMAGE("/img/samples/light_green.gif",
"Green"),IF(Average_Review_Score__c >= 2.5,IMAGE("/img/samples/light_yellow.gif",
"Yellow"),IF(Average_Review_Score__c > 0,IMAGE("/img/samples/light_red.gif", "Red"),
"No Reviews")))

5. Ensure your screen looks like this:
   
   
6. Click Check Syntax to verify there are no errors.
   
7. Click Next, Next, then Save.
   

Create a Job Application Record

• Click to open the App Launcher, then click Recruiting.
  
• Click the Job Applications tab.
  
• Click New to create a record.
  
• Enter values for all the fields.
  
• Click Save.
  

Now customize the Job Application record page and move the new fields to a Candidate Rating section.

1. Click , then select Edit Page. This is a shortcut to get into App Builder and clone the default record page for the Job Application object.
   
2. Click the Details tab, then select the Record Detail component inside the tab.
   
3. In the properties pane, click Upgrade Now to start the Dynamic Forms migration wizard.
   
4. Step through the wizard, select Job Application Layout, then click Finish.
   
5. Click Save, then click Activate | Assign as Org Default.
   
6. Select Desktop and phone on the Assign Form Factor page.
   
7. Click Next, then click Save.
   
8. Click the Fields tab, then drag a Field Section from the palette to below the Information section.
   
9. Fill in the section properties:
   

• Label: Candidate Rating.
  
• Columns: 1 Column.
  

10. Drag the Number of Reviews, Total Review Score, Average Review Score, and Review Indicator fields from the Information section into the Candidate Rating section.
    
11. Click Save.
    

By customizing pages and creating cross-object formulas, validation rules, roll-up summaries, and formulas, you’re giving the HR team more bang for their buck with their recruiting app. With consistent, complete data they can easily utilize and find at a glance, they can add new talent to AW Computing faster than ever.