Mark, your solution works great. However.....I can only have ONE (Product-to-Product) relationship (not three).....
I able to identify the lowest and the highest Hierarchy product levels....within the same Product Group. In the example I provided....I'm able identify....Hierarchy level for Product A = 4 and Hierarchy level for Product D = 1.....trying to figure out how to correctly identify [Hierarchy Levels] for Products that fall between the highest (4) and lowest (1)
Hierarchy Level]=4-Product A (Parent to B)
Hierarchy Level]=?----Product B (Parent to C)
Hierarchy Level]=?--------Product C (Parent to D)
Hierarchy Level]=1----------Product D
App has two tables (Product Groups & Products)
1st relationship -- Product Groups (parent) to Products (child) lookupfield=[Related Product Group]
2nd relationship -- Products to Products --- lookupfield=[RelatedParentProduct]
Key Fields
a) [#ofproducts] = 4 - created a roll up summary field (1st relationship) on the Product Groups table (gives me total number of products in Product Group aka "hierarchy grouping"....I then "pulled" this field back down into my Products table.
b) [#ofchildproducts] **this is a rollup summary field (2nd relationship) from my Products-to-Product relationship...[.
Formula to identify Highest and Lowest levels
If([#ofchildproducts]=0, 1,
[#ofchildproducts]<0 and Length([RelatedParentProduct])<1, [#ofproducts])
-----------------
Formula Explained:
If([#ofchildproducts]=0, 1 / (If no related child records then it is not a parent....so product is lowest hierarchy level = 1)
[#ofchildproducts]<0 and Length([RelatedParentProduct])<1, [#ofproducts]) / (If there are related child records then this record is a parent....and if the [RelatedParentProduct] field is empty....then this record is not a Child.....so this is the Highest Hierarchy Level....so it = [#ofproducts] -- = 4
------------
The challenge is assigning [Hierarchy Levels] to products that fall between the Highest & Lowest levels.....???????
Any suggestions or advice is appreciated....
