Discussions

I have a Product Table.....and within this table I have created a Relationship with itself.....and my look-up field is called "Parent Product"....for example I have 4 Product records (A, B, C & D)..... example of relationship diagram below..

Product A (Parent to B)

---Product B (Parent to C)

-------Product C (Parent to D)

-----------Product D

I have another table in my app called Product Group. Product Groups (Parent) is related to Products (Child).....all 4 of the products (A, B, C & D).....in the products table are related to the same Product Group ("Group 1")...

I would like to have a field on my Products table called [Hierarchy Level]....this field will assign a hierarchy level for each record. (example below)

Hierarchy Level]=4-Product A (Parent to B)

Hierarchy Level]=3----Product B (Parent to C)

Hierarchy Level]=2--------Product C (Parent to D)

Hierarchy Level]=1----------Product D

How can I accomplish this?

*I know that I can create a summary field on my Product Groups table [# of Products] --- this will tell me the total number of Hierarchy Levels....I'm dealing with (4)..but how can I go about assigning each Product its particular Hierarchy Level within their related Product Group.

I appreciate and advice....

I love a good Relationship puzzle and I have created a test app which seems to work. Contact me via my profile and I will give you admin access.


https://yqc.quickbase.com/db/bj57f3zxg

Basically I have a field called [Count 1] which always has a formula value of 1.  One each of the three relationships I have a summary maximum of that field to determine of the product has children at that level. My summary field will have either a 1 or 0 in it as it's a summary maximum of the count 1 field which always has a value of 1.

Then my Hierarchy formula is

If(
[Product has Level B records (=1)]=1,4,
[Product has level C Records (=1)]=1,3,
[Product has Level D records (=1)]=1,2,1)

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....

Mark, I was able to come up with a "limited" solution.....a solution where I'm limited to 4 hierachy levels....(limited because I did not want to create allot of new fields.....but it works.....thank you

OK great.

I am currently trying to navigate a similar issue, My thought is to reverse your numbering.  On the child object, name the relationship parent_object. have a formula for "hierarchy_level" which will equal  parent_object.hierarchy_level + 1. I am new to QB, So I'd love to hear feedback on this solution before I travel too far down this road.

I will also need to keep track of objects related to all ancestors (lower hierarchy_levels).

I think you are going to find this difficult to work with both in constructing the table schema and entering data. I would go back to whatever system is creating the information. I assume this is some type of Multi-Level BOMs (Bill of Material). If you can get some file that describes the BOM as XML, JSON, or YAML hierarchy it would be easier to use script to both import the data on an ongoing basis and build a representation it in a table.

If YAML is used in project and want to validate the data , https://codebeautify.org/yaml-validator">https://codebeautify.org/yaml-validator">https://codebeautify.org/yaml-validator can be used. 

I'm not importing data, I'm working with someone  to make something new. There is no existing data. There is an existing process that I'm trying to make digital.
I know I may find it difficult, that's why I'm asking here. I'm wondering if Quickbase is the wrong system to try to store this type of data model.
This is going to be tracking experimental samples, and steps and processes (materials applied, coatings) applied to samples. When the samples are split (divided in 2 or more smaller sample batches), the resulting samples will need to have all the steps and processes that were associated to the "parent" sample. and the samples can be split multiple times. So when I look at the data for a grandchild sample, I need to know that the parent had what processes applied to it, and the grandparent had what applied to it.

I do believe  that QuickBase can do this Structure. I have done apps with self
Referencial relationships.

I am not saying it can't be done but it isn't going to be easy and there will be a lot of limitations. Modeling things like Bills of Materials or Chart of Accounts which are hierarchies are notoriously difficult even in SQL.

Joe Celko's Trees and Hierarchies in SQL for Smarties
http://www.amazon.com/Hierarchies-Smarties-Edition-Kaufmann-Management/dp/0123877334