Calculations and portals

[scolesm]

Hey there,

I'm working with products with ingredients with certain amounts of nutritional values.

I've currently got the following tables:

NutVal - contains a record for each measured nutritional value

Ingred - contains a record for each measured ingredient

InNut - contains all Ingredients with a record for each nutritional value per ingredient

InProd - contains a record for each ingredients in a product, indexed by product number

Prod - contains a record for each product

Each record in Prod contains a portal containing its nutritional breakdown. I need to do a specific calculation though, using the percent nutritional value per ingredient (contained in InNut) multiplied by the percent of formula per ingredient (contained in InProd) to get the mass of the nutritional value.

It's frustrating because in Prod right now, the two numbers are sitting in portals right next to eachother, but I can't wrap my mind around how to combine them.

I also want to avoid creating another table which requires user input. Assuming all other tables are complete (contain information on all available Ingredients and Nutritional Values), the user should only have to enter data into two tables, as I'm unsure how familiar they'll be with FM.

See attachments below! :

Any suggestions?

Thanks already,

M

[theyetti]

This might be easier if you could post your file with a small amount of sample data/records.

Cheers Pedro :-)

Edited June 15, 2006 by Guest

[scolesm]

Yeah, you're completely correct. Here's an uber basic form of my db.

Hope this works.

Just for clarification, I need to multiply the % Formula of each ingredient by the RM Spec of each Acid in that ingredient to get the Blend Spec. I also need to multiply the mg/dose by the Blend Spec to get a new mass.

Also, I realize in this example each ingredient is only used once. This will not be the case, otherwise I could do all calculations in the Additive Acids table. I, in poor practice, have done this in this file as it does show the correct values/calculations for this example.

Thanks again,

M

ProductsEm.zip

Edited June 15, 2006 by Guest
Better Attachment