Skip to main content

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:Advanced Formula tab showing Overall Review Score formula.
  8. Click Check Syntax to verify that there are no errors.
  9. Click Next, Next, then Save.

Create Roll-Up Summary Fields

Now, create a rollup 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 picklist.
  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 picklist.
  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) = 
    IF(Number_of_Reviews__c <> 0, Total_Review_Score__c / Number_of_Reviews__c, null)
  5. Ensure your screen looks like this.
    Advance formula tab show Average Review Score formula.
  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:
    Advanced Formula tab showing Review Indicator formula.
  6. Click Check Syntax to verify there are no errors.
  7. Click Next, Next, then Save.

Now move the new fields to a Candidate Rating Section on the Job Application page layout.

  1. While still viewing the Job Application in Object Manager, click Page Layouts on the left navigation bar.
  2. Click Down arrow iconnext to Job Application Layout, and select Edit.
  3. Add a new section to the page layout, by dragging Section from the palette to below the information section.
  4. Fill in the section properties:
    • For Section Name, enter Candidate Rating.
    • For Layout, select 1-Column.
  5. Click OK.
  6. Drag the Number of Reviews, Total Review Score, Average Review Score, and Review Indicator fields from the Information section into the Candidate Rating section.
  7. Click Save.

By customizing page layouts 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.

Keep learning for
free!
Sign up for an account to continue.
What’s in it for you?
  • Get personalized recommendations for your career goals
  • Practice your skills with hands-on challenges and quizzes
  • Track and share your progress with employers
  • Connect to mentorship and career opportunities