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.

  • 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 Oct 3 '16 at 16:31
  • @D.S. That if I have field values of 5.00, 4.00 a null, I expect 9.00. – Jacob Raccuia Oct 3 '16 at 16:32
  • formula in a field update? – cropredy Oct 3 '16 at 16:33
  • @cropredy There is a checkbox that allows you to use a formula to set a new value. – Jacob Raccuia Oct 3 '16 at 16:34

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)

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).

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.