Multi level sub Assembly- Can I get the children rolled up under the great grandparent

[Mike_Wiley]

I have a 4 level part Hierarchy. The lowest level contains individual parts but some of the low level parts are simple assemblies. I can create kits for these simple assemblies, Screws, nuts, bolts, labor etc… But when I want to roll up that assembly into a parent assembly things get wonky. I figured out how to get 2 layers of assemblies to play nice but I need up to 4.
Low level assembly A rolls up and is part of Assembly C which is then part of assembly F which then becomes part of the finished assembly J.

Can I get all the assemblies costs to roll up and show one final cost of goods for assembly J?

[David_Skinner]

You should be able to roll up through the hierarchy. So a field in Assembly C rolls up the values in Assembly A. Then a field in Assembly F rolls up the values in Assembly C. Then a field in Assembly J rolls up the values in Assembly F.

[Mike_Wiley]

Thanks David. I need to know how to do it? Is my formula wrong or are the various fields pointing to the wrong parent?

[David_Skinner]

I’m assuming you have a Currency field called “Cost” in the Assembly A table. Make sure this is formatted correctly when you set it up.

In the Assembly C table you would have a Link To Record field that links to the Assembly A table. Make sure you tick “Allow Linking To Multiple Records”. In this field, for each record in the Assembly C table, you select all of the components that are needed from the Assembly A table. Again in the Assembly C table, make a field called “Cost”. This will be a Roll Up field that uses the Link to Record field we have just created. This will roll up the “Cost” field from Assembly A table. Type “sum(values)” into the aggregate function field. Make sure this field is formatted correctly (into £s or whatever).

In the Assembly F table you would have a Link To Record field that links to the Assembly C table. Make sure you tick “Allow Linking To Multiple Records”. In this field, for each record in the Assembly F table, you select all of the components that are needed from the Assembly C table. Again in the Assembly F table, make a field called “Cost”. This will be a Roll Up field that uses the Link to Record field we have just created. This will roll up the “Cost” field from Assembly C table. Type “sum(values)” into the aggregate function field. Make sure this field is formatted correctly (into £s or whatever).

etc.

[Mike_Wiley]

Thank You. I will try that. :slightly_smiling_face:

[David_Haile]

David,

I am also having trouble with this concept. Would you mind explaining in greater detail using the following example?

Parts: engine, tire, frame, rim, window, lug nut, radio, trailer hitch
Wheel: Contains rim(1), tire(1), lug nut (5)
Car: Contains engine(1), window(4), wheel(4)
Truck: Contains engine(1), window(4), wheel(4), trailer hitch (1)
Garage: Contains car (2), truck (1)

If I’m designing a table that contains a Car, it has one field which is a link to parts which has the engine and windows in it and another field which contains a link to the wheel (4) which has the tire, rim, and lug nuts (5) in it. This is a simple example which gets much more complicated the more types of sub-assemblies that are in my database. I really don’t want to have to add 20 lug nuts from the Parts table to my Car table. I want to add 4 wheels. I can do this with any spreadsheet so I’d imagine I should find a way to do it with AirTable. If not, I’m out.

Expanding on the above example: Let’s assume the Car table has a Parts field and a Wheel field. How do I pull the cost of the Parts (engine & windows) and also the cost of the wheels (4 x cost from Wheel table) into a single Cost field?

I wonder if the answer is in a report or view.