4 Replies Latest reply on Aug 21, 2009 1:35 PM by ninja

# Summarizing amounts of ingredients in a formula

### Title

Summarizing amounts of ingredients in a formula

### Post

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!

• ###### 1. Re: Summarizing amounts of ingredients in a formula

Howdy BatchMaker,

Funny, I just built that function onto one of my Dbases this afternoon! :)  No, really, I actually did!

Life's a whole lot simpler without repeating fields for this.  I know it's a big shift, but get off of the repeating fields and use a child table instead.  Then you can simply connect a table with a list of products and calculate

Sum(ChildTable::FormulaLineItemPct) for each material, whether or not it is a solvent.

Then it's pretty easy to continue the calculation into %solids, %volatiles, %pigment, pigment ratios, colorant schemes, etc.

How to do it with repeating fields....I'm not sure I want to know :o  I can see why the calculation would be rather complex.

• ###### 2. Re: Summarizing amounts of ingredients in a formula

I have a couple of things holding me back though.

How do you control the order of the line items (this item first, this item fifth)? Also, does the calculation you wrote give percent solvent, or percent solvent x (25% xylene out of three different materials). Because solvent slot one might contain any of the fifty or so different ones.

Yeah, what I'm after is solvent package information directly on the formula, which is not as simple as %solids, %volatiles, %pigment, pigment ratios, which I have already.

PS- nice to see another paint manufacturer on here!

• ###### 3. Re: Summarizing amounts of ingredients in a formula

I can't suggest a complete solution as I can't put together a complete picture from these posts, but perhaps by answering one of your questions, I can get you started in the correct direction.

"How do you control the order of the line items?"

Since each portal row is a record, you can add a number field to each portal record and use it to document the order. When you create your portal, you can specify a sort order that uses this number field to display the records in a specified sequence. (You can also specify a sort order in the relationship to get the same effect.)

• ###### 4. Re: Summarizing amounts of ingredients in a formula

BatchMaker wrote:

does the calculation you wrote give percent solvent, or percent solvent x (25% xylene out of three different materials). Because solvent slot one might contain any of the fifty or so different ones.

Yeah, what I'm after is solvent package information directly on the formula, which is not as simple as %solids, %volatiles, %pigment, pigment ratios, which I have already.

PS- nice to see another paint manufacturer on here!

The data that becomes available in the child table is/can be:

-how much of each ingredient (in % or wfraction as you decide to have it entered)

- special characteristics of the ingredient (solid/solvent/pigment/organic/etc.)

- and you can put a multiplier in such as 25% acrylic/75% pentanediol in such a way as to use it in batch calculations and batch summaries.

- Think through (draw through) a child table relationship to replace your repeting fields, then start thinking through the new capabilities you could have now that the individual ingredient data is easily accessible.  Make a list of what you want to end up with, then plug the pertinent fields into the various tables to make the calculations possible.  If you choose the structure well (ie. where you put the fields), the math should become pretty straightforward.

- For the mixed items, you may need a grandchild table.  I haven't needed one so far, so I haven't thought that one through to a great extent.  I imagine you would simply tack the grandchild fields on to the end of the calculation...if there aren't any, the calculation will just end up adding zero.

The key to making the data readily available is to use a related table instead of the repeating fields.  It may be available in both methods, but it's actually pretty straightforward using the child table...I've yet to find a use for a repeating field, I suppose it was a step toward related tables/dbases during the evolution of FMP but that's just a guess.

Sorry to burst the bubble, but I'm not a paint guy...but I process functional materials using extremely similar slurry technology and ingredients.

Down with phthalates!

Edit: Why do you need to control the order?  The calculation won't care.  Is there another reason to sort them?