I think I will be needing both a batch report, and a form in this case. But I am only busy on the form atm. Both I am aiming to do in access. There needs to be an interactive form for the operator in which they can both, view all fixed and pertinant information to the recipe they selected for the current batch, as well as enter 'input' variables like the desired total weight of the batch but also general info like, name of the supervisor, start time, end time etc. That form will then calculate the ingredient weights based on a combination of formulas. It's like a more complicated version of the Weightmaster that you have, except that it would also list the instructions seperately from the raw numbers. That was the user request. Basically, they want the whole thing to be on one form and a seperate table just for the weights and ingredients. I am prototyping the framework for this atm.
I also have two different types of 'variables'. There are 'variables' like in your case (pH, rpm, temp etc) used in the instructions themselves that look pretty much identical to the types you have in your example (right now they only exists embedded in the instruction text). An example would be :
"Heat up reactor to 78,4C"
"Add Chemical B to the mixture"
"Increase mix speed to 24 rpm"
"Control pH and note the value. Must not exceed a pH of 6"
Then there are variables used in the calculations, which is where it gets complicated:
Variables used in the calculations are things like coefficient A,B,C,D used in the calculation of the weight of ingredient A and coefficient E,F,G used in the calculation of coefficient A,B,C, which depend on coefficient H,I,J coming from the lab on that day. There are some that are constant within a recipe (all calculations in that recipe use the same value coefficient) and some that vary by ingredient used in the recipe (the coefficient value is different for every ingredient used in the recipe, and between recipes). Many of those coefficients are currently not named but hard coded into expressions in the excel files (all the ones that differ by ingredient and recipe). I'm going over the details with a chemist currently, but not all can have meaningful names. Some are just products of math with little human interpretability. However, I would like to expose those varibles properly anyway since even they sometimes needed to be changed in the past. Fortunately, the formulas that use those coefficients never really change. So the ingredient weights are calculated in a more complex structure where different coefficients change on the daily depending on the characteristics of the raw materials that need to be entered, but sometimes also just manual changes to some of those coefficients by a qualified chemist at the plant. There are often very technical reasons for it, like the pressure in the tank might be slightly different because a part got swapped out, or they need to use a different reactor that behaves differently because the one they normally use is in maintenace etc. So the form would have to list all those coefficients for the operator to inspect and make changes to if neccesary. There are default values, but they must be able to temporarily change them for the current batch. What is also important is that not all of those coefficients are used in every recipe. There are some where the whole step using coefficients EFG an HIJ does not exist and some where weights are purely calculated by ratio (like in your case). Sometimes the inbetween calculations use a different set of coefficients K,L,M, sometimes there are an additional 2 calculation steps for coefficients N,O,P and then Q,R,S. All those calculations that happen inbetween must be clearly layed out on the form the operator uses. There are obviously also new input variables for those inbetween steps. This all changes from one recipe to the next since there are many different chemicals being produced here, all with very specific requirements. That is the biggest challenge for me I think.
I've been busy simplifying the recipes to a common framework the last couple weeks but I found out that even the simplest common framework would basically require a gigantic form in which most elements are not used in most recipes. And just hiding them when not needed would result in a weird and unintuitive layout. Because the recipes are so varied in the info they need to display, as well as the calculations used and the variables that need to be manually entered, I had the idea of building a dynamic form on which control elements can be swapped out depending on the recipe. Calculations could then also be swapped out by switching between saved queries that hold them. Both, the query names and their corresponding subform names would then be saved to a table in the database that links to the correct recipe with an id informing to which subform container they belong. Then I write a module that swaps out all those subforms and queries based on the recipe when the master form gets loaded. I'm still not sure how to deal with the variables that should have default values but need to be changed on the fly and then revert back the next time they are loaded. I suppose I could write a module that is a simplified version of an audit trail tracking the change that was made to the database during the current batch in a temp table and when the form gets closed replace the modified value with the prior one. But that seems unsafe to me since the value in the database actually changes during that process. The other option would be to store only the default values in the tables, then have the queries make empty fields that correspond to them, then write a function that looks if a particular field has a default value in a table and populate the control with that value. That way I would get the default value, but the field is not linked directly to the table, so changing it would not affect them. The queries would then need to reference the control element values on the form instead of the tables for the calculations. Haven't tried that out yet, so maybe acess won't let me change the field value after placing it or there might be issues with recalculating after entereing new values when using requery or changing focus etc. Or maybe there are simpler solutions to this, I'd be happy to hear any. I have a simple version of a dynamic form right now that I am playing with that works ok. I can swap out subforms and queries on the fly, the details of which are saved in a query table linked to a recipe by id.
As for how to store the variables, some differ by ingredient and recipe combination (all the weird, non-name math related ones). I had placed them as seperate columns in a junction table between ingredients and recipes. But that basically means that I'd have to add another column to that table every time a new coefficient might be needed. It also does not help that coefficients would then share a common name between recipe/ingredient combinations. That's why I decided to better store them in a seperate "variable" or "coefficient table" similar to what you had suggested with the other type of variables and link that to the junction table. That way I'd have a different ROW for every coefficient in which I can also name it and give a description by adding those corresponding columns. I could then also have any number of coefficients for any R/I combination. Some might need 1 or 2, others 5 or 6 and they would all be able to be named independently of eachother. Aparently, what I had been looking for was a ternary relationship in this case. I'm setting that up right now trying to see if this is working better. I have also looked into class inheritance for tables but I do not think that will work as well in this context and I'm not sure if Access can even handle it. I've only seen examples using sqlserver.
It's a pretty big task, I think I'll be busy with this for a couple months at least. Which is why I will likely split up the problem and just ask more specific questions whenever they come up in seperate threads.
The instructions I can do pretty much like you described. Just have commonly repeating instruction texts saved to one table, linked to a table that holds all the instruction steps, link those to the ingredients and add another table with instruction variables as a child table. Then use a function to replace placeholders in the instruction text string with the corresponding instruction variables. I could also leave out the instruction variables and just have them permanently embedded in the instruction string. It's less elegant of course. I'd have lots of instruction strings that only differ in one little thing, so a different one every time pH might be different etc. So, I think in the long run, your solution fits better in this case. Which is why I will likely implement the instruction steps exactly like that.