(Advanced) - Formula Fields

At Dreamforce, we were able to attend a session on formulas that was truly amazing. There are lots of posts and discussions about formulas and a great user group so if you need help trouble shooting a formula visit the Help & Training section in your salesforce instance as well as http://www.developerforce.com.

Bringing in Information from the Contact record into another object.

In this example, we are bringing the email and phone numbers associated with the Opportunity Coordinator into the Volunteer Opportunity record.

Basic Formula Field

In Classic:  Go To Setup --> App Setup --> Create  --> Objects.
Scroll to and click on Volunteer Opportunities

Go to the  Custom Fields & Relationships section and select "New"

In Lightning: Go to Setup / Object Manager and select Volunteer Opportunities.  Go to Fields and Relationships and select New.

Step 1- Once in the Create New Field select the Type "Formula" and click next.

Step 2- Enter the field label.  I used "Opportunity Coordinator Contact Information"

             The Field Name field will automatically be populated based on the Field Label

             And for the Formula Return Type select "Text"

Then click "Next"

Step 3-  Create the formula.    Below is the basic formula that we used to bring in the email, Primary Phone? field, and all 4 of the Phone number fields.

"Email: "+HOC__Opportunity_Coordinator__r.Email+" "+BR()+

"Primary Phone: "+TEXT(HOC__Opportunity_Coordinator__r.HOC__Primary_Phone__c)+BR()+

"Home Phone: "+HOC__Opportunity_Coordinator__r.HomePhone+BR()+

"Business Phone: "+HOC__Opportunity_Coordinator__r.Phone+BR()+

"Mobile Phone: "+HOC__Opportunity_Coordinator__r.MobilePhone+BR()+

"Other Phone: "+HOC__Opportunity_Coordinator__r.OtherPhone

 

 

 

On Step 3 you can also provide the Description and Help Text for this field.

In this step you also designate how to handle blank values as either "0" or blanks.

Step 4-  In this step you can select the field level security for this new field by designating which profiles have access to this information.   We recommend that at minimum you grant access to the System Admin and Staff profiles, and you can also share with the Partner Staff and Volunteer Leaders so that they can confirm that their contact information is correct.  Remember that by default all formula fields are READ ONLY.

Once you've set the field level security, then click Next.

Step 5- In this step you can add the field to the Page layouts.  If you add them to any page layout, the field is just insert in the top section so you'll still need to manually go into each page layout and designate exactly where you want this field to appear.

Once you have selected the Page Layouts, if any, to add it to click "Save."

After clicking Save, you are taken back to the Volunteer Opportunity Object page.  From here you can go to the Page Layouts and update the placement of the field you just added or even if you didn't add it initially you can use the page layout to add the field now in the desired location.  For more information on page layouts click here.

Advanced version of the same formula

We can take the same formula shown above and make it smarter to not display rows that are missing values using this version:

IF(ISBLANK(HOC__Opportunity_Coordinator__r.Email),Null,"Email: "+HOC__Opportunity_Coordinator__r.Email)+" "+BR()+

IF(ISBLANK(TEXT(HOC__Opportunity_Coordinator__r.HOC__Primary_Phone__c)),Null,"Primary Phone: "+TEXT(HOC__Opportunity_Coordinator__r.HOC__Primary_Phone__c))+BR()+

IF(ISBLANK(HOC__Opportunity_Coordinator__r.HomePhone),Null,"Home Phone: "+HOC__Opportunity_Coordinator__r.HomePhone)+BR()+

IF(ISBLANK(HOC__Opportunity_Coordinator__r.Phone),Null,"Business Phone: "+HOC__Opportunity_Coordinator__r.Phone)+BR()+

IF(ISBLANK(HOC__Opportunity_Coordinator__r.MobilePhone),Null,"Mobile Phone: "+HOC__Opportunity_Coordinator__r.MobilePhone)+BR()+

IF(ISBLANK(HOC__Opportunity_Coordinator__r.OtherPhone),Null,"Other Phone: "+HOC__Opportunity_Coordinator__r.OtherPhone)

Adding Traffic Light Image to represent which projects need the most volunteers

In the Occurrence record, you can designate a Minimum and Maximum attendance.  The Minimum attendance doesn't have much functionality in the system, but can if you implement this Image Formula, it can be used to help distinguish between occurrences that haven't reached their minimums and those that have, but still have space available versus those that are full.

We will be using the same initial steps as above for the Opportunity Coordinator Contact Info, except that this time we are using the Occurrence Object instead of the Volunteer Opportunities.

Go To Setup --> App Setup --> Create  --> Objects.

Scroll to and click on Occurrences

Go to the  Custom Fields & Relationships section and select "New"

Step 1- Once in the Create New Field select the Type "Formula" and click next.

Step 2- Enter the field label.  I used "Still Need Volunteers?"

             The Field Name field will automatically be populated based on the Field Label

             And for the Formula Return Type select "Text"   ( I know we are using an Image, but you still select TEXT)

Then click "Next"

Step 3-  Create the formula.    In this example we are using an IF Statement to determine the image to return.    We are also using some of the sample images that Salesforce provides in the formula.  (The last page of this Salesforce guide has a listing of all the URLs for the sample images provided by Salesforce.

The standard format for an IF statement is as follows:

IF(logical test, result if test is TRUE, result if test is FALSE)

EX:  IF(HOC__Total_Confirmed__c<HOC__Minimum_Attendance__c, "Needs volunteers", "Has Minimum # of Volunteers needed")

In this example we are saying to check that the Total Confirmed in the occurrence to see if it is LESS THAN the Minimum Attendance for that occurrence.  If Total Confirmed is less then enter the text: Needs Volunteers in the field or if Total Confirmed is GREATER THAN OR EQUAL TO the Minimum Attendance then enter the text: Has Minimum # of Volunteers needed.

If statements can also be coupled, for example we instead of returning the "Has Minumum # of Volunteers needed" text when the logical test fails, we can tell it to perform another logic test.

IF(HOC__Total_Confirmed__c<HOC__Minimum_Attendance__c, "Needs volunteers", IF(HOC__Total_Confirmed__c<HOC__Maximum_Attendance__c, "Has Minimum # of Volunteers needed", "Project Full"))

In this example we are saying to check that the Total Confirmed in the occurrence to see if it is LESS THAN the Minimum Attendance for that occurrence.  If Total Confirmed is less then enter the text: Needs Volunteers in the field or if Total Confirmed is GREATER THAN OR EQUAL TO the Minimum Attendance then perform another test to see if the Total Confirmed is less than the Maximum Attendance.  If the Total Confirmed is less than the Maximum Attendance then enter the text: Has Minimum # of Volunteers needed, if the Total Confirmed is GREATER THAN OR EQUAL TO the maximum attendance then enter the text: Project Full.

Using this second example, we will replace the display text with a traffic light image where:

Green= has not met minimum attendance

Yellow= has met minimum, but not maximum attendance

Red= has met maximum attendance

So here's the formula we are using.  (This formula was created using the Advanced Formula tab in step 3)

IF(HOC__Total_Confirmed__c<HOC__Minimum_Attendance__c, IMAGE("/img/samples/light_green.gif","Green"),

IF(HOC__Total_Confirmed__c>=HOC__Maximum_Attendance__c,IMAGE( "/img/samples/light_red.gif", "Red"),IMAGE( "/img/samples/light_yellow.gif", "Yellow")))

NOTE:  when using an image the formula code is IMAGE("location of the image","Name of image")   The name of the image is what can be used for creating reports, filters, and views.

 

Now that you've created the field you can continue to complete Step 3 to enter the Description and Help Text.

Step 4- Grant the field level access

Step 5- Add to page layout.  This traffic light field isn't as helpful in the page layout so our recommendation is not to add it.  

Now that the field has been added, you can go to the Occurrence object and create a new view to display which upcoming occurrences still need more volunteers.  For information on creating views, see this documentation.

In this view we are filtering for occurrences that are associated with Schedule Type EQUALS Date & Time Specific and have an End Date & Time GREATER THAN TODAY.

 

Using Google Charts Wizard to create a Formula Field

This next section is not for the faint of heart and will earn you advanced geek status.

Google has a Chart Wizard that can be used to create some fun charts that can be embedded into your records.

The 30,000 view of what we are about to do is this:

1) Go to Google Charts and create a chart with some false data

2) Copy the code for the chart

3) Create a formula field and copy the chart code into the formula

4) Replace the false data values with links to the field in the record that has the value we want to use for the chart

NOTE:  Google Charts: Special Notes / Limitations  (As provided in the Dreamforce '11- Formula Ninjas Powerpoint; Sept 1, 2011)

  • HTTP / HTTPS Mixed Content
  • Specify Height & Width on Image Function
  • Google Chart Wizard is NOT SSL Encrypted
  • See Chatter Post for Visual Force Page Usage

Google Chart Wizard

Go to the Google Chart Wizard page.

For this example we will be using the gauge chart, but you can see that this can be applied to bar charts, pie graphs, etc.   To get to the gauge chart click on "Show Full Gallery" and then select "Google-o-Meter" Charts.

Once you select the chart, you are taken to the Editor tab to customize. As you update the fields, the example and chart code are updated.

Here we will set the following parameters:

Y-Axis:  Range:  0-100

Labels: Poor

              Fair

              Great

Title: (Can Skip)

Size:  Change to Width: 200; Height:110.  Can leave as default if desired.

Data: In here we enter some false data to get the necessary code.  (See screenshot below for explanation)

Data Style:  (Can Skip)

Fills: (Can Skip)

Margins:  This is optional.  But we can set the margin for the top as 5 to give it some built in buffer for views.

Additional Style:  (Can Skip)

 

Once you've made all the changes we can then copy the code provided by Google Charts

Breakdown of the chart code:

http://chart.apis.google.com/chart   (link to the chart)

  ?chxl=0:|Poor|Fair|Great                 (provides the labels for the chart)

  &chxt=y                                               (designates the use of the Y-axis)

  &chs=300x150                                 (provides the dimensions for the chart-  when we enter it into the formula code we will need to reverse the order)

  &cht=gm                                            (Represents type of Chart.  gm=google-o-meter)

  &chd=t:70                                          (Represents first Data Value)

  &chl=70%                                          (Represents data label on the chart)

  &chma=0,0,5                                    (designates the margins (Left, Right, Top, Bottom  (missing since it is 0)

Now that we have the code we can create the formula field.

Creating the Chart Formula Field- Attendance Rate

For this example, I had to create a new field to get the attendance rate percentage.  I created a formula field with the type= Number; Decimals=0 and the formula (HOC__Total_Attended__c/HOC__Total_Confirmed__c)*100  I was then able to use this new field to convert the percentage to be shown on the gauge as outlined below:

Just like with the two previous examples, I created a new field using these steps:

Go To Setup --> App Setup --> Create  --> Objects.

Scroll to and click on Occurrences

Go to the  Custom Fields & Relationships section and select "New"

Step 1- Once in the Create New Field select the Type "Formula" and click next.

Step 2- Enter the field label.  I used "Attendance Rate"

             The Field Name field will automatically be populated based on the Field Label

             And for the Formula Return Type select "Text"   ( I know we are using an Image, but you still select TEXT)

Then click "Next"

Step 3-  Create the formula.  

I then copied the Google Chart Code shown above:

http://chart.apis.google.com/chart

  ?chxl=0:|Poor|Fair|Great

  &chxt=y

  &chs=300x150

  &cht=gm

  &chd=t:70

  &chl=70%

  &chma=0,0,5

I then modified the code to reflect the fields in the occurrence record that I wanted to use in the place of the data.

http://chart.apis.google.com/chart

  ?chxl=0:|Poor|Fair|Great

  &chxt=y

  &chs=300x150

  &cht=gm

  &chd=t:"+TEXT(Attendance_Rate_Percentage__c) + "                                        (This tells the code to pull the value from the Attendance Rate Percentage field and insert it here.  Don't forget the " and +)

  &chl="+TEXT(ROUND(Attendance_Rate_Percentage__c,0))+"%                     (This tells the code to pull the value from the Attendance Rate Percentage field and insert it before the % to create the label.  Notice we had to tell the code to round            

                                                                                                                                             the value to 0 decimals since the actual value in the Attendance rate percentage field is the result of a calculation.  Don't forget that the % goes after the ")

  &chma=0,0,5

Now we modify the standard Image formula  IMAGE("Source of the Image","Alternative Text", Height, Width)  Remember: the Height and Width are the opposite of how they are displayed in the Google Chart code.

when we plug in our Google Chart Code the formula looks like this:

IMAGE("http://chart.apis.google.com/chart?chxl=0:|Poor|Fair|Great&chxt=y&chs=200x110&cht=gm&chd=t:"+TEXT(Attendance_Rate_Percentage__c) + "&chl="+TEXT(ROUND(Attendance_Rate_Percentage__c,0)) +"%&chma=0,0,5","No Image Available" ,110,200)

NOTE: You don't want carriage returns in the middle of the image source location otherwise it breaks the code.

 

 

Now that we have the formula code entered, we can finish creating the field.

Complete Step 3 to enter the Description and Help Text.

Step 4- Grant the field level access

Step 5- Add to page layout.  This traffic light field isn't as helpful in the page layout so our recommendation is not to add it.  

Now that the field has been added, you can go to the Occurrence object and create a new view to display which the attendance rates for past projects.  For information on creating views, see this documentation.

In this view we are filtering for occurrences that are associated with Schedule Type EQUALS Date & Time Specific and have an End Date & Time LESS THAN TODAY and Total Confirmed is GREATER THAN 0.  The need for the Total Confirmed filter is that those occurrences have an error because you can't divide by 0).

If you would like to see the video of the Dreamforce '11 Formula Ninjas class you can view it on YouTube.

0 Comments

Add your comment

E-Mail me when someone replies to this comment