Bill of Materials / Formula

[Claude Sebastien]

Ok. I'm a super neophyte newbie so please don't be too harsh with me. Only a few days with my head in the database world. At the moment I'm trying to work out basic relationships and I'm alright until I get to one table. On the one hand I've got all my Purchase Order data, and on the other I've got Invoice data. In the middle however, I've got a Bill of Materials, which is in essence a formula table. Each record describes a Parent Product Code Number, a Batch Quantity (almost always 1), Component Product Code Number, Quantity of Component Per Parent, and a Decimal Control Code. I am assuming this would be a Many-To-Many relationship conundrum. I need to relate cost data through the formula/bill of materials from the Purchase Order to the Invoice. I am assuming this is a fairly common exercise. Has anyone done this before, and if so how best do I structure this. Many thanks in advance.

[bruceR]

In general many to many is done through a join table. So for example a project system might have Projects and Employees and their would be a join table where each record has one project ID and one employee ID; plus in this case some kind of role or responsibility description.

Since your relation graph provides no detail on key fields it is difficult to help you, and I don't quite understand your explanation.

[daltwin]

Hi,

Sounds like you're treating your bill of materials table as a line items table for your invoices?

If so, it's INVOICES --- BOM --- PRODUCTS

[The Shadow]

I have a simple BOM example I threw together a while ago.

BilloM.fp7.zip

[Landin]

Shadow,

I have seen you simple BOM database before and it is very nice because it is the simplest way I have seen this done. I was wondering if you had a way to print it out in a second way. Instead of just seeing the first top level under the bike see all components listed for each part and indented to keep separate. So for example

Bike

--Wheel

----Tire

----Spoke

----Rim

--Frame

--Seat

[The Shadow]

Hi Landin,

Sorry for the delay in response, I wasn't watching this thread.

Here's a modification that supports the indented list as in your example, it's all done using unstored calculations though, so its not really going to fly for a 747's BOM though. :B

BilloM.fp7.zip