Blank Field Handling in Salesforce Formulas

I recently encountered a question in the Salesforce Success Community that asked why two formula fields with the same formula were returning different results.

I advised that the two formulas most likely have different settings for how blank fields are treated.  I too had struggled with a formula that I knew was right, but the results showed otherwise.  Basically, I randomly tested variations before finally realizing that my Blank Field Handling settings were incorrect.  So in hopes of saving others the frustration and time, here is a rundown on what Blank Field Handling is and how to use it.

Blank Field Handling can be found at the bottom of the formula editor and has two settings:

Blank Field Handling
  • Treat blank fields as zeroes: when your formula evaluates an empty field, it substitutes the blank with a zero and the calculation is completed.  This option is most often used in mathematical formulas.
  • Treat blank fields as blanks: when your formula evaluates a blank field, it does not substitute with a zero.  This option is most often used when formula results are based on logic (i.e. if/then statements).

Let’s create a simple mathematical formula as an example.

Let’s say we have two currency fields, “Income” and “Expense”, and one formula field “Profit”, which is Income – Expense.

When the formula’s Blank Field Handling setting is “Treat blank fields as zeroes”, there will always be a result:

Treat blank fields as zeroes

When the formula’s Blank Field Handling is set to “Treat blank fields as blanks”, profit will only have a value when both Income and Expense have a value (either of which could be a manually entered 0):

Treat blank fields as blanks

By treating the blank fields as zero, I always have a profit value to use in reporting.

However, there are times when mathematical formulas should not treat blanks as zero. Let’s create an accounting solution for measuring the Customer’s current balance against the credit limit we’ve extended to them.  I have three fields:

  1. Current Balance (Roll-up field summing the customer’s invoice balances)
  2. Credit Limit (Currency Field)
  3. Credit Utilization (Formula field, Currency Results) = Current Balance/Credit Limit

New customers do not receive a credit limit, so I leave that field blank until they have used my services enough that I am willing to work on credit. Because the formula builder defaults the Blank Field Handling to “Treat blank fields as zeroes”, I receive a division by zero error until I add a Credit Limit value:

Division by Zero Error

To correct this, I change the Blank Field Handling setting to “Treat blank fields as blanks”:

Credit Utilization

And my Credit Utilization is now also blank.

Credit Utilization blank

I mentioned that “Treat blanks as blanks” would be used in if/then statements.  As an example, here’s a formula for a field “Credit Status”:

Credit Status

If the customer does not have credit, I do not want to display a credit status.  I have to change the Blank Field Handling to “Treat blanks as blanks” in order for my if statement to be evaluated as true:

[twocol_one]Blanks as zeroes

Blanks as Zeros

[/twocol_one] [twocol_one_last] Blanks as blanks

Blanks as Blanks

[/twocol_one_last]If you aren’t sure which setting is right, try saying the formula out loud, substituting “zero” or “blank” for the field.  The best tip for troubleshooting your formulas is to check the easy things first.  Check your Blank Field Handling settings.

February 9, 2015