I think I have it figured out and would appreciate some feedback on how I’m handling it.
My “mud” recipe below needs to be broken into separate lines, like:
- Mud blue parts. . . . . . 50
- Mud red parts. . . . . . . 20
- Mud yellow parts. . . . . 30
Then I can use formulas to get what I need.
This quote plus a good night’s sleep helped:
Fortunately I’m not trying to do any multi-record calculations.
Hi - I dye fabric. I’m trying to get the right links to the right fields and have created a hot mess. I’m attaching the hand-drawn schema. One correction: Order lines: “belongs to” links to customer orders. To translate, black lines are links, double 00 indicates lookup.
The recipe called Mud = 50 parts blue, 30 parts yellow, 20 parts red. Right now the ‘recipe codes’ table shows the ingredients on one line, as written above. I need them to be on separate lines. Which they are in Ingredients | Recipes | Parts. Am I just linking to & looking up in the wrong table? Does the schema look like I should be combining the ‘Recipe Codes’ and ‘Ingredients | Recipes | Parts’ tables into one?
Edited to add: this is based on the structure of ‘Recipe Organizer.’ It works like that, but it turns out that’s not what I need.
Edited to add: Combining the two reciped] tables wasn’t the answer: I still get “blue, yellow, red” and “50,30,20” or blue 50, red 20, yellow 30.
Details, possibly excruciating, below:
Customer A orders 3 yards of velvet in that color, very dark, and 4 yards very light.
Customer C orders 5 yards of gauze in that’s medium-dark . . . etc, etc.
When done, I find I need to use this recipe at five different strengths.
I need to be able to rollup my recipes so I end up with one dye lot. I DO NOT have a problem with the formulas (except I haven’t tried a rollup yet.) My problem is getting my output to look like:
Customer A - Mud color | total weight of all fabric (velvet + cotton + gauze)
Customer C - Mud color | total weight of all their fabrics
= sum of total weights
sum of total wt * 50% blue = x mls of blue dye
sum of total wt * 30% yellow = y mls of yellow dye
sum of total wt * 20% red = z mls of red dye