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:
- 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:
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):
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:
- Current Balance (Roll-up field summing the customer’s invoice balances)
- Credit Limit (Currency Field)
- 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:
To correct this, I change the Blank Field Handling setting to “Treat blank fields as blanks”:
And my Credit Utilization is now also 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”:
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
[/twocol_one] [twocol_one_last] 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.
