Comparing a list items with another list

AppSheet AppSheet Q&A
1

Hi,

I have two tables A & B. I have columns, Status A and Status B, in tables A and B, respectively. The Status columns are of type Enumlist.

I want to get the row ids of those rows from table B for which the Status B has at least one or all the values present in column Status A in table A. So, basically, I need to compare each value from a list of values in one column of a table with each value from a list of values in a column of another table.

I’ve tried both functions, IN() and Contains(), but neither has produced the desired results.

SELECT(
Table B [Id],

IN(

[_THISROW].[Status A], Table B [Status B]

)
)

2

if understanding of your requirement is correct, please try an below

  1. In table A , add a virtual column called say [Common_Value] of type Y/N with an expression something like

ISNOTBLANK( INTERSECT([Status A] , SPLIT(TEXT([Table B[Status B]), “,”))

2.Then your expression to find the table B rows can be something like

SELECT(Table B[ID], [Common_Value])

You may also mention where you are using these expressions. Please avoid using these in virtual columns as it would add to sync time in large tables ( Tables with 100s or 1000s of rows).

1 Like
3

INTERSECT()

2 Likes