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.

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
    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. Oct 3, 2016 at 16:32
  • formula in a field update?
    – cropredy
    Oct 3, 2016 at 16:33
  • @cropredy There is a checkbox that allows you to use a formula to set a new value. Oct 3, 2016 at 16:34

2 Answers 2

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

1
  • 1
    This definitely works, but BLANKVALUE as posted above is slightly more concise. :) Oct 3, 2016 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.