Loading

Formula which references a formula on a blank 'relationship' field returns a value instead of null

Date de publication: Oct 13, 2022
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.

Example:

- 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"
Résolution
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.


See also:
Formula Operators and Functions
Build Cross-Object Formulas in the Advanced Formula Tab
Tips for Building Cross-Object Formulas
Numéro d’article de la base de connaissances

000381947

 
Chargement
Salesforce Help | Article