I use this formula field in my apex code. But I have big troubles with it, because there should be different logic for values 0 and NULL.

enter image description here If I choose Treat blank fields as blanks, I'll get NULL even if one of the Integer filds (First__c or Second__c) is NOT NULL. If I choose Treat blank fields as zeroes, my formula field will be 0 too. And this formula is incorrect, because in both variants I get NULL. And I don't know why.

IF(First__c != NULL && Second__c != NULL, First__c + Second__c ,
IF(First__c = NULL && Second__c  != NULL, Second__c ,
IF(First__c != NULL && Second__c = NULL, First__c , NULL)))

And if one of fields in NULL and another one is 0, I want to get 0 in my formula field.

I don't want to change the formula type, because I have a lot of logic with it already. Can I do something with this?

  • 5
    did you try to use IsBlank function? – m Peixoto Nov 26 '19 at 16:28
  • 2
    The ISBLANK function is the official way to test "!= NULL" in a formula. – Phil W Nov 26 '19 at 16:31

Here is the working formula. this works in both the cases: Treat blank fields as zeroes and Treat blank fields as blanks

    !ISBLANK(First__c) && !ISBLANK(Second__c),
    First__c + Second__c,
        !ISBLANK(First__c), First__c,
        IF(!ISBLANK(Second__c), Second__c, 0)

If any of the values is null formula returns the nonnull value.

If both of the values are null your formula will result in 0. If you want it to be null in this case replace 0 in formula with null

  • 1
    Thank you for your help – Apelsinova Nov 26 '19 at 19:43

I did some testing in my org with fields called Number1__c and Number2__c.

Set the field to Treat blank fields as blank fields

use the formula:

IF(AND(NOT(ISBLANK(Number1__c)), NOT(ISBLANK(gnsf__Number2__c))), gnsf__Number1__c + Number2__c,
IF(AND(ISBLANK(Number1__c), NOT(ISBLANK(Number2__c))), Number2__c,
IF(AND(NOT(ISBLANK(Number1__c)), ISBLANK(Number2__c)), Number2__c, NULL)))

More Info:

At first I thought that your formula would work which is why I had to try for myself. I also did some simpler version to determine what SFs default behavior was.

I tried IF(Number2__c = null, 0, Number2__c) just to see what would happen. Even if the value was null, the second parameter was always returned (0 was never displayed unless Number2__c was set to 0).

Then I tried IF(ISNULL(Number2__c), 0, Number2__c) this worked. This means that SFDC knows the value is null, but doesn't support number = null.

IsBlank is more powerful than IsNull as it doesn't just look for a null value. It also supports strings which IsNull does not. Because of this it is best practice to use IsBlank

I've gotten into the habit of using the built in functions in formulas as opposed to the programmer syntax. For instance use AND(BoolExpr1, Boolexpr2) instead of && and NOT(BoolExpr) instead of ! as well as the above ISBLANK() instead of = null as these seem to be much more reliable.

  • Thank you for your help – Apelsinova Nov 26 '19 at 19:43

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.