[SalesForce] Nested IF statements in formula field

I'd like to have a field which contains the value of the final system size if it exists, the post system size if that exists, or the value of the regular system size otherwise. The formula I have is here: IF(NOT(ISNULL(Final_System_Size__c)),
VALUE(Final_System_Size__c),
IF(NOT(ISNULL(Post_System_Size__c)),
Post_System_Size__c,
System_Size__c))

This only grabs the final system size, even when its empty and system size and post system size have values. What's causing the other two values to be ignored?

Best Answer

Since field Final_System_Size__c is text and formula return type is number. Empty fields are treated as blanks or zeroes. Meaning that NOT(ISNULL(Final_System_Size__c)) always will return false, thats why you see Final_System_Size__c.

If you select option Treat blank fields as blanks to true, ISBLANK(Final_System_Size__c) will return true/false, based on its parameter.

try to modify to:

IF(NOT(ISBLANK(Final_System_Size__c)),
VALUE(Final_System_Size__c),
IF(NOT(ISBLANK(Post_System_Size__c)),
Post_System_Size__c,
System_Size__c))
Related Topic