LinkedIn
Twitter
Here again.
I am still trying to develope an app to aid me in the process of mixing industrial paint colors old fashioned try and failure method. My app now has 5 sheets:
1 - Basic Colors Catalog ("Colbas")
2 - Formulas Header ("Formulas")
3 - Formulas Details ("Det_Formulas")
4 - Work Orders (spanish "Pedidos")
5 - "Buckets" - Header (Spanish "cubetas"; every bucket/ gallon /quart of paint has its own registry)
6 - Buckets - Details (Spanish "Detalle_Cubetas")
If I receive an order to make, say, 1 bucket of Dark Brown paint, the app should be able to go to the Formulas tables and fetch the recipe. Let*s say it is 7,500 grams of red, 6500 of yellow, 5,000 of black and 1,000 of white with a tolerance of say maximum 4 grams plus.
After a month of try and failure, I finally managed to have the app structure set up and I am able to see the formulas and recipes running OK. But, one of the issues I run into over and over is that formulas fail to their job. For instance, I need the app to the a recalculation whenever I ran out of spec and make a bigger formula to compensate. Let-s say after having a perfect mix of 7,500 grams of red and 6500 grams of yellow, I comitt a mistake and pour in 5,010 grams of black. Simple math tell me that I will need 13 more grams of yellow and 15 more of red, plus instead of 1,000 grams of white I can count on 1,002. (The number in real life are far from being this simple and that is why I am doing the whole apping thing).
I have tried to come up with a solution: Have a formula calculate the difference of the formula versus the real weight, and as soon as one of them goes out of spec, take the highest "factor", multiply all the numers and give me a new set of specs. But - Appsheet will only give me zeros. Also I have tried to come up with a factor that will divide every formula by 19 to give me liters and by 4/19 to give me Meican gallons (4 Liters), but again, Appsheet maths seem to fail constantly. Am I doing something wrong? Why can you not rely on simple basic math when using Appsheet?
Formula for calculating the Max factor to compensate for run overs:
SUM(
SELECT(
DETALLE_CUBETAS[CANT], ''amount" in grams
AND([COLOR]=[_THISROW].[COLOR],
[ID_CUBETA]=[_THISROW].[ID_CUBETA])))
/
(LOOKUP([MIXCOL],"Det_Formulas","ID_DETF","CANT")
)
MIXCOL = A combined factor of initials to fetch the required number of grams for a given color in a formula: DBW = Dark Brown Mix, White Paint required, etc. If I am not wrong this formula shoud always produce a number around 1; but zeros is all I get.
Why???
