A filtered dropdown list from a column of another table

[CorneliusH]

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=118107562572051071...

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.

[Suvrutt_Gurjar · Accepted solution]

As you can see from the expression used, the [Color] options will start appearing only when the  [ID_BUCKET] is selected in the "DETALLE CUBETAS" form.  It basically pulls the options from the parent tables. In the screenshot shared by you [ID_CUBETA] field is empty. You can check after populating it.

For user convenience , you can implement ideas such as making that [Color] field visible only when the [ID_CUBETA] field is populated or is non blank.

Of course I am assuming [ID_CUBETA] is same field as [ID_BUCKET] 

Since we are switching between Spanish and English names, I am assuming it.

My above reply is based on approach suggested by me. earlier of adding [Combination] in "Orders" table and then pulling it from there in the valid_if of [Color] column in the " Detail_Buckets" table.

Edit: if you have pulled the [Combi] column in the "Detail_Buckets" table , then in the same table the valid_if for [Color] column can be something like below. However please note that the [Color] options will show only when [ID_CUBETA] is populated.

SELECT(Colbas[Id_Color], CONTAINS([Combi], [Id_Color]))

[Suvrutt_Gurjar · Accepted solution]

Yes, you are right, it seems so.

[Suvrutt_Gurjar]

I think in such questions that span multiple tables, in addition to textual description, a few screenshots of the relevant table columns with their reference relationships helps to better understand the requirement. A few screenshots of the related view ( for example where you are looking for the said drop down) will help still better.

You may want to do so for a better response rate from the community.

Edit: Just an an example, the post referred below makes good use of relevant screenshots to describe the issue. Of course always use dummy data in any shared screenshots.

Solved: Teach me how to display measure_time value from an... - Google Cloud Community

[CorneliusH]

BASIC COLORS CATALOG IN SPREADSHEETTABLE COLBAS (COLORS-BASIC)FORMULAS SPREADSHEET - notice that out of 10+ colors, only 3-4 are used in any given formulaScreenshot of Formulas tablehere is the form with the erferenced listthis is the dropdown I want to reduce to only the needed options

I hope this gives you a better idea. I did some editing to the real data for the purpose of "privacy policies" and because my app is in a mix of Spanish end English, so I did some translating

[Suvrutt_Gurjar]

Thank you. Could you mention, on which table the following form is based on? And how the column MIX is populated there? Does it reference the Formulas table?

[CorneliusH]

Let me see if this clarifies the questions

and this is how the form "Details" should work in 4 progressive movements:

A series of movements in the last table

[CorneliusH]

parent table orderschild table bucketsgrandchild table details

the data on the desired (goal) color/mix is running through all tables

[Suvrutt_Gurjar]

Thank you for the details.

Please try below

1. Please add a column called say [Combination] in the Orders table with an expression something like [Color].[Combination]. This column will pull the [Combination] column from the referenced Formulas table.

2. Then the valid if  expression in the [Color] column in the Detail_Buckets table can be something like

SELECT(Colbas[Id_Color], CONTAINS([ID_BUCKET].[ID_ORDER].[Combination], [Id_Color]))

[CorneliusH]

Thanks. I am trying but it is not working yet. Are you sure of that [ID_BUCKET].[ID_ORDER].[Combination] part? is ir right to have 3 fields linked with periods?

[Suvrutt_Gurjar]

---

@CorneliusH wrote:

Are you sure of that [ID_BUCKET].[ID_ORDER].[Combination] part? is ir right to have 3 fields linked with periods?

---

as per the images shared by you, "Orders" table is child of Formulas table, "Buckets" table is  child of "Orders' table and "Detail_Buckets" table is child of "Buckets" table. In this case  [ID_BUCKET].[ID_ORDER].[Combination] is called as chained dereference. Please refer the article below

Dereference expressions - AppSheet Help

---

@CorneliusH wrote:

Thanks. I am trying but it is not working yet

---

You may want to ensure that the new recommended [Combination] column is populated in all existing records of the "orders" table if you have added it as a real column.

You may also want to post in what way it is not working.

[CorneliusH]

Thanks for your kind replies. 

I had it working for a few minutes then it all dissapeared. As a way to make sure of everything, I made a field for "mix" (the color I am mixing) and the "Combi"-nation of all colors I need in all the 3 related tables: orders, buckets and details, but suddenly they dissapeared, and ddo not show up anymore. At first they dissapeared from the system generated "details form" but showed up in the ones I made, but eventually everything dissapeared. I made sure that the columns are markedd "show" on all tables, I have tried to add ttem manually in the views, but nothing. ANd since the option was working a while, no i have an empty dropdown. 

it is making me lots of headache

thanks 

cornelius

[Suvrutt_Gurjar · Accepted solution]

As you can see from the expression used, the [Color] options will start appearing only when the  [ID_BUCKET] is selected in the "DETALLE CUBETAS" form.  It basically pulls the options from the parent tables. In the screenshot shared by you [ID_CUBETA] field is empty. You can check after populating it.

For user convenience , you can implement ideas such as making that [Color] field visible only when the [ID_CUBETA] field is populated or is non blank.

Of course I am assuming [ID_CUBETA] is same field as [ID_BUCKET] 

Since we are switching between Spanish and English names, I am assuming it.

My above reply is based on approach suggested by me. earlier of adding [Combination] in "Orders" table and then pulling it from there in the valid_if of [Color] column in the " Detail_Buckets" table.

Edit: if you have pulled the [Combi] column in the "Detail_Buckets" table , then in the same table the valid_if for [Color] column can be something like below. However please note that the [Color] options will show only when [ID_CUBETA] is populated.

SELECT(Colbas[Id_Color], CONTAINS([Combi], [Id_Color]))

[CorneliusH]

i am not sure that that is the only complication. Here I created a new "pedido" (order), then a new Cubeta (bucket) then open a new Detalle (detail). The Ids are on, but the Combi fields is nowhere to be seen and the colors are gone for good. 😞 😞

[Suvrutt_Gurjar]

You may want to check /share what is the expression fir the [color] column and what it shows in the test pane.

Also earlier the [Color] column was in " Detail_Buckets" form. Here I see only " Buckets" form.

Anyway if you wish I can take a look at the app since we have discussed in a long thread so far.

You may want to DM me, if you wish.

Or alternatively,  I can creare another sample app , say an Order capture that shows how one can pull values by chained dereference and that you can refer as a sample. I can share such an app with you.

Please let me know.

I am in travel during the weekend. So I may take a look on Monday.

[CorneliusH]

Thanx. I am afraid I don't know how to DM in this forum. But I sent you a friend request. Please DM me. I d be very happy to receive a little hep.

[Suvrutt_Gurjar]

You can use this option under your profile to send a DM

Thereafter you can click / tap on the following highlighted icon to open the new message pane

[CorneliusH]

I guess I am still too new in the forum

[Suvrutt_Gurjar · Accepted solution]

Yes, you are right, it seems so.

[CorneliusH]

can you please drop me a note at <PII removed by staff> ?

[CorneliusH]

Hello. Can you please help me once more to expand the same formula? I got it running now like this:
SELECT(Colbas[Id_Color], CONTAINS([ID_CUBETA].[ID_CUBETA].[Combi],[_Thisrow].[Id_Color]))

I want to filter it further so that, if I already added enough of one color, than only the remaining colors shoud stay. Lets say: I have to mix 4 colors: Black, White, Yellow, Red. I start with white, so in step 2, only black, yellow and red pop up; after adding black, only red and yellow are to be seen. Of course that means I am staying within specs. Until all colors are done, no more colors swhow app, status changes to "done" and I cannot add any further. 

My guts tell me the formula should go somewhat like this:

and(

(First part already solved)SELECT(Colbas[Id_Color],(CONTAINS([ID_CUBETA].[ID_CUBETA].[Combi], [Id_Color],

(second part) SELECT(DETALLE_CUBETAS[COLOR],AND(

[ID_CUBETA],[_THISROW].[ID_CUBETA],

[%TAJE]<0.99)))))

Where [%taje] is a percentage of the real mix divided by the original mix from the formulas table. However I cannot make Appsheet accept that expression as valid. Maybe to many "selects", maybe to many "ands", maybe both. Anyhow, I want to see only the colors if:

1) they are needed for the particular formula and
2) if they are in the formula but have not been added as to fill at least 99.9 of the formula within that particular bucket.

What am I doing wrong?

[Suvrutt_Gurjar · Accepted solution]

I have responded in the other post thread that you have started. I thought that is a cleaner new thread with clear description of the issue.

Filter a list by 3 criteria, jump from parent to g... - Google Cloud Community

Please take a look in that post thread.