Bill of Materials - Multiple Parts per Product?


#1

Hello,

I’m hoping someone is able to assist with this. We are trying to build a Bill of Materials for our products. I have a Products and Parts tab. The Products tab pulls the list of parts from the Parts tab using ‘Link to Parts’ and totals the costs using ‘Roll-up’ to determine our cost per product. However, the issue I run into is when some of the products have multiple of the same part. I can’t find a way to make this work as the roll-up formula I am using only pulls one of each of the listed parts, while I need it to pull multiples of specific parts for an accurate total.

i.e.
What it does now: 1x screw, 1x case, 1x cable
What I need: 6x screws, 1x case, 2x cable

Thank you.


#2

Hello,

Can’t you add a ‘quantity’ field to the Products table and then have a formula to calculate the cost?
I think it is not posible to do what you ask for because there is no criteria to know how many parts you want to pull from the Parts table.


#3

I think you need a join table to contain the line items like in an Invoice.

  • Products: name, price, etc
  • Parts: name, color, whatever…
  • Products_Composition: Product, Part, number of parts, cost per part, total cost, etc

Now you can have the Rollup you want, but to the joint table, to the Total Cost field.