please help with value related to field and other table

[mathac]

Please Please Help!

I have 2 tables, Ingredients and products.

The product layout has fields for line items,1. formulas1, formulas2, etc.-which is a drop down menu to select the ingredients from related to the field manufacturer. Works fine. 2. manufacturer list drop down menu related to the formulas list from the table ingredients. Also works fine. 3. I need to capture the cost per gram in the related table Ingredients for the field cost per gram in products table. The value would depend on the formula selected in each line item. I don't know if I need to use GetNthRecord, create some kind of If formula? The value list with related tables doesn't seem to work. I don't know how to construct calculation formulas??

Thanks for any input.

[comment]

It seems like you are missing a table or two:

First, you should be using a separate table for the line items - see the demo here:

http://fmforums.com/forum/showpost.php?post/309136/

(in your case Invoices would be Products, and Products would be Ingredients.)

Next, if an ingredient has more than one cost per gram (, then you should have another table for these too.

[mathac]

thanks Cramps,

I'll elaborate a little bit. To make an herbal product, it may include several ingredients, each with it's own cost per gram used for a total 100g bottle.

So, I have a table I'll call Ingredients with the fields- herbs name, an alterantive pharmaceutical name, cost per g, file ID(serical no.), Manufacturer.

It is related to table products by manufacturer or company (each company may have different pricing and/or different products), and herb name. the relationship gragh and value list worked great for creating drop down menus in the products layout. I use the products layout as an estimate for myself to calculate pricing, etc. Therefore, I need the cost per g to somehow auto-enter depending on the ingredient selected for ingredient 1, 2, 3, etc. It appears you are suggesting an additional table with the cost and ingredients? Are file Id's useful in this? Would that help me out at all to create serial numbers in the products table?

[comment]

Not sure what you mean by "file id's". Practically every table should have a unique ID field (auto-entered serial number) - and this is the field you want to use for relationships to the table's children. Have you looked at the file I have mentioned above? I believe it does 90% of what you need (I am still not clear on what the other 10% are...).

---

Who is Cramps?