Getting information from one table for a calculation by a record in another table

[emile]

Hi,

I am new to filemaker and am using it to do pricing analysis on prototype products we make. I have two tables: one has a record for each product we are developing showing the amount of materials in each product. The other table has a record for each material giving the price and other specifics. I want a way to have the product table look up records in the materials table and return the price or other information about the material.

For example, record for product A might have 5 different materials: base material, primer, paint, trim, etc. I want the record for product A to be able to access and look up the price (for example) of "paint" from the materials list and use it to do a calculation that would show how much product A would cost.

Ideally, the price of "paint" is changed then the product record would update to show this.

What is the best way to do this, scripting? I am, unfortunately, quite new to scripting also.

Thanks for any help you can give.

[mr_vodka]

There should be 3 tables. Product, Materials, ProductMaterials which is a join table. Each product consists of many materials which can be added or subtracted from it. However, each materials is its own seperate component.

[emile]

Thanks for your reply. I am not sure I understand the ProductMaterial join table. Can you explain?

thanks

[mr_vodka]

Well a Product is a product. A material is a material. However, a product is made up of many materials.

Therefore, you should have another table that stores the ID numbers of each of those materials items for each product.

ProductID     MaterialID

1             45

1             22

1             9

2             1

2             59

So lets say product 1 is a bed. Material 45 is a mattresss, 22 a frame, and 9 the boxspring. Product 2 is a dresser set. Material 1 is the dresser and material 59 is the mirror. Do you follow? So if you have prices stored within the Material table specific to that item you can do two things. Either you can store the cost at the time that the material was added for the material (historical or point in time) with a lookup, or you can always price it will the latest amount by summing up the amounts from the material table.