Find hierarchy of item

[Paras_Sood]

Hi

Is it possible to find what level an item is in a hierarchy? I am making a bill of materials and thought that maybe this process should be automated rather than me adding it manually.

If this item does not have any ingredients (child) then level should be 0

If this item has ingredient, and those ingredients do not have a child then level should be 1

If this item has ingredient which itself has an ingredient, then level should be 2

Some level of recursion is required but Im not sure how we can achieve that in appsheet.

[Suvrutt_Gurjar]

In general , you could have a VC called say [BOM Level] in your highest grandparent table  with an expression something like

IF(ISBLANK([Related Parents]),0,1)+

IF(ISBLANK([Related Parents][Related Grandchildren]),0,1)

where [Related Parents] is the reverse reference column in the grandparent table.

 [Related Grandchildren] is the reverse reference column in the parent table.

[Paras_Sood]

But if the BoM could go around 5 levels deep , then I would need 5 of these if conditions?

[Suvrutt_Gurjar]

Okay, thank you. 

In general, yes, if you need to detect each of those 5 conditions, you will need those many conditions.

You had earlier mentioned 3 levels only. 🙂 In general helps to share these important details so that solution can be more appropriate.

So, if there are 5 levels, does it mean the structure is Grand grand grandfthaer ( Level 1), Grand grandftaher (level 2), Grandfather (Level 3) , Parent ( level4) and Child ( Level5) table?

Could you confirm, how the hierarchy is structured for those five levels?

[Paras_Sood]

Yes so because it's a bill of materials, I have two tables: one for all the items (including all raw materials, finish products, and work in progress)

and one table for the relationship between these items. For example

Stage 1: Milk Chocolate is made using cocoa butter, cocoa mass and sugar\

Stage 2: Milk Chocolate is coated around almonds to produce milk chocolate almonds.

Stage 3: Milk Chocolate Almonds are packed in 100g bags.

Stage 4: 100g bags of milk chocolate almonds are packed in pairs of 18 in a shipper box

Stage 5: 72 of these boxes are kept on a pallet and dispatched.

Ideally all should be in the BoM.

[Suvrutt_Gurjar]

Thank you. But does each stage mean different table each ? Could you elaborate where the BOM levels will be captured?

Could you share overview of your data schema and how you are assigning BOM levels?

[Paras_Sood]

That's what I am trying to figure out because an item could be used in different stages of production. For example we may use peanuts in stage 2 (coat milk chocolate around a peanut centre) or we may also have peanuts in stage 1 (directly used in milk chocolate recipe)

Tables are always 2: One to list all of the items in each stage and the other table to include all the relationships between these items

[Suvrutt_Gurjar]

So same peanuts item is used in say 25 assemblies , there will need to be 25 levels instances stored ( Between level 1 and 5) for peanuts for each of those assemblies. That seems like a a bit complex requirement. So relationship table will need to store 25 records for peanuts for each of those assemblies?

[Paras_Sood]

You're right maybe it's too complex. I think I should leave BoM level for now. Or, maybe I can just collect the max level and the min level?

[Suvrutt_Gurjar]

Yes, as requested, if you could share data schema of those tables, the community could still give it a try. But in general managing 5 levels for many assemblies for same item with just two tables could be a bit complex. 

[Paras_Sood]

Yes ofc

Products table:

Recipes table:

[Suvrutt_Gurjar]

Thank you. Which is the items table and which is the relationship table and how the two are currently related=, by referencing etc?