I'm working on setting up a system in airtable for generating project pricing/quotes. I would like it to have 3 tiers of organization where a project is built out of a grouping of "components" each of which is a grouping of "materials". The reason for wanting components is so that I can reuse these to build quotes faster by piecing together existing components as well as define a labor price for the assembly of a component. I've been able to create the basic structure for this just fine using intermediate line item tables to define the quantity of different materials in a component and quantities of different components in a project quote.
I've set up a simple example base here for illustration: https://airtable.com/shrd46DwWkldZpeJv
Now here is where I've run into the problem that I haven't been able to come up with a solution for. I would like to be able to create a full list materials that corresponds to a project quote. The difficulty is that some materials may be used within multiple components and that the full count of materials in a project is reliant on the quantity of that material in a component and of the quantity of that component in a project. I have tried different combinations of rollups, counts, filters, etc but haven't been able to get to the correct outcome.
Does anyone have a way to generate this full material list per project? I would like like to be able to use this as a BOM to order and track everything needed for a project. It would probably make more sense to take this BOM into a different base, but ideally I'd like to be able use a filtered view of materials table to make this list first. If that isn't possible to achieve then I'm open to suggestions on ways to use a script or automation to generate this list.
Thanks in advance for any guidance! this one has been driving me a little crazy trying to figure out
