Skip to main content

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

 

Because you want to consolidate usage for an individual material that appears across multiple components, your best bet is to use scripting to create “flattened links” to materials, summing up individual materials across all components. 

Unless you are familiar with scripting, I recommend hiring someone to write the script for you. It is not a beginning scripting project.

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

Because you want to consolidate usage for an individual material that appears across multiple components, your best bet is to use scripting to create “flattened links” to materials, summing up individual materials across all components. 

Unless you are familiar with scripting, I recommend hiring someone to write the script for you. It is not a beginning scripting project.


Got it, thanks. I wrote a script for it that loops through and generates the BOM and its working well.

Reply


Terms & Conditions