You may or may not have a many to many relationship. You might have this many to many relationship:
Materials::MaterialID = Material_Product::MaterialID
Products::ProductID = Material_Product::ProductID
Where you have one record in Materials for each type of material used to make at least one product. And one record in Products for each product produced. Each record in Material_Product identifies one Material used in the production of one product and includes a a qty field that records how much of that material is needed to produce a unit quantity of your product. Material_Product is often referred to as the BOM or Bill of Materials.
The unit cost for each material would be listed in Materials.
You can use a portal to Material_Product on a Products layout to construct the Bill of Materials for a given product. A drop down list can be used to select MaterialID's by name. THis is very similar to a typical invoicing set up, BTW.
To get a cost breakdown on the Materials used to produce a single product or unit quantity of the product, you can perform a find on Material_Product to find the records for one product on a layout designed for this purpose. It can refer to material costs from the Materials table and the Product cost from the Products table in order to produce your percentage break down. Such a report could actually list multiple products with the material cost break down for each all in the same report.
A portal to Material_Product on a Products layout could also show this information.
Thanks for the reply PhilModjunk. I may be misunderstanding you but I feel like I need to explain in more detail what I'm after. My raw materials include 100+ different hues of pigment. My end product will use 6 different pigments. I already have formulas for each product broke down by the % of each pigment it requires per lb of finished product. For example, ProductID 1 may be composed of 75% Black Pigment per pound, but ProductID 2 maybe only be composed of 53% Black. I have so far a Materials table listing the name of Pigment, the supplier, and it's cost. My second table is a Products table. On this table I have the ProductID, it's name and the formula which includes 6 different pigments (available via drop down list), and the % of that pigment required to make that product. I was also planning on including a production cost on this table as well that would recognize the cost of each pigment and multiply by the % the formula requires. Thanks again for any help you can give me. I'm very new at this and would appreciate any help!
My raw materials include 100+ different hues of pigment.
Which means you would have 100+ different records in the Materials table
I already have formulas for each product broke down by the % of each pigment it requires per lb of finished product.
These percentages would be listed in the BOM, the Material_Product table.
The records in the BOM for what you have described
ProductID MaterialID Pct
1 <ID for Black> 0.75
2 <ID for Black> 0.53
This format allows you to produce costing reports from layouts based on the Material_Product table and can be for just one product or more than one product with quite a few different sub totals possible depending on how you perform finds, sort records and defien summary fields with sub summary layout parts to get the needed sub totals.
I have made great success in my database. Upon your (PhilModJunk) suggestion that my database was similar to an invoice database, I referenced the invoice starter solution and I have made tremendous progress. Thanks for the suggestion! The database is doing everything I need except for the portal. The most important part on my db. I have the portal on my "Formula Details" table with the portal showing related records from "Formula Data." My fields are specified Formula Data::Pigment (with a drop down list), Formula Data::Qty, and so forth (exactly like the Invoice starter solution). My problem is the portal is blank once I'm out of layout mode and I can't access the fields to put in the information. Actually, my first record is doing what I want it too. Once I select the pigment, it finds the price. I can enter the qty and it calculates a price. For some reason, I can't enter data into the portal on any other records. I've tried comparing my portal to the Invoice portal but I'm missing something. I've tried scripts (GoToPortalRow). I've tried the old copy and paste trick. I'm not sure if I have a relationship problem or if there is some magic box I need to check or uncheck but I'm almost at my wits end trying to figure it out. Again, this is a whole new experience for me and any light anyone can shed would be more appreciated than you can imagine. Please Help!! Thanks!
Open Manage | Database | Relationships
Find the line linking your layout's table to the portal's table and double click it.
In the dialog that opens, select "allow creation of records via this relationship" for your portal's table.
Now you should be able to add new records in the portal's table by entering data into the blank 'add' row of your portal.
Oh my goodness! It works!! Thank you sooo much!!
You have helped me out so much already but I've got one more question. If I update a price on my Pigment table, is there a way I can get the new price to automatically replace the one in the portal without having to reselect the pigment in the dropdown list in the portal?
Yes. It sounds like you kept the auto-entered data method (either a looked up value or calculation option) that copies price data into a field in the Bill of Materials table (The table used for your portal).
If you change the calcualtion to refer directly to the price in your pigments table instead of copying it, the computed cost wil automatically update when you modify the price in the pigments table.
Your current table probably has an expression similar to this:
Qty * LookedUpPriceField
change it to:
Qty * Pigments::PriceField
My portal was Unit Price = Formula Data::Price but I changed it to Pigment::price and it's working. Thanks again!!
I have a new problem. Several of my products have the same internal product code so after putting all 300 of them in, I went back in and created another field with an auto enter serial number on my products table and then used replace field contacts. I've done the same thing on the formulas table and the relationship seems to be working. However, I can't enter information into my portal now which is on my formulas table. My drop down lists are there but once the information is committed, I get an error that states "This field cannot be modified until "" is give a valid value". Other than entering the records into my products table, I have made no other changes. Any suggestions?
Check the validaton rules on your fields. One of these no longer works given the changes that you have made. Either that or you have a relationship where the field in the parent record is now empty and your action is attempting to create a new record in the related table.