Find the common parent of two list members

thomas_2

I have an Entity list which contains the entities in my organisation. If, as the result of another calculation, I have been returned two entities in the Entity list, how do I determine their lowest common parent? Some combination of PARENT and FINDITEM seems likely... but they might be at different levels.

ravi

Assuming you have 4 levels in the hierarchy and the Entity list is Level 4 in the Hierarchy you can show the common parent name in a Text Formatted line item using the formula below.

 

IF PARENT(Entity 1)=PARENT(Entity 2) THEN "L3 - " & NAME(PARENT(Entity 1))
ELSE IF PARENT(PARENT(Entity 1))=PARENT(PARENT(Entity 2)) THEN "L2 - " & NAME(PARENT(PARENT(Entity 1)))
ELSE IF PARENT(PARENT(PARENT(Entity 1)))=PARENT(PARENT(PARENT(Entity 2))) THEN "L1 - " & NAME(PARENT(PARENT(PARENT(Entity 1))))
ELSE "No Common Parents"

The code above Prefixes the Level of the Hierarchy which was found to be common starting from the bottom ending at the top.