Fun with Formulas

I know that formulas may not seem like a “process automation” but I think it meets the criteria because it’s automating an additional step for a user while insuring data quality.  Any time we can automate something for our users we’re helping to minimize user error or forgetfulness and saving them time and effort.  For the savvy admin formulas are a lifesaver.

#1 What should an admin consider before creating a formula?

  • Keep in mind that formula fields are read only, only the formula can define the value in the field.
  • Does the formula calculate differently depending on the scenario?
  • Test the formula in a sandbox environment before adding it to the page layouts in production.
  • If a formula is complex, it helps me to write it out on paper before building it in the formula editor.

#2 Steps to create a formula:

  1. Define the formula parameters before building the actual field in Salesforce.  
  2. Name the formula field and select the formula return type.formula1
  3. Build the formula.  Here is a list of formula operators and functions with explanations.  I prefer to build formulas in the Advanced Formula builder.formula2
  4. Confirm the formula syntax is accurate.formula3
  5. Define how blank values will be handled.formula4
  6. Set field level security and add the field to the necessary layouts, just like with any custom Salesforce field.

Now, for some of my favorite formulas!

#3 Example -The Power of One

There are many blogs and community answers that reference this concept created by Thomas Tobin.  Basically you’ll create a formula field on an object, the formula will equal 1, it’s that simple.   


So, why is this such a big deal?  Well now you can do math on things like the average number of activities on Closed Won opportunities.  By adding this formula to Activities and Opportunities we can create a formula in a report that will calculate and visualize the average.


#4 Example – Image formulas

It’s always nice to allow your users to have a visual on their records, in list views or reports.  Formulas that display images based on a set of criteria are a great way to impress your users and your manager with your admin super powers.  Here’s an example, I’ve built an event management application for my event planning team, I’d like to display a warning on the Event Milestone Tasks when it is overdue. 


The IMAGE function is comprised of 3 parts; the image location, the alternate text and the size.

**For bonus points, a rollup summary field can be used to allow the event management hierarchy to roll up so that the warning image would display on the Event parent record record anytime one of the child records in the event plan was also overdue**

#5 Example – User ID formula

Most of the time the standard Record Owner field on every record in Salesforce all we need to create views and reports on the records that a user owns (ex–My Cases).  We can do this by selecting My in views and reports.

My Report  My View

But we can’t use the Salesforce standard ownership field to reflect when a user is related to a record via another lookup field.  For example, in our Event Management app the Event Owner/Record Owner might assign Event Milestones to other users.  The assignees would want to see only the work that has been assigned to them.  We can create a hidden checkbox formula field that will check to see if the user viewing the record is the user in the Assigned To field.  

My formula

Now, as an admin I can create a single “My Assigned Records” view or report for my users to use, instead of having to hardcode their name in separate views.

My formula view

A system admin should look to formulas to reduce keystrokes for users, get rid of manually calculated math (all of it), and to enhance data quality. Making things easy and accurate every time increases user adoption and makes everyone much much happier.

Come back next week for Dependencies and Validation rules!

Check out the Saasy5 Pinterest board for more process automation resources.

Visit Saasy5 on Pinterest, each blog will have a board with pins to the resources used to write the blog.  Here’s this week’s board.  Since the Salesforce world is HUGE let me know if I missed something that should have been pinned and I’d be happy to pin it.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s