0

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!

Improve this question

1 Answer 1

Reset to default
0

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.
Improve this answer
1
  • Thanks Lieven, it worked well by adding 'And' condition in the formula you suggested. In this child will capture only ultimate parent active contract number regardless to it's immediate parent Active contract value.
    – Aarti
    Commented Dec 8, 2016 at 10:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .