[SalesForce] Formula to get parent field value to update field on child record in self look up relationship

In my org I have created enter image description herea text field 'Active contract Number' and a formula field 'Parent Active Contract' on Account object. Account object has self lookup. The requirement is to show the 'Parent Active Contract Number' of Ultimate Parent on all it's child (till 7 level deep).

Below is the formula that is used to accomplish this:

IF( LEN( Parent.Active_Contract_Number__c) < 1, Active_Contract_Number__c,
IF( LEN( Parent.Parent.Active_Contract_Number__c ) < 1, Parent.Active_Contract_Number__c,
IF( LEN( Parent.Parent.Parent.Active_Contract_Number__c) < 1, Parent.Parent.Active_Contract_Number__c,
IF( LEN( Parent.Parent.Parent.Parent.Active_Contract_Number__c) < 1, Parent.Parent.Parent.Active_Contract_Number__c,
IF( LEN( Parent.Parent.Parent.Parent.Parent.Active_Contract_Number__c) < 1, Parent.Parent.Parent.Parent.Active_Contract_Number__c,
IF( LEN( Parent.Parent.Parent.Parent.Parent.Parent.Active_Contract_Number__c) < 1, Parent.Parent.Parent.Parent.Parent.Active_Contract_Number__c,
IF( LEN( Parent.Parent.Parent.Parent.Parent.Parent.Parent.Active_Contract_Number__c) < 1, Parent.Parent.Parent.Parent.Parent.Parent.Active_Contract_Number__c,
" "
)
)
)
)
)
)
)
Here, if 'Ultimate parent1' has value in Active contract number field then the same will reflect in Parent Active contract in Ultimate Parent 1 and its related child, however this formula is only working when we put value in Active contract Number in all parents, means we have to put value in all level to get formula field value till Child 4.

Help me with this, if i have Active contract number only on Ultimate parent but not in other (here in child 1 or 2 or 3) then the value of ultimate parent of Active contrcat number should reflect in Child 4 Parent Active contract number field.

Appriciate your help!

Best Answer

You can try apply the following logic in your formula. The example goes 3 levels deep but can be extended to 7 as in your example.

IF(len(Parent.Active_Contract_Number__c) >= 1, Parent.Active_Contract_Number__c,
if(len(Parent.Parent.Active_Contract_Number__c) >= 1,Parent.Parent.Active_Contract_Number__c,''))

Break down into steps:

  1. Parent has Active Contract Number
  2. Yes -> Take that value
  3. No -> Try for the parent of my parent
  4. Repeat 1 for parent of my parent.
Related Topic