IN(), CONTAINS(),or something else for valid_if filter for a dynamic search with an ENUMLIST()?

[andrew_caff]

Hello, 

I am having trouble figuring out if this is possible in appsheet. 

I am looking to have a dynamic dropdown menu that is an ENUMLIST, that selects rows from another table where a certain column type ENUMLIST contains an element selected in the dropdown menu. An example follows

Table_1:

Table_1_ID	Table_1_list
1	a
2	b

Table_2:

Table_2_ID	Table_2_lists
1	a
2	a,b
3	b

Table_3:

Table_3_ID	List_Storage
1	a,b

The user then has the options to select from the list {a,b} in their dropdown menu. The list is generated using the valid_if expression Table_1[Table_1_id] so the number of elements will change over time. The user's selection is stored in Table 3 as an ENUMLIST column for the following filter:

SELECT(Table_2[Table_2_ID],In([Table_2_lists],Table_3[List_Storage]))

AKA select the ID's from Table 2 where the value of List_Storage in Table_3 is in the Table 2 column Table_2_List

So this works okay, but not fully as intended. What I was hoping to happen was if a user selects the value "a", they will get all rows that contain "a". So row ID 1 and 2. 

What is happening is it only selects exact matches. So a selection of "a" only gives me row ID 1. I feel like this is behaving how the devs want it to so I am looking for alternatives. I have tried CONTAINS:

SELECT(Table_2[Table_2_ID],CONTAINS(Table_3[List_Storage],Table_2[Table_2_lists]))

but had no luck. A push in the right direction would be helpful. Thank you!

Edit: Apologies if any of the example is unclear as sometimes it is difficult to describe things in appsheet given the no-code system. For people that know MySQL, I am just trying to use a simple dynamic filter for a drop down, it would be something like this given the selection in Table_3

SELECT Table_2_ID FROM Table_2 WHERE Table_2_Lists IN ("a","b") 

or 

SELECT Table_2_ID FROM Table_2 WHERE Table_2_Lists REGEX 'a|b'

or 

SELECT Table_2_ID FROM Table_2 WHERE Table_2_Lists LIKE '%a%' or LIKE '%b%

[Suvrutt_Gurjar]

The description could be clearer such as which table this expression is used, the sequence of operations and requirement is also a bit unclear, so if the suggestion below does not work, there could be more discussion on exact requirement.

You could try an expression of 

SELECT(Table_2[Table_2_ID], COUNT(INTERSECT(Table_3[List_Storage],Table_2[Table_2_lists]))>0)

[andrew_caff]

I see what you were wanting to do there, but it looks like an error pops up that says the INTERSECT function does not accept a list of lists.

I hope I can expand better to make the problem more clear as you are correct, I did not appropriately give an output for the function. 

My goal here is to filter data using a select expression. Because I have so many data entries, it is more user friendly to filter the dropdown options prior to the users selection.

The table hierarchy follows:

A table of few proteins

A table of some projects

A table many samples

Each project can use multiple proteins for experiments the user is doing. On the dashboard with the dynamic filters the selections are overwhelming if the users see the full amount of samples or even projects.

What I am trying to do is have the top level filter be select the proteins the user wants, the second filter now only shows the projects those proteins are involved in, and then finally the third filter only shows the samples involved in those projects and use those proteins.

I am having trouble with the dynamic filter on the second level since I need it to search across a column of lists. And match where they intersect like the equation you had above. I am just unsure if Appsheet has the capability of doing this.

[Suvrutt_Gurjar]

Thank you for your more details, I have not yet fully read them. But on the error of lists of lists, please try below expression.

SELECT(Table_2[Table_2_ID], COUNT(INTERSECT(

            SPLIT( Table_3[List_Storage], ",")   ,

              SPLIT(Table_2[Table_2_lists], ",") ))>0)

[andrew_caff]

Hmm that did clear the error, but the filter is grabbing everything for the second drop down. 

I tried to roll with your idea and thought maybe 

SELECT(Table_2[Table_2_ID],

CONTAINS(Table_2[Table_2_Lists],

SPLIT(Table_3[List_Storage],",")))

But same thing. It seems to not be filtering and just grabs everything. I'll keep thinking about this on my end