Select values that matches the content of two different lists

AppSheet AppSheet Q&A
, , ,
1

Hello, how are you? i’ve been facing this problem and cannot solve it. Hope you can help me.

I need to get the articles from a table with the condition that they match the current row “type_article” and the “type_article” on the articles table.

Both of them are type enumlist. This is the formula i’ve come up with (used in valid_if):

select(article[id_article];([type_article]=[_thisrow].[type_article]))

The thing is, this works only for the first value that matches the article type. If i want to filter all the articles that matches two or more article types it doesn’t work.

What should i do?

Hope you can help me, thanks in advance.

2

So let’s be clear - on your row you have a column of “type_article” defined as EnumList. Can it actually have one or more values?

When you use the word “match” - in what what way do you need to match?

For example, if your row column had the list {1, 5, 8}, are you trying to find all the other rows that also have {1,5,8}? Does order matter?

Or are you only trying to find rows that have at least one occurence 1 or 5 or 8 but possibly only just one of those values?

2 Likes
3

INTERSECT() - AppSheet Help

2 Likes
4

Hello Willow, thanks for your answer.

Yes it can have more values.

When you use the word “match” - in what what way do you need to match?> > For example, if your row column had the list {1, 5, 8}, are you trying to find all the other rows that also have {1,5,8}? Does order matter?

When i said “match” i meant literally this:

Select the values from the colum ID_ARTICLE in table ARTICLE, when the TYPE_ARTICLE in this row is the equal as TYPE_ARTICLE in the table ARTICLE.

It should return all the articles that are contained in the articles type selected in the form but for some reason it doesn’t show ALL the articles that match the criteria.

This is the current behavior:

630573eb8e29d483106828.gif

As you can see, when i try to see the articles that belong to the type “services” and “supplies” at the same time it doesn’t show anything, but when i try only “services” it does show. So it seems to work for only one “match”

For example, if your row column had the list {1, 5, 8}, are you trying to find all the other rows that also have {1,5,8}? Does order matter?> > Or are you only trying to find rows that have at least one occurence 1 or 5 or 8 but possibly only just one of those values?

No, this is not what i need.

Please check above.

Thanks again!,

Fede.

5

Hello Steve, I don’t think this is what i need as Intersect() gives me the common values of two lists. If you can please check the answer i gave to Willow.

Thanks for your answer nevertheless!!

Fede.

6

Ok, got it. As you probably know the dropdown list is controlled by the Valid_If expression. Can you please show the expression you have in the Valid_If for your “articulo” field? We can help you get it corrected.

2 Likes
7

Yes sure, this is the expression in the Valid_if for the “articulo” field (in my first post i just translated every item to english):

select(articulo[id_articulo];[tipo_articulo]=[_thisrow].[tipo_articulo])
8

Due to the EnumList, change it to be like this:

SELECT(articulo[id_articulo], IN([tipo_articulo], [_thisrow].[tipo_articulo]))
2 Likes
9

Hello Willow, thanks for your answer once again.

The code you wrote worked flawlessly, and I learned this new (for me) method of using Select() too. I think it will be very useful in the future.

Thanks again!!

Regards,

Federico.

1 Like
10

Hello! If i have the same problem but in my case I do need to find only one of the values form the list of {1 ,5 , 8} what expression would I need?