Here I am again with this problem I still have not overcome.
I have a table “Bascol” (basic colors) where I have a Key Column ID_Color with only one letter and a NameColor column with the names of the pre-mixed colors my provider sells to my employer: B = black, W, White, etc. There are about 12 premixed colors so one letter IDs is enough.
I have a second table “Formulas” with a series of recipe-like formulas for the making of “new colors” or “color mixes” by combining a few (most of the time 3 or 4) of the premixed colors. Now in the fourth table (“Detail_Buckets”), I have a column with a dropdown referenced list from table Bascol which at the moment shows all of the “basics” instead of only the ones needed for the individual mixes. Let’s say, I make a Greige (Gray Beige) color; so I want the dropdown to only show white, black, yellow and red oxide. No need for the blues or greens.
After reading lots of help-articles on the appsheet forums, I came across these: https://support.google.com/appsheet/answer/10107665?hl=en&ref_topic=10102053&sjid=11810756257205107179-NC
https://support.google.com/appsheet/answer/10107321
where I understand that there should be a way of doing what I need, but I have not yet completely clear how. For that purpose, I included a new column in the “Formulas” table (#2), with the name “Combi” for combination, where I have all the initials of the basic colors needed for each recipe. So, in the case of Greige, the “combi” is BWXY (Black, White, red oXide, Yellow), For a dark purple you might expect a BLX (Black, bLue, red oXide) . Now, I understand that I need something like this in the “Valid If” or “Show If” section in the 4th table:
SELECT(Colbas[Id_Color], where [Id_Color] is contained in formulas[Combi]
i.e. Greige shoud only show Black, White, red oXide and Yellow) since its combi is BWXY
Can anyone give me the a help in writing the necesary expressions?
Complication 2: If this seems complicated, as next step, if it is possible I would like to have dissapear the choices as the mix progresses and reappear in case I need to readjust. For instance, lets say Greige formula is 17,000 g White, 1,500 g Black, 1,000 g Yellow, 500 g red oXide (tolerance plus/minus 5g each). Let s say i managed to have white and black in the right proportions, so the dropdown now only shows Yellow and redOx. But I pass by 10 grams with the yellow - it is a try and failure process - so a series of calculations will reset the goals to compensate: another 170 grams of white, another 15 g of black (so I need those options to “come back” while the Y option dissapears) and a new total of 505 of rOxide.
Is this doable? I will appreciate any ideas as my head is hurting already.
