Summarizing amounts of ingredients in a formula
I have a fairly complex database that I run a paint production line with. I have two tables involved: Raw Materials and Master Formulas. Some/most of the raw materials contain/are cut with solvents e.g. 25%, 40%. I have fields set up for the solvent name and percentage in material(some are straight solvent and have 100%). Six slots for solvents in all.
The master formulas have repeating fields for raw material and amount (40 reps). So a master formula may contain solvent x, and maybe some other material that is cut in solvent x. What I'd like to have is maybe a portal in the master formula showing the total of solvent x in the formula.
There are around 50 different solvents, so setting up a huge case calculation for each one would not be ideal. What I have are fields in the formula pulling all six solvents into the record. The reason I'd like to go with a portal is because and average number of solvents in a given formula is about seven, so a sum field for each solvent wouldn't present well because most of the sum fields would be 0 and not be needed.
I'm sure more information is needed, but any ideas would greatly be appreciated!
Thanks in advance- BatchMaker