A Formula which references a formula on a related object still returns a value when the relationship field is blank.
|Knowledge Article Number||000239980|
|Description||A cross-object formula which refers to a formula field on another object may still return a value when the relationship field which connects those objects is blank.
- There are 2 Objects ("object A" and "object B")
- Object A has a text field (text_field__c), and a formula field (formula_field__c). The Formula Field concatenates text_field__c with a text string (syntax like: text_field__c & "@test.com")
- Object B has a Lookup relationship to Object A, and a formula field to pull the value of formula_field__c on Object A (syntax like: Object_A__r.formula_field__c)
Result: On an "Object B" record where the lookup to "Object A" is blank, the formula on Object B displays "@test.com"
|Resolution||This is an expected result.
To explain, when a formula refers to a formula field it pulls in the syntax (not the value) of that field.
Continuing the above example, this means although the formula field on "Object B" has the syntax "Object_A__r.formula_field__c", the underlying logic of that formula field on "Object B" is actually 'Object_A__r.text_field__c & "@test.com"'
When the lookup to Object A is blank, this formula cannot fetch "Object_A__r.text_field__c", so that part of the formula is null. The formula logic evaluates as 'null & "@test.com"', and outputs "@test.com"
To prevent this sort of behavior, update the formula logic on "Object B" with an IF function to return null when the lookup to Object A is null.