2

I'm using a workflow rule to populate a field with the sum of some fields. All of the fields are in the currency type.

Field__c + Field1__c + Field2__c

It works perfectly if the values are populated. If the fields are blank, which is their default value, this formula does not work and it returns a blank value. How can I check for null properly in the formula field?

For example, if the field values are $5.00, $4.00, and null I expect the sum to be $9.00.

I don't want to set a default value for these fields of $0.00.

Improve this question
4
  • Are you saying that when all fields are null you want the sum to be 0, or are you saying that if any one field is null, you are expecting a sum but are getting blank?
    – dsharrison
    Commented Oct 3, 2016 at 16:31
  • @D.S. That if I have field values of 5.00, 4.00 a null, I expect 9.00.
    – Jacob Raccuia
    Commented Oct 3, 2016 at 16:32
  • formula in a field update?
    – cropredy
    Commented Oct 3, 2016 at 16:33
  • @cropredy There is a checkbox that allows you to use a formula to set a new value.
    – Jacob Raccuia
    Commented Oct 3, 2016 at 16:34

2 Answers 2

Reset to default
6

Your friend is the BLANKVALUE function that if the first argument is null, will use the second argument as the value of the expression 

BLANKVALUE(Field__c,0) + BLANKVALUE(Field1__c,0) + BLANKVALUE(Field2__c,0)
Improve this answer
1

While this is not ideal, you can wrap each of your field references is an IF statement to ensure it is not null. This would look something like:

IF(ISBLANK(Field1__c), 0, Field1__c) + IF(ISBLANK(Field1__c), 0, Field1__c) + ...

This is tedious but unfortunately there is no "Treat blank fields as 0" during workflow field updates (that I am aware of).

Improve this answer
1
  • 1
    This definitely works, but BLANKVALUE as posted above is slightly more concise. :)
    – Jacob Raccuia
    Commented Oct 3, 2016 at 16:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .