Select Page

## Create a validation rule indicating whether an opportunity has a closed date in the past

Your number-crushing sales team has so many deals in the pipeline, you’re starting to see occasional problems with data quality. Namely, sales reps are forgetting to update the close date to a date in the future. Make a formula that requires an opportunity to have a CloseDate of today or any future date. Hint: this formula should reference the hidden IsClosed checkbox field on the Opportunity object, and you will be creating the formula as an Opportunity validation rule.
• The validation rule should be on the Opportunity object
• The validation rule should be named ‘Close_Date_Alert’
• The validation rule should fire if IsClosed is not selected and CloseDate is yesterday or earlier
• The validation rule should display the error ‘Hey McFly, unless you are planning to go back in time, please update your close date’ at the top of the page when triggered

## Create a Number formula that calculates the volume of a cylinder

Your company sells cylindrical hyperbaric chambers. You need a formula field that calculates the volume of a cylinder for you, rounded to the nearest whole number, given its radius and height. Use the existing mathematical formula for the volume of a cylinder, V = πr2h, where r is the radius of the cylinder, h is the height, and π is the constant Pi. Note: Although this formula field might best be created on a custom object, for simplicity, we’ll create this formula on the Opportunity object.
• Create 2 custom fields of type Number on the Opportunity object: ‘radius’ with a resulting API name of ‘radius__c’ and ‘height’ with a resulting API name of ‘height__c’.
• The formula should be named ‘Cylinder Volume’, with the resulting API name ‘Cylinder_Volume__c‘ and should be created on the Opportunity object.
• The formula should reference the custom fields ‘radius__c‘ and ‘height__c‘.
• The formula should use 3.14159 as an approximation of Pi.
• The formula should have 0 decimal points.

## Create a validation rule for escalated cases

Create a validation rule formula that does not allow a user to mark a case as escalated unless the priority is set to ‘High’, the case was not Closed when Created, and the case isn’t closed.
• The validation rule should be on the Case object.
• The validation rule should be named ‘Mark_as_Escalated’.
• The validation rule should fire if someone tries to set a case as escalated and it is closed, closed when created, or does not have a priority of High.
• The validation rule should display the error message ‘You can only set a case as escalated if it is high priority and not closed’ under the Escalated field when triggered.
• Add the ‘Escalated’ field to the Case page layout.

## Create a formula field that returns an image to indicate data quality

Sales Managers have asked for an at-a-glance solution to see completeness on leads. Create a helper formula field that looks at 5 key fields on the Lead object and evaluates their completeness, then a second formula field that references the helper formula and returns an image.
• The helper formula field should be on the Lead object with a name of ‘Lead Quality Helper’ and a resulting API name of ‘Lead_Quality_Helper__c’.
• The helper formula should be of type Number.
• The helper formula should evaluate the following 5 fields: Email, Phone, Company, Title, and Industry and return 0 if blank and 1 if not blank. The formula should then add all the values together to return a total value.
• The image formula should be on the Lead object with a name of ‘Lead Quality’ and a resulting API name of ‘Lead_Quality__c’.
• The image formula should reference the helper formula, and return an image based on the number returned by the helper formula. The image formula should be of type Text. Note: All of these images are already available in your Developer Edition.
• 1 = /img/samples/stars_100.gif with alternate text ‘1 star’
• 2 = /img/samples/stars_200.gif with alternate text ‘2 stars’
• 3 = /img/samples/stars_300.gif with alternate text ‘3 stars’
• 4 = /img/samples/stars_400.gif with alternate text ‘4 stars’
• 5 = /img/samples/stars_500.gif with alternate text ‘5 stars’
• If none of the fields are filled out, the default should be /img/samples/stars_000.gif with alternate text ‘0 stars’.

## Create a formula that shows where an Opportunity is in the pipeline

Create a formula field that classifies an Opportunity as either “Early”, “Middle”, or “Late”. This formula field should use TODAY() to calculate what percentage of the time between an opportunity’s CreatedDate and CloseDate has passed, and label the opportunity accordingly.
• This formula should be on the Opportunity object
• This formula should be named ‘Opportunity Progress’ with the resulting API name Opportunity_Progress__c
• This formula should return ‘Early’ if less than or equal to 25% of an opportunity has passed
• This formula should return ‘Middle’ if between 25% and 75% of an opportunity has passed
• This formula should return ‘Late’ if more than 75% of an opportunity has passed
• This formula should reference a helper formula field, also on the Opportunity Object, with the type Percent and the name Percent Completed
• Percent Completed should return the percentage of the time that has passed between an opportunity’s CreatedDate and CloseDate

## Troubleshoot a formula and fix a couple of errors

The following formula, meant to return the last day of the current month, has a couple of errors in it:

IF( MONTH( NOW() ) = 12,
DATE( YEAR( NOW() ), 12, 31 ),
DATE( YEAR( NOW() ), MONTH( NOW() ) + 1, 1) – 1

Create a new formula with the same label, name, and data type that successfully compiles.
• The formula should be of Date type and on the Case object
• The formula should have the name Last Day of Month and the resulting API name Last_Day_of_Month__c
• The formula should return the last day of the current month