In my org I have created a 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!