Tip #429: Calculating with empty fields

Calculated fields are all the rage, it seems. Today’s tip is from the Lego land, and it does look that CRM people over there do have very long daylight hours to come up with awesome tips. Plus their names look cool. See for yourself, passing the baton to Stig “Not that  Stig” Højmark Jensen.

Hi Tippers

Since I have found many helpful tips from following you on Twitter, I thought it was time to share this little thing that I found out yesterday when trying to meet a requirement using calculated fields.

In this case, the users have three currency fields on the opportunity for recording expected revenue in three different areas.

  • Est. Revenue Furniture
  • Est. Revenue Copy/Print
  • Est. Revenue Supplies

None of the fields is required.

We need the calculated field called Total Est. Revenue, which is pretty easy to make. But if you just add the three fields together, it will only show a result if all three fields have a value. If just one of them is blank, the result will show up as empty (‐‐).

Solution is to create three calculated fields:

Est. Revenue Furniture value
    If Est. Revenue Furniture contains data, result is Est. Revenue Furniture otherwise result is 0
Set Estimated Revenue Non-Blank

Est. Revenue Copy/Print Value and Est. Revenue Supplies value are done in the same way.

Now I can create my Total Est. Revenue by simply adding Est. Revenue Furniture value + Est. Revenue Copy/Print Value + Est. Revenue Supplies value

Result for non-blank calculations

Thanks for a great website and lots of great tips. (Thank you! – t.j.)
Med venlig hilsen / Best regards

2 thoughts on “Tip #429: Calculating with empty fields

  1. Lars Martin says:

    why do you create calculated fields for “Est. Revenue Furniture, Est. Revenue Copy/Print and Est. Revenue Supplies”?

    I would create a business rule which sets the default value for the mentioned fields to “0” if they are empty.

    Benefit: I only need one calculated field to sum up the values of the three fields.

    kind regards

    • Matthew Pope says:

      Hi Lars,

      I agree, you want to use as little fields as possible to avoid confusion with advanced finds and reports. If I am honest I just use multiple if functions to cover all eventualities.

      Although this takes longer to build it is easier to manage and should work better for advanced finds and reports.


Leave a Reply

Your email address will not be published. Required fields are marked *